By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,986 Members | 1,944 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,986 IT Pros & Developers. It's quick & easy.

Method 'Range' of object '_global' failed during sort

P: 1
I'm trying to fix an sub routine in an VB module that basically reads in a MS database and writes it to an Excel Spread sheet. It works just fine except that the data isn't sorted correctly. I have no experience at all in excel or the vb code to access excel. I found a few lines of code by searching on how to sort in vb for excel and the code with the new sort logic works fine the first time you run it, but run it twice and you get the titled error. Here is the code they created and I marked the code I added. Any help would be deeply appreciated. Sorry ahead of time for length of code.

Expand|Select|Wrap|Line Numbers
  1. Public Sub excel()
  2.  
  3.   Dim indx As Integer
  4.   Dim rowIndex As Integer
  5.   Dim colIndex As Integer
  6.   Dim recordCount As Integer
  7.   Dim fieldCount As Integer
  8.   Dim MSG As String
  9.   Dim avRows As Variant
  10.   Dim excelVersion As Integer
  11.   Dim transType As String
  12.   Dim system As String
  13.   Dim sql As String
  14.   Dim TcrRecs()
  15.   Dim Oput As String
  16.  
  17.   system = lstLOB.Text
  18.   transType = lstTransacType.Text
  19.  
  20. 'CHECK FOR SEARCH TYPE
  21.   openconn
  22.  
  23.   If lstLOB.SelCount = 0 And lstTransacType.SelCount = 0 Then
  24.     MsgBox "Search Requires A System/Transaction Or Both!", vbExclamation, "Error"
  25.     closeconn
  26.     Exit Sub
  27.   End If
  28.  
  29.   If lstTransacType.SelCount > 0 Then
  30.     For a = 0 To lstTransacType.ListCount - 1
  31.       If lstTransacType.Selected(a) Then
  32.         If sql = "" Then
  33.           sql = " AND (TransacType = '" & lstTransacType.List(a) & "'"
  34.         Else
  35.           sql = sql + " or TransacType = '" & lstTransacType.List(a) & "'"
  36.         End If
  37.       End If
  38.     Next
  39.  
  40.     Call rs("SELECT a.Name, b.TransacType, b.TestCaseNum, c.PolicyNum, b.TestScenarioDescription, c.Impact, c.ExpectedResults FROM Areas a, TestCases b, TestCaseExecution c WHERE a.AreaID = b.AreaID AND b.TestCaseID = c.TestCaseID " & sql & ") ORDER BY a.NAME, b.TransacType, b.TestCaseNum ASC")
  41.  
  42.   End If
  43.  
  44.   If lstLOB.SelCount > 0 And lstTransacType.SelCount = 0 Then
  45.     For a = 0 To lstLOB.ListCount - 1
  46.       If lstLOB.Selected(a) Then
  47.         If sql = "" Then
  48.           sql = " AND (name = '" & lstLOB.List(a) & "'"
  49.         Else
  50.           sql = sql + " or name = '" & lstLOB.List(a) & "'"
  51.         End If
  52.       End If
  53.     Next
  54.  
  55.     Call rs("SELECT a.Name, b.TransacType, b.TestCaseNum, c.PolicyNum, b.TestScenarioDescription, c.Impact, c.ExpectedResults FROM Areas a, TestCases b, TestCaseExecution c WHERE a.AreaID = b.AreaID AND b.TestCaseID = c.TestCaseID " & sql & ") ORDER BY a.NAME, b.TransacType, b.TestCaseNum ASC")
  56.  
  57.   End If
  58.  
  59.   If adoRS.recordCount = 0 Then
  60.     MsgBox "There Were No Test Cases Found Matching Your Criteria", vbInformation, "Error"
  61.     closeconn
  62.     Exit Sub
  63.   End If
  64.  
  65. 'THROWS THE RECORDSET INTO AN ARRAY
  66.   avRows = adoRS.GetRows()
  67.  
  68.   recordCount = UBound(avRows, 2) + 1
  69.   fieldCount = UBound(avRows, 1) + 1
  70.  
  71. 'CREATE REDERENCE VARIABLE FOR THE SPREADSHEET
  72.   Set objExcel = CreateObject("Excel.Application")
  73.   objExcel.Visible = True
  74.   objExcel.Workbooks.add
  75.  
  76.   Set objTemp = objExcel
  77.  
  78.   excelVersion = Val(objExcel.Application.Version)
  79.   If (excelVersion >= 8) Then
  80.     Set objExcel = objExcel.ActiveSheet
  81.   End If
  82.  
  83. 'PLACE THE NAMES OF THE FIELDS AS COLUMN HEADERS
  84.  
  85.   With objExcel.Cells(1, 1)
  86.     .Value = "System"
  87.     .VerticalAlignment = xlVAlignTop
  88.     With .Font
  89.       .Name = "Arial"
  90.       .Bold = True
  91.       .Size = 11
  92.       .Italic = True
  93.     End With
  94.   End With
  95.  
  96.   With objExcel.Cells(1, 2)
  97.     .Value = "Trans Type"
  98.     .VerticalAlignment = xlVAlignTop
  99.     With .Font
  100.       .Name = "Arial"
  101.       .Bold = True
  102.       .Size = 11
  103.       .Italic = True
  104.     End With
  105.   End With
  106.  
  107.   With objExcel.Cells(1, 3)
  108.     .Value = "TC Nbr"
  109.     .VerticalAlignment = xlVAlignTop
  110.     With .Font
  111.       .Name = "Arial"
  112.       .Bold = True
  113.       .Size = 11
  114.       .Italic = True
  115.     End With
  116.   End With
  117.  
  118.   With objExcel.Cells(1, 4)
  119.     .Value = "In Prog"
  120.     .VerticalAlignment = xlVAlignTop
  121.     With .Font
  122.       .Name = "Arial"
  123.       .Bold = True
  124.       .Size = 11
  125.       .Italic = True
  126.     End With
  127.   End With
  128.  
  129.   With objExcel.Cells(1, 5)
  130.     .Value = "Req Nbr"
  131.     .VerticalAlignment = xlVAlignTop
  132.     With .Font
  133.       .Name = "Arial"
  134.       .Bold = True
  135.       .Size = 11
  136.       .Italic = True
  137.     End With
  138.   End With
  139.  
  140.   With objExcel.Cells(1, 6)
  141.     .Value = "Policy Nbr"
  142.     .VerticalAlignment = xlVAlignTop
  143.     With .Font
  144.       .Name = "Arial"
  145.       .Bold = True
  146.       .Size = 11
  147.       .Italic = True
  148.     End With
  149.   End With
  150.  
  151.   With objExcel.Cells(1, 7)
  152.     .Value = "Date"
  153.     .VerticalAlignment = xlVAlignTop
  154.     With .Font
  155.       .Name = "Arial"
  156.       .Bold = True
  157.       .Size = 11
  158.       .Italic = True
  159.     End With
  160.   End With
  161.  
  162.   With objExcel.Cells(1, 8)
  163.     .Value = "Tstr Intls"
  164.     .VerticalAlignment = xlVAlignTop
  165.     With .Font
  166.       .Name = "Arial"
  167.       .Bold = True
  168.       .Size = 11
  169.       .Italic = True
  170.     End With
  171.   End With
  172.  
  173.   With objExcel.Cells(1, 9)
  174.     .Value = "Test Scenario Description"
  175.     .VerticalAlignment = xlVAlignTop
  176.     With .Font
  177.       .Name = "Arial"
  178.       .Bold = True
  179.       .Size = 11
  180.       .Italic = True
  181.     End With
  182.   End With
  183.  
  184.   With objExcel.Cells(1, 10)
  185.     .Value = "Impact"
  186.     .VerticalAlignment = xlVAlignTop
  187.     With .Font
  188.       .Name = "Arial"
  189.       .Bold = True
  190.       .Size = 11
  191.       .Italic = True
  192.     End With
  193.   End With
  194.  
  195.   With objExcel.Cells(1, 11)
  196.     .Value = "Expected Results"
  197.     .VerticalAlignment = xlVAlignTop
  198.     With .Font
  199.       .Name = "Arial"
  200.       .Bold = True
  201.       .Size = 11
  202.       .Italic = True
  203.     End With
  204.   End With
  205.  
  206. 'MEMORY MANAGEMENT
  207.   adoRS.Close
  208.   Set adoRS = Nothing
  209.  
  210. 'ADD THE DATA
  211.   With objExcel
  212.     For rowIndex = 2 To recordCount + 1
  213.  
  214.       Oput = IIf(IsNull(avRows(1 - 1, rowIndex - 2)), "", avRows(1 - 1, rowIndex - 2))
  215.       'Oput = avRows(1 - 1, rowIndex - 2)
  216.       Oput = Replace(Oput, Chr(13), "")
  217.       Oput = Replace(Oput, Chr(9), "")
  218.       .Cells(rowIndex, 1).Value = Oput
  219.  
  220. '      .Cells(rowIndex, 1).Value = avRows _
  221. '      (1 - 1, rowIndex - 2)
  222.       Oput = IIf(IsNull(avRows(2 - 1, rowIndex - 2)), "", avRows(2 - 1, rowIndex - 2))
  223.       'Oput = avRows(2 - 1, rowIndex - 2)
  224.       Oput = Replace(Oput, Chr(13), "")
  225.       Oput = Replace(Oput, Chr(9), "")
  226.       .Cells(rowIndex, 2).Value = Oput
  227.  
  228. '      .Cells(rowIndex, 2).Value = avRows _
  229. '     (2 - 1, rowIndex - 2)
  230.       Oput = IIf(IsNull(avRows(3 - 1, rowIndex - 2)), "", avRows(3 - 1, rowIndex - 2))
  231.       'Oput = avRows(3 - 1, rowIndex - 2)
  232.       Oput = Replace(Oput, Chr(13), "")
  233.       Oput = Replace(Oput, Chr(9), "")
  234.       .Cells(rowIndex, 3).Value = Oput
  235.  
  236. '      .Cells(rowIndex, 3).Value = avRows _
  237. '      (3 - 1, rowIndex - 2)
  238.  
  239.       .Cells(rowIndex, 4).Value = " "
  240.       .Cells(rowIndex, 5).Value = " "
  241.  
  242.       Oput = IIf(IsNull(avRows(4 - 1, rowIndex - 2)), "", avRows(4 - 1, rowIndex - 2))
  243.       'Oput = avRows(4 - 1, rowIndex - 2)
  244.       Oput = Replace(Oput, Chr(13), "")
  245.       Oput = Replace(Oput, Chr(9), "")
  246.       .Cells(rowIndex, 6).Value = Oput
  247.  
  248.  '    .Cells(rowIndex, 6).Value = avRows _
  249.  '    (4 - 1, rowIndex - 2)
  250.  
  251.       .Cells(rowIndex, 7).Value = " "
  252.       .Cells(rowIndex, 8).Value = " "
  253.  
  254.       Oput = IIf(IsNull(avRows(5 - 1, rowIndex - 2)), "", avRows(5 - 1, rowIndex - 2))
  255.       'Oput = avRows(5 - 1, rowIndex - 2)
  256.       Oput = Replace(Oput, Chr(13), "")
  257.       Oput = Replace(Oput, Chr(9), "")
  258.       .Cells(rowIndex, 9).Value = Oput
  259.  
  260.   '    .Cells(rowIndex, 9).Value = avRows _
  261.   '    (5 - 1, rowIndex - 2)
  262.  
  263.       Oput = IIf(IsNull(avRows(6 - 1, rowIndex - 2)), "", avRows(6 - 1, rowIndex - 2))
  264.       'Oput = avRows(6 - 1, rowIndex - 2)
  265.       Oput = Replace(Oput, Chr(13), "")
  266.       Oput = Replace(Oput, Chr(9), "")
  267.       .Cells(rowIndex, 10).Value = Oput
  268.  
  269.      ' .Cells(rowIndex, 10).Value = avRows _
  270.      ' (6 - 1, rowIndex - 2)
  271.       Oput = avRows(7 - 1, rowIndex - 2)
  272.       Oput = Replace(Oput, Chr(13), "")
  273.       Oput = Replace(Oput, Chr(9), "")
  274.      .Cells(rowIndex, 11).Value = Oput
  275. '      .Cells(rowIndex, 11).Value = avRows _
  276. '      (7 - 1, rowIndex - 2)
  277.  
  278.     Next
  279.   End With
  280.  
  281.   objExcel.Cells(1, 1).CurrentRegion.EntireColumn.AutoFit
  282.   objExcel.Cells(1, 1).CurrentRegion.VerticalAlignment = xlVAlignTop
  283.   objExcel.Cells(1, 1).CurrentRegion.WrapText = True
  284.  
  285. ' This what I added*******************************************************
  286.   Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Key2:=Range("C1") _
  287.    , Order2:=xlAscending, Key3:=Range("B1"), Order3:=xlAscending, Header:= _
  288.   xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
  289. '*************************************************************************
  290.  
  291.   closeconn
  292.  
  293. End Sub
  294.  
  295.  
Jan 29 '08 #1
Share this Question
Share on Google+
1 Reply


Expert 100+
P: 634
Hi

Without ploughing through all your code, and based on my the interpritation that it ALWAYS works without the sort, and works just ONCE with the the sort code, I suggest this mod

Change this
Expand|Select|Wrap|Line Numbers
  1. ' This what I added*********************************************  **********
  2.   Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Key2:=Range("C1") _
  3.    , Order2:=xlAscending, Key3:=Range("B1"), Order3:=xlAscending, Header:= _
  4.   xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
  5. '*************************************************  ************************
to this
Expand|Select|Wrap|Line Numbers
  1. ' This what I added*********************************************  **********
  2.   objExcel.Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Key2:=Range("C1") _
  3.    , Order2:=xlAscending, Key3:=Range("B1"), Order3:=xlAscending, Header:= _
  4.   xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
  5. '*************************************************  ************************
This suggestion is based on very similar problem I had exporting a report to Word (which IMHO is much worse to automate that Excel, as the native object are not so obvious!).

The above suggestion is based on a similar once pass only is OK problem, the explanation being that the native Word/Excel object that are not excplicitly refered to the Woed/Excel object stay referenced to the first instance of Word/Excel, which changes to a different instance for the second pass. Therefore you need to referance the active Word/Excel object that is current instance !!!???

Be interested to see if it solves your problem.


MTB
Jan 29 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.