Results 1 to 8 of 8
  1. #1
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Running Excel from Access (Access 2002 SP-2)

    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:
    <font face="Georgia">
    Dim appExcel As Excel.Application

    Set appExcel = CreateObject("Excel.Application")
    appExcel.Visible = True
    appExcel.Workbooks.Open <filename>
    .
    .
    .
    appExcel.ActiveWorkbook.Save
    appExcel.Quit
    Set appExcel = Nothing
    </font face=georgia>
    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,

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Running Excel from Access (Access 2002 SP-2)

    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.

  3. #3
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Running Excel from Access (Access 2002 SP-2)

    Thanks for the suggestion, Hans. I tried adding the <font face="Georgia">.Close</font face=georgia> 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:
    <font face="Georgia">
    appExcel.Range(Cell1,cell2)).HorizontalAlignment = xlCenterAcrossSelection
    </font face=georgia>
    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 (<font face="Georgia">? appExcel.Range(Cell1,cell2)).HorizontalAlignment</font face=georgia>), I get the same error, but if I try displaying the value for a single cell (<font face="Georgia">? appExcel.Cells(2,1).HorizontalAlignment</font face=georgia>), 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,
    Attached Images Attached Images

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Running Excel from Access (Access 2002 SP-2)

    How are Cell1 and Cell2 defined?

  5. #5
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Running Excel from Access (Access 2002 SP-2)

    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 <font face="Georgia">appExcel.Range(Cell(2,1),Cell(intRo w,1)).HorizontalAlignment...</font face=georgia> but I should have been using <font face="Georgia">appExcel.Range(appExcel.Cell(2,1),(appExcel.Cell(intRow,1)).HorizontalAlignment...</font face=georgia>.

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

  6. #6
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Running Excel from Access (Access 2002 SP-2)

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

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Running Excel from Access (Access 2002 SP-2)

    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.

  8. #8
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Running Excel from Access (Access 2002 SP-2)

    Makes sense. My "brick and morter" house doesn't keep itself clean, either.

    Thanks.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •