Results 1 to 9 of 9
  1. #1
    Join Date
    May 2011
    Posts
    47

    Unanswered: Close an Excel 2007 Spreadsheet from Access 2007

    Any help would be much appreciated... I open an Excel 2007 spreadsheet from Access 2007 using the following VBA:

    Dim excel_path As String
    Dim my_path As String

    excel_path = "C:\Program Files\Microsoft Office\OFFICE12\EXCEL.EXE"
    my_path = "C:\Documents and Settings\folder_x\My Documents\Test Shell Function.xls"

    Call Shell("""" & excel_path & """" & " " & """" & my_path, vbNormalFocus)

    Subsequent to opening the file (and other Excel files) I'll be doing a number of vLookUps (in Excel). Once complete I'll want to close the Excel files. In test I've tried to close an Excel spreadsheet a number of ways but keep getting errors. This seems like it should be simple but I can't seem to close the file from my VBA. Any guidance would be appreciated.

    Jack Kent
    Lavallette, NJ

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Try opening Excel via Automation (COM+):
    Code:
    Dim xlApp As Excel.Application
    Dim xlWrksht As Excel.Worksheet
    
    Set xlApp = CreateObject("Excel.Application")
    my_path = "C:\Documents and Settings\folder_x\My Documents\Test Shell Function.xls"
    Set xlWrkBk = xlApp.Workbooks.Open(my_path)
    When you need to close Excel, use:
    Code:
    xlApp.Quit
    Set xlApp = Nothing
    Have a nice day!

  3. #3
    Join Date
    May 2011
    Posts
    47

    Close Excel From Access

    Thanks for your reply... I tried your code. Something was going on because I had an hourglass for a short period of time, but Excel did not open.

  4. #4
    Join Date
    May 2011
    Posts
    47

    Close Excel From Access

    Thanks for your reply... I tried your code. Something was going on because I had an hourglass for a short period of time, but Excel did not open.

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Simply try adding:
    Code:
    Set xlApp = CreateObject("Excel.Application")
    xlApp.Visible = True
    You can also check whether Exell is in the Windows task list or not.
    Have a nice day!

  6. #6
    Join Date
    May 2011
    Posts
    47

    Close Excel From Access

    Thanks... That's perfect for the open since I'll want to open Excel in the background (i.e. without True). I still however have a problem closing the Excel spreadsheet. I get an error message telling me that an object is required. I tried adding the path to the close event but I continue to get the same message.
    Jack

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    If you don't close Excel in the same procedure as the one where you open it, the object variable xlApp must be accessible from both procedures (i.e. the one where Excel is open and the one where Excel is closed). The simplest way to achieve this is by declaring xlApp in the declaration section of a module (i.e. ouside a Sub or Function procedure), like this:
    Code:
    Public xlApp As Excel.Application
    See: Scope of variables in Visual Basic for Applications
    Have a nice day!

  8. #8
    Join Date
    May 2011
    Posts
    47

    Close Excel

    All works... Many thanks.

  9. #9
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

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