Trapping Errors in Excel through Access

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

I'm using Office 2003. While in Access, I dump a query to an Excel file,
open that Excel file, perform some data manipulation using the Excel Object
Model, then close and save the Excel file

My problem is, from time to time, my Access App will create the Excel file,
and at some point during data manipulation using the Excel Object Model,
error out, but never close the Excel file, and never trap any errors in
Access.

I have come to the conclusion that something has errored within the open
Excel file I have, based on some code I have written using the Excel object
model, but for some reason I am not trapping any errors in Access. Access
doesn't freeze or hang, it returns control to the mouse/keyboard, but never
closes the Excel file (nor the EXCEL.EXE process), and just kinda leaves me
wondering what went wrong.

Any ideas?

Thanks,

Scott
 
Hello,

I'm using Office 2003. While in Access, I dump a query to an Excel file,
open that Excel file, perform some data manipulation using the Excel Object
Model, then close and save the Excel file

My problem is, from time to time, my Access App will create the Excel file,
and at some point during data manipulation using the Excel Object Model,
error out, but never close the Excel file, and never trap any errors in
Access.

I have come to the conclusion that something has errored within the open
Excel file I have, based on some code I have written using the Excel object
model, but for some reason I am not trapping any errors in Access. Access
doesn't freeze or hang, it returns control to the mouse/keyboard, but never
closes the Excel file (nor the EXCEL.EXE process), and just kinda leaves me
wondering what went wrong.

Any ideas?

Thanks,

Scott
Click to expand...

Hard to tell from here... could you post your code? That's easier to
debug than your description <g>. (Although an accurate/concise
description of what it does is a big help too.)
 
It's a lot of code, I wouldn't even know where to begin. If it happens
again, and I can trace it to a smaller section of code, I will post it.
 
The most usual cause of the problem you are experiencing is inadequate
qualification of object references. That is, you have to be very explicit in
defining the Excel objects when using automation. What happens is you create
an instance of Excel as an application object. Then you create a workbook or
some other Excel object, but you don't reference it to the original
application object. Access can get confused and create his own instance of
Excel. Now, when you are done, you correctly close the workbook, quit the
application, and set all the object variables to Nothing, but there remains
an instance of Excel.exe in the Processes tab of Task Manager. You quit the
instance you created, but the instance Access created is still there.

I would advise always use late binding and be sure you are correctly and
fully qualifying and destroying your automation objects.
 
Back
Top