Errors using ADO, running Excel automation

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Error number 9: subscript out of range
Error number 91: Object variable or with block varible not set

I get the preceding error messages when running the following code (ADO
using SQL recordsets):


Do Until rsData.EOF

xlApp.ActiveSheet.Name = rsData.Fields(0).Value
xlApp.Range("C4").Value = rsData.Fields(1).Value 'Acct descr
xlApp.Range("C5").Value = rsData.Fields(0).Value 'Acct Number
xlApp.Range("C6").Value = "'" & strDate ' as at balance
sheet date
xlApp.Range("K9").Value = rsData.Fields(3).Value 'Balance
per GL
xlApp.Range("E15").Value = "'" & strPer1 'Period 1 label
xlApp.Range("E16").Value = rsData.Fields(3).Value 'Period 1
value
xlApp.Range("G15").Value = "'" & strPer2 'Period 2 label
xlApp.Range("G16").Value = rsData.Fields(4).Value 'Period 2
value
xlApp.Range("I15").Value = "'" & strPer3 'Period 3 label
xlApp.Range("I16").Value = rsData.Fields(5).Value 'Period 3
value
xlApp.Range("K15").Value = "'" & strPer4 'Period 4 label
xlApp.Range("K16").Value = rsData.Fields(6).Value 'Period 4
value
xlApp.Range("C53").Value = rsResp.Fields(4).Value 'Resp Name

rsData.MoveNext
If rsData.EOF <> True Then
strCurrSheetName = ActiveSheet.Name
xlApp.Worksheets(strCurrSheetName).Copy
After:=xlApp.Worksheets(strCurrSheetName)

Else
'nothing
End If


Loop


*** It runs through the first time as expected, but when i run it
immediately after that, it blows up on the worksheet.copy line. Im trying to
run some automation with excel. Thanks.
 
Maybe
strCurrSheetName = xlApp.ActiveSheet.Name
rather than
strCurrSheetName = ActiveSheet.Name
?

"Blowing up" with those messages indicates 1) it can't find something in a
collection (out of range) and 2) it can't get a handle on the object you
want to copy (object not set). Both errors are probably the result of the
same thing (#1 results in #2).

Set a breakpoint on the line and check the value of strCurrSheetName.

HTH,
 
Thanks for the suggestions George. I just had to step back and look at it a
little closer. xlApp.ActiveSheet.Name as the answer i needed
 
Back
Top