Running Excel from Access (Access 2002 SP-2)

Topic

New Reply

I’m not sure if this is an Access problem or an Excel problem or just my problem, but I’ll try posting the question here. I’m using VBA automation code in my Access routines to set up an Excel chart for displaying Access data. For the most part, everything works fine. The problem is that when I close out Excel when I’m done, it doesn’t seem to really get “released” — there’s still an instance of it lurking in Windows XP’s Task Manger (even though it’s left the Task Bar). This seems to cause conflicts (automation errors) when I try to run the code again. If I apply “End Process” to the Excel instance, exit Access, and restart my Access application the routine works again.

The basic framework I use is:

Dim appExcel As Excel.Application

Set appExcel = CreateObject(“Excel.Application”)
appExcel.Visible = True
appExcel.Workbooks.Open
.
.
.
appExcel.ActiveWorkbook.Save
appExcel.Quit
Set appExcel = Nothing

Is there something else I should be doing, housekeeping-wise, to close Excel when I’m done with it? Or is there something else I’m doing wrong?

Thanks,

Reply | Quote

Replies

Although the code looks OK, try closing the workbook explicitly (I know, saving it, then quitting Excel should do that, but …)

appExcel.ActiveWorkbook.Save
appExcel.ActiveWorkbook.Close SaveChanges:=False
appExcel.Quit
Set appExcel = Nothing

Can’t guarantee anything, though.

Reply | Quote

Thanks for the suggestion, Hans. I tried adding the .Close command, but the problem persists. The error I get is shown in the attachment. The thing that’s odd, too, is that on the second pass through, the automation code runs for a bit (plugging values into Excel cells, formatting cells, etc.), with the error being generated on the following command:

appExcel.Range(Cell1,cell2)).HorizontalAlignment = xlCenterAcrossSelection

If I debug at this point and try stepping through this command, I get the same Error 1004 with a different description (also in the attachment). If I try displaying the current value (? appExcel.Range(Cell1,cell2)).HorizontalAlignment), I get the same error, but if I try displaying the value for a single cell (? appExcel.Cells(2,1).HorizontalAlignment), I get the current value. I wonder if it’s a problem with the Range object or if it’s just chance that it’s hanging on this particular statement (it ran fine the first time through). I’ll keep fiddling with it, but if this jogs any ideas, I’d sure appreciate any suggestions.

Thanks again,

Reply | Quote

How are Cell1 and Cell2 defined?

Reply | Quote

How are Cell1 and Cell2 defined?

Reply | Quote

I think I found the answer…

When specifying the arguments for the range collection, I wasn’t including the appExcel “parent” object. I was using appExcel.Range(Cell(2,1),Cell(intRow,1)).HorizontalAlignment… but I should have been using appExcel.Range(appExcel.Cell(2,1),(appExcel.Cell(intRow,1)).HorizontalAlignment….

It’s still puzzling to me why it works the first time (using the incomplete specification), but not thereafter…

Reply | Quote

Wrote my latest response before I saw yours. Your on to me, Hans! Thanks for your help.

Reply | Quote

Wrote my latest response before I saw yours. Your on to me, Hans! Thanks for your help.

Reply | Quote

It works first time because you are creating an implicit Excel object by referring to Cell(..) without specifying an object explicitly. But this implicit object does not get destroyed at the end of the code. It is this object that remains in the Processes tab of the Task Manager, and it is also the one causing problems when you run the code again.

Reply | Quote

Makes sense. My “brick and morter” house doesn’t keep itself clean, either.

Thanks.

Reply | Quote

Makes sense. My “brick and morter” house doesn’t keep itself clean, either.

Thanks.

Reply | Quote

It works first time because you are creating an implicit Excel object by referring to Cell(..) without specifying an object explicitly. But this implicit object does not get destroyed at the end of the code. It is this object that remains in the Processes tab of the Task Manager, and it is also the one causing problems when you run the code again.

Reply | Quote

I think I found the answer…

When specifying the arguments for the range collection, I wasn’t including the appExcel “parent” object. I was using appExcel.Range(Cell(2,1),Cell(intRow,1)).HorizontalAlignment… but I should have been using appExcel.Range(appExcel.Cell(2,1),(appExcel.Cell(intRow,1)).HorizontalAlignment….

It’s still puzzling to me why it works the first time (using the incomplete specification), but not thereafter…

Reply | Quote

Thanks for the suggestion, Hans. I tried adding the .Close command, but the problem persists. The error I get is shown in the attachment. The thing that’s odd, too, is that on the second pass through, the automation code runs for a bit (plugging values into Excel cells, formatting cells, etc.), with the error being generated on the following command:

appExcel.Range(Cell1,cell2)).HorizontalAlignment = xlCenterAcrossSelection

If I debug at this point and try stepping through this command, I get the same Error 1004 with a different description (also in the attachment). If I try displaying the current value (? appExcel.Range(Cell1,cell2)).HorizontalAlignment), I get the same error, but if I try displaying the value for a single cell (? appExcel.Cells(2,1).HorizontalAlignment), I get the current value. I wonder if it’s a problem with the Range object or if it’s just chance that it’s hanging on this particular statement (it ran fine the first time through). I’ll keep fiddling with it, but if this jogs any ideas, I’d sure appreciate any suggestions.

Thanks again,

Reply | Quote

Although the code looks OK, try closing the workbook explicitly (I know, saving it, then quitting Excel should do that, but …)

appExcel.ActiveWorkbook.Save
appExcel.ActiveWorkbook.Close SaveChanges:=False
appExcel.Quit
Set appExcel = Nothing

Can’t guarantee anything, though.

Reply | Quote