Excel worksheet question

[Carl Imthurn]

My apologies in advance for cross-posting; I wasn’t sure
where to post this question since it involves both Excel and
VB.

I am launching Excel and copying a number of recordsets to
different worksheets within the same workbook.
I am returning multiple recordsets from one stored
procedure. The first recordset works fine;
during the processing of the second recordset (there could
be up to 5 recordsets) I get the following error message:

Run-time error ‘1004’:
Select method of Range class failed

Here’s the code:

Dim ExcelApp As Excel.Application ' This is the excel
program
Dim ExcelWBk As Excel.Workbook ' This is the work book
Dim ExcelWS As Excel.Worksheet ' This is the sheet

Set ExcelApp = CreateObject("Excel.Application") ' Create
Excel Object.
Set ExcelWBk = ExcelApp.Workbooks.Add

Dim iCounter As Integer

Dim rs As adodb.Recordset
Set rs = New adodb.Recordset

rs.Source = "EXEC s_FinancialAdminEOM_1 '2003-01-01',
'2004-04-01'"
rs.ActiveConnection = pADOConnection
rs.CursorLocation = adUseClient
rs.Open

If Not (rs.BOF And rs.eof) Then
iCounter = iCounter + 1
Set ExcelWS = ExcelWBk.Worksheets(iCounter)
' Copy the data onto the spreadsheet
ExcelWS.Range("A2").CopyFromRecordset rs
ExcelWS.UsedRange.RowHeight = 13
ExcelWS.UsedRange.Columns.AutoFit
ExcelWS.Name = "Travel charges for Zamboanga"
ExcelWS.Range("A1").Select ' make A1 the "active" cell

End If

Set rs = rs.NextRecordset

If Not (rs.BOF And rs.eof) Then
iCounter = iCounter + 1
Set ExcelWS = ExcelWBk.Worksheets(iCounter)
' Copy the data onto the spreadsheet
ExcelWS.Range("A2").CopyFromRecordset rs
ExcelWS.UsedRange.RowHeight = 13
ExcelWS.UsedRange.Columns.AutoFit
ExcelWS.Name = "Travel charges for Timbuktu"

' THE FOLLOWING LINE GENERATES THE ERROR MESSAGE:
' Run-time error ‘1004’:
' Select method of Range class failed
ExcelWS.Range("A1").Select ' make A1 the "active" cell

End If

' up to 3 more recordsets are copied to 3 different
sheets here

ExcelWBk.SaveAs "C:\My Documents\Expense report 2004.xls"


ExcelApp.Quit

End Sub

Why did the ExcelWS.Range("A1").Select command work on the
first recordset but not the second? Am I neglecting to do
something with the ExcelWS object? I thought that just
setting it to ExcelWBk.Worksheets(iCounter) should be enough
.. . .

Any suggestions/advice/help will be, as always, gratefully
accepted.

Carl

 

[Tod]

Make sure ExcelWS is still the active sheet before the
line:

ExcelWS.Range("A1").Select

tod

 

[Carl Imthurn]

Tod --

That did it -- thank you so much.

Carl

Make sure ExcelWS is still the active sheet before the
line:

ExcelWS.Range("A1").Select

tod