OLE Error

[Rich]

I am using Access 97.

I am trying to populate an Excel spreadsheet when I get a run-time
error 1004. The error says :

Method 'Sheets' of object '_Global' failed.

What I am trying to do is populate several different tabs with data.
For whatever reason, OLE Automation seems to get "lost".

The code looks something like this.
Set ws = wb.Sheets("Fees")

lnRowCount = 1
Do While not rsBalanceReport.EOF
lnRowCount = lnRowCount + 1

lcCellA = "A" & trim(str(lnRowCount))
lcCellB = "B" & trim(str(lnrowcount))

ws.range(lcCellA).value = rsBalanceReport(0)
ws.range(lcCellB).value = rsBalanceReport(1)

rsBalanceReport.MoveNext
loop

*Code Crashes on the next line.
Sheets("Rebate Buckets").Select
Columns("A:Z").AutoFit
Columns("A:Z").Select
Selection.NumberFormat = "#,##0.00"
How can I fix this problem.

[TC]

Whatever the cause is, it is unlikely to be "OLE Automation gets lost"! :-)

Are you sure that the syntax is correct for the statement in question? I
would try putting a copy of the <Sheets("Rebate Buckets").Select> statement
immediately after you've opened the workbook, just to see if it works there,
or not. If it doesn't, this confirms that there is something wrong with that
statement, not with the preceding code.

If you're not sure of the syntax, just go to Excel & record a macro while
you manually select that shet, then confirm the VBA that this produced.

HTH,
TC
"Rich" <ri*******@earthlink.net> wrote in message
news:46**************************@posting.google.c om...

I am using Access 97.

I am trying to populate an Excel spreadsheet when I get a run-time
error 1004. The error says :

Method 'Sheets' of object '_Global' failed.

What I am trying to do is populate several different tabs with data.
For whatever reason, OLE Automation seems to get "lost".

The code looks something like this.
Set ws = wb.Sheets("Fees")

lnRowCount = 1
Do While not rsBalanceReport.EOF
lnRowCount = lnRowCount + 1

lcCellA = "A" & trim(str(lnRowCount))
lcCellB = "B" & trim(str(lnrowcount))

ws.range(lcCellA).value = rsBalanceReport(0)
ws.range(lcCellB).value = rsBalanceReport(1)

rsBalanceReport.MoveNext
loop

*Code Crashes on the next line.
Sheets("Rebate Buckets").Select
Columns("A:Z").AutoFit
Columns("A:Z").Select
Selection.NumberFormat = "#,##0.00"
How can I fix this problem.