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

OLE Error

P: n/a
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.
Nov 12 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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.

Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.