Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2003
    Posts
    33

    Unanswered: Trouble closing open Excel object via Access

    I have a module that opens a locked excel worksheet. It then creates a linked excel table used to populate a list box. Items selected from the list box are then written to a table. Once the user has made the selection and they are added to the table the dialog box closes and there is a function that deletes the link and closes open instance of excel. Or at least it is supposed to.

    Anyway the excel worksheet that I open is set to not be visible. Then when I open another workbook it will not open. I have to go into task manager and end the Excel process before I can open another excel workbook. Everything I have read says the method that I am using should work. Has anyone else encountered this problem and is there a solution?

    Thank you,
    Gina

  2. #2
    Join Date
    Nov 2003
    Location
    LONDON
    Posts
    238

    Re: Trouble closing open Excel object via Access

    Originally posted by glee0511
    I have a module that opens a locked excel worksheet. It then creates a linked excel table used to populate a list box. Items selected from the list box are then written to a table. Once the user has made the selection and they are added to the table the dialog box closes and there is a function that deletes the link and closes open instance of excel. Or at least it is supposed to.

    Anyway the excel worksheet that I open is set to not be visible. Then when I open another workbook it will not open. I have to go into task manager and end the Excel process before I can open another excel workbook. Everything I have read says the method that I am using should work. Has anyone else encountered this problem and is there a solution?

    Thank you,
    Gina
    If you have opened Excel using VBA along the lines of

    Dim objXL as objxl As Excel.Application
    Set objxl = New Excel.Application

    to close Excel use this line to destroy the object:

    Set objxl = Nothing

    If you set Excel Visible to False, it does just that and makes Excel invisible! Trying to open another Excel document won't work until Excel is visible again

  3. #3
    Join Date
    Jul 2003
    Posts
    33
    Thank you, but unfortunately that is the method that I'm using but for some reason setting it to "nothing" is not working.

    Any ideas why?

    Thanks,
    Gina

  4. #4
    Join Date
    Nov 2003
    Location
    LONDON
    Posts
    238
    Originally posted by glee0511
    Thank you, but unfortunately that is the method that I'm using but for some reason setting it to "nothing" is not working.

    Any ideas why?

    Thanks,
    Gina
    Not sure why that isn't working as that is the correct way to close Excel. You could try objxl.Quit first to try and force Excel to close itself

  5. #5
    Join Date
    Jul 2003
    Posts
    33
    This is my code that was already there.


    Public Sub CloseExcelObj()

    myExcelApp.Quit
    Set myExcelApp = Nothing

    End Sub


    I'm completely out of ideas. Could this be a Windows XP thing?

  6. #6
    Join Date
    Jul 2003
    Location
    Amsterdam, Nederland
    Posts
    450
    Me.MousePointer = 0
    Set objExcel = Excel.Application
    objExcel.Visible = True

    objExcel.Workbooks(1).Close SaveChanges:=True, FileName:=App.Path & "\MyName" & Format(Now, "DD-mm-yyyy HH nn ss") & ".xls"

    objExcel.Application.Quit
    objExcel.Quit
    Set objExcel = Nothing
    End Sub

  7. #7
    Join Date
    Jul 2003
    Posts
    33
    I tried that and got an "application-defined or Object-defined" error on the objExcel.Workbooks.close.

    I tried commenting that out. the objExcel.visible runs fine then the excel object appears to close. But when I go to open an excel workbook the menu bar and everything is visible but not the workbook itself.

    ???????

  8. #8
    Join Date
    Jul 2003
    Posts
    33
    This is the code I am using, perhaps I am doing something else incorrectly. I had to use set myExcelApp=CreateObject("excel.application") because when I used just
    Set myExcelApp=excel.application I get an a "Object required" error.

    Option Compare Database
    Public myExcelApp As Object

    Public Sub OpenExcelObj()



    Set myExcelApp = CreateObject("excel.application")
    myExcelApp.Workbooks.Open FileName:="L:\Miscellaneous\Sub List\Subs October 30, 2003 JAS.xls", ReadOnly:=False, Password:="commodore", WriteResPassword:="02466"
    myExcelApp.Visible = False
    DoCmd.TransferSpreadsheet acLink, 8, "SubsList", "L:\Miscellaneous\Sub List\Subs October 30, 2003 JAS.xls", True, ""



    End Sub


    Public Sub CloseExcelObj()


    myExcelApp.Visible = True
    myExcelApp.Workbooks.Close FileName:="L:\Miscellaneous\Sub List\Subs October 30, 2003 JAS.xls"
    myExcelApp.Application.Quit
    myExcelApp.Quit
    Set myExcelApp = Nothing

    End Sub

Posting Permissions

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