Excel and Access VBA - Method failed problem

[Stu H]

I have an Access VBA procedure that uses Excel by automation and opens
different Excel Workbooks 1 at a time.

Some data is manipulated and added to a new sheet inserted in th
workbook and the workbook is saved under a new name. The workbook i
closed before opening another.

No matter which workbook I open 1st all code executes correctly. Bu
when I try to open the second as soon as code that cites a specifi
range or sheet is encountered I receive the following message:
"Method 'Sheets' of object '_Global' failed."

The problem has something to do with the fact that another workbook wa
opened before.

The code is in Access and includes:
objExcel = New Excel.Application
Set xlsFarEastBOM = objExcel.Workbooks.Open(strFileAndPath)
At the end of the procedure, I close the active workbook and have th
following code
objExcel.Quit
set objExcel = nothing

A different procedure is then called to do the similar routine with
different workbook

 

[Dick Kusleika]

Stu

You don't seem to use Sheets in any code you've shown. It sounds like you
have an unqualified Sheets somewhere in the code and you need to qualify it
with the workbook variable

Not

Sheets(1)...

but

xlsFarEastBOM.Sheets(1)...

If that's not it, post the portion of the code that calls the Sheets
property, likely in the second sub since that's where you're getting the
error.

 

[Stu H]

Dick,

Thank you for your reply. I found that the problem was there was a
instance of Excel open when the 2nd procedure opened a new instance an
so the code did not know which instance of Excel to be working with