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(lnRowC ount))
lcCellB = "B" & trim(str(lnrowc ount))

ws.range(lcCell A).value = rsBalanceReport (0)
ws.range(lcCell B).value = rsBalanceReport (1)

rsBalanceReport .MoveNext
loop

*Code Crashes on the next line.
Sheets("Rebate Buckets").Selec t
Columns("A:Z"). AutoFit
Columns("A:Z"). Select
Selection.Numbe rFormat = "#,##0.00"


How can I fix this problem.

[TC]

Re: OLE Error

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("Reba te Buckets").Selec t> 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" <richmarin@eart hlink.net> wrote in message
news:46e4ef1a.0 309141736.114c3 295@posting.goo gle.com...[color=blue]
> 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(lnRowC ount))
> lcCellB = "B" & trim(str(lnrowc ount))
>
> ws.range(lcCell A).value = rsBalanceReport (0)
> ws.range(lcCell B).value = rsBalanceReport (1)
>
> rsBalanceReport .MoveNext
> loop
>
> *Code Crashes on the next line.
> Sheets("Rebate Buckets").Selec t
> Columns("A:Z"). AutoFit
> Columns("A:Z"). Select
> Selection.Numbe rFormat = "#,##0.00"
>
>
> How can I fix this problem.[/color]