Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Nottingham, UK, Nottinghamshire, England
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Using ShellExecute to start Excel (Access 2000 VBA)

    Within an Access form, I have a button that, when clicked, creates an Excel spreadsheet (with a pivot table, although that's not important). I used to then make Excel visible and just destroy the Excel Application variable in the code, so that the user was always left in the spreadsheet for them to use as they saw fit. However, this tended to leave behind an Excel process that could prevent access to the last spreadsheet created (unless the process was removed in Task Manager).

    So I thought I would create the spreadsheet, save it, then Quit the Excel.Application, destroy the Application variable, and finally use ShellExecute to open the new spreadsheet. Works a treat EXCEPT that the spreadsheet is invisible in Excel! I have to go into VBA in the Excel application and set ScreenUpdating to True before the sheet can be seen. I've tried setting ScreenUpdating to True within the Access coding while the spreadsheet is being worked on but to no avail.

    Anyone have any ideas how to get the spreadsheet visible right from the get-go?

    This is what I code (somewhat abbreviated):
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, strQueryName, strSheetName, True
    Set appExcel = CreateObject("Excel.application")
    appExcel.Workbooks.Open strSheetName
    appExcel.Application.ScreenUpdating = True
    With appExcel.ActiveSheet
    .Name = "Charges"
    etc etc
    End With
    appExcel.Application.ScreenUpdating = True
    appExcel.ActiveWorkbook.Save
    'appExcel.Visible = True ' leave it showing for user
    appExcel.Quit
    Set appExcel = Nothing
    StartExcel Me.hwnd, strSheetName ' where this executes ShellExecute to Open the spreadsheet

    If I uncomment the Visible=True statement and comment out the Quit and StartExcel lines, then the user sees the spreadsheet just fine, but as I say, there's a tendency to leave an Excel process behind, invisible!

    Thanks.

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

    Re: Using ShellExecute to start Excel (Access 2000 VBA)

    If you use Automation to create an Excel workbook, and set all object variables to Nothing at the end of the code, the instance of Excel should only stay in memory as long as the user keeps Excel open. The moment the user quits Excel, the instance created by you should disappear.
    One other thing to look for is unqualified references to Excel objects. The code you posted is correct, e.g. the reference to appExcel.ActiveSheet. If, however, you'd use ActiveSheet without prefixing it with appExcel, that would keep the instance of Excel in memory even when the user closes it.

  3. #3
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Nottingham, UK, Nottinghamshire, England
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using ShellExecute to start Excel (Access 2000 VBA)

    Doh! You're right about the appExcel reference, Hans. I was just about to reply that I knew that (there are mentions via a Google search) and it wasn't that when I thought that I'd better just check again, and lo and behold, there it was. Because I have everything inside a 'With appExcel.Activesheet' block, it amounted to a missing period at the tailend of a statement!

    Oh well, just goes to show, you can never do too much checking! Thanks for the (extremely prompt) help, Hans, as always. Much appreciated.

Posting Permissions

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