Excel automation causes runtime error 1004 on second attempt

  • Thread starter Thread starter Ken Snell
  • Start date Start date
K

Ken Snell

You are not being consistent in your reference to the EXCEL application.

You set exl to be the EXCEL application, and you use it to open the EXCEL
workbook. You then use EXCEL instead of exl in the next two lines of code.
You then set exl to Nothing while leaving the EXCEL instance running that
had been set to exl. Then you run the code again and start a new line of
EXCEL as exl, but you continue to use EXCEL, which likely is pointing to the
first and still running instance of EXCEL -- and in that application, the
workbook is now closed and not available!
 
All,

I have used Excel automation many times in the past without error until now.
My machine has both Access 97 and XP installed but this code is written in
97 and I am using Excel XP. The code below is running in my database:

Dim exl As Excel.Application

Set exl = New Excel.Application
exl.Workbooks.Open ("C:\Test\ Listing.xls")
Excel.Run "sheet1.LFooter"
Excel.Workbooks(1).Close True
Set exl = Nothing

The first time it runs it runs without error. If I try to run it again
without first closing the database I get a runtime error 1004 'The macro
sheet1.LFooter can't be found'. I have also gotten this same runtime error
1004 with different messages during different attempts. If I close the
database after the first run it works fine. I have the Excel reference
checked as well. Any help would be much appreciated.

Thanks in advance,
Mark C.
 
Thank you Ken,
That was exactly the problem. I must have been half asleep when I wrote this
one.

Thanks again,
Mark C.
 
Back
Top