Close Excel after TransferSpreadsheet
in [Forms]
|
Prev: ADHResize2K error
Next: Highlight Current Row
From: Rob Parker on 11 Jun 2006 03:53 Apologies in advance for a long posting ... Running Access 20002 (XP), SP3 on a Windows XP Pro (SP2 and later updates) machine: I've got a small subroutine which reads all sheets in an Excel file and transfers the data into a table; this is called by routines which ask for a specific Excel file, or which loop through all files in a specific folder. The code is shown at the end of this post. My problem is that this code does not close the Excel process which is created. This is not normally a problem, but can be under some circumstances. And, it seems to me, it is generally bad practice to create processes which are not closed when completed - if this happens often enough (eg. on a machine which is normally left running and is not re-booted each day) there will come a time when it falls in a heap. But enough of that - details of my problem are: If I read an Excel file, when Excel is not open, a single Excel process is created and remains after the routine finishes. If I immediately re-read a file (via a command button on the same form), another Excel process is briefly created while the file is read, and then closes. However, if I do something else between the reads, such as closing and re-opening the calling form, or switching to a different application and then back to Access, the next attempt to read a file will often cause Access to hang, and both the original and new Excel processes remain open. If I open Excel itself, the next attempt to read an Excel file from Access will certainly hang, regardless of whether the new Excel application is still open or has been closed. The hung state can be switched back to active by closing both the Excel processes via the Windows Task Manager (closing one is usually not sufficient, but under some circumstances it is - I haven't fully explored that track yet); when this is done my GetXLSData routine generates Error 462 (The remote server machine does not exist or is unavailable). After terminating the Excel process(es), the next time Excel itself is opened it shows the Recovered Documents panel - not exactly what I want to happen ;-). The initial Excel process also remains open after Access is closed. If Access is restarted, the read process still works as described above. The GetXLSData routine is shown below; note the the xlWrk.Close and xlApp.Quit statements seem to have no effect. Neither does declaring xlApp, xlWrk and xlSht as objects, rather than Excel-specific objects. And yet another strangeness: if I uncomment the xlApp.Visible = True statement, the Excel application opens and closes as the file is read, and then about 30 seconds later a 'File Now Available' dialog box appears, stating "'Testfile.xls' is now available for editing. Choose Read-Write to open it for editing.", with Read-Write and Cancel buttons available. Selecting either button causes an Excel application titlebar to appear, and that Excel application immediately hangs; fortunately, it can be closed via the Close button at the right-hand-end of the title-bar. What have I missed? How can I get the Excel process created by this routine to close - I'm pretty sure if that happened, all the other problems would disappear. All this makes me think I'm at a location from an ancient main-frame Dungeon text-adventure game - I'm at Wits End ;-) Rob Private Sub GetXLSData(strFilename As String) ' Author : Rob Parker, 09 Jun 2006 ' Last Edit : 09 Jun 2006 ' Purpose : Read data from all sheets in an .xls file '--------------------------------------------------------------------------------------- ' Dim xlApp As Excel.Application Dim xlWrk As Excel.Workbook Dim xlSht As Excel.Worksheet Dim strSht As String On Error GoTo GetXLSData_Error Set xlApp = CreateObject("Excel.Application") ' xlApp.Visible = True With xlApp Set xlWrk = .Workbooks.Open(strFilename) Debug.Print xlWrk.Worksheets.Count For Each xlSht In xlWrk.Worksheets strSht = xlSht.Name DoCmd.TransferSpreadsheet acImport, , "tblFSFTTest", strFilename, True, strSht & "!" Next End With ' xlWrk.Close Set xlWrk = Nothing xlApp.Quit Set xlApp = Nothing ExitPoint: Exit Sub GetXLSData_Error: If Err.Number = 2391 Then If MsgBox("Sheet " & strSht & " in " & strFilename & vbNewLine _ & "does not match the fields in the import table." _ & vbNewLine & vbNewLine & "It has not been copied into this database." _ & vbNewLine & vbNewLine & "Select OK to continue with next sheet in this file," _ & vbNewLine & "or Cancel to move to the next file.", _ vbOKCancel + vbExclamation, "") = vbOK Then Resume Next Else ' xlWrk.Close Set xlWrk = Nothing xlApp.Quit Set xlApp = Nothing Resume ExitPoint End If Else MsgBox "Error " & Err.Number & " (" & Err.Description & ")" & vbNewLine _ & "in procedure GetXLSData" & vbNewLine _ & "of VBA Document Form_frmReadSpreadsheet" Resume ExitPoint End If Resume 'set this line as next statement, when debugging after Ctrl-Brk, to find error line End Sub
From: DW on 12 Jun 2006 19:22 Dont feel bad, sometimes I have the same problem while importing data from Excel, Sometimes Access will start the process and then "hang". -but not Excel- The only way I have found to deal with this is to Kill the process. One thing that I did find that reduced the reoccurances was to upgrade my excel program on my machine to a newer version since this didnt occur on machines that was running a later version. Stumped as Well DW
From: Rob Parker on 13 Jun 2006 04:04 Hi DW, Thanks for the comments. It's not that I feel bad, it's that if I distribute an application that works like this the end-users are going to think I've stuffed up :-( If I can't solve it, I'll just use a simpler method and force them to have only one sheet in each .xls file - the straight TransferSpreadsheet works fine. But it would have been nice to include the capability to read all the sheets in a workbook. And you're right - it's getting better ;-). A previous application in Access 97, with Office 97, used to regularly hang/crash when interactions with Excel were involved - I had a large high-lighted section in its User Manual explaining all the possible nastiness. Now, with Access XP/Office XP it's much less of an issue (and, as I said, it may even go un-noticed unless the user does some specific things). Maybe by Access/Office 2015 it will be cured completely ;-) I'm surprised that there's not more posts on this issue - or is Office Automation much less commonly used between Access and Excel than between Access and Word or Outlook. Rob "DW" <davidw(a)mcintoshconstruction.net> wrote in message news:O3uGKcnjGHA.456(a)TK2MSFTNGP05.phx.gbl... > Dont feel bad, sometimes I have the same problem while importing data from > Excel, Sometimes Access will start the process and then "hang". -but not > Excel- > The only way I have found to deal with this is to Kill the process. > > One thing that I did find that reduced the reoccurances was to upgrade my > excel program on my machine to a newer version since this didnt occur on > machines that was running a later version. > > Stumped as Well > DW >
From: DW on 13 Jun 2006 09:29 Ive got 3 apps that I have distributed and all intervene with Excel. The market that I am in makes Excel intergration a must. I also have made efforts to warn end users of the problem before they execute the code that involves Excel. I do this by a custom form(Ive got it where it opens before my code opens Excel for the first time) that explains about previous versions and thier conflicts. You are right, there is not enough information available for this problem, other than keeping all of your software current which sometimes gets to be expensive. If you do run into a fix email me at davidw(a)mcintoshconstruction.net and I will do the same DW "Rob Parker" <NOSPAMrobpparker(a)optusnet.com.au.REMOVETHIS> wrote in message news:eDXX1$rjGHA.1208(a)TK2MSFTNGP02.phx.gbl... > Hi DW, > > Thanks for the comments. It's not that I feel bad, it's that if I > distribute an application that works like this the end-users are going to > think I've stuffed up :-( > > If I can't solve it, I'll just use a simpler method and force them to have > only one sheet in each .xls file - the straight TransferSpreadsheet works > fine. But it would have been nice to include the capability to read all > the sheets in a workbook. > > And you're right - it's getting better ;-). A previous application in > Access 97, with Office 97, used to regularly hang/crash when interactions > with Excel were involved - I had a large high-lighted section in its User > Manual explaining all the possible nastiness. Now, with Access XP/Office > XP it's much less of an issue (and, as I said, it may even go un-noticed > unless the user does some specific things). Maybe by Access/Office 2015 > it will be cured completely ;-) > > I'm surprised that there's not more posts on this issue - or is Office > Automation much less commonly used between Access and Excel than between > Access and Word or Outlook. > > Rob > > > "DW" <davidw(a)mcintoshconstruction.net> wrote in message > news:O3uGKcnjGHA.456(a)TK2MSFTNGP05.phx.gbl... >> Dont feel bad, sometimes I have the same problem while importing data >> from Excel, Sometimes Access will start the process and then "hang". -but >> not Excel- >> The only way I have found to deal with this is to Kill the process. >> >> One thing that I did find that reduced the reoccurances was to upgrade my >> excel program on my machine to a newer version since this didnt occur on >> machines that was running a later version. >> >> Stumped as Well >> DW >> > >
From: John Nurick on 13 Jun 2006 14:31
Hi Rob, IME it's never a bad idea to explicitly close all workbooks before quitting Excel. It's at least theoretically possible for an add-in to open a workbook behind your back, and there are circumstances in which Excel recalculates a workbook on opening and then asks whether to save changes on closing even though you haven't done anything yourself. So try something like this: Set xlWrk = Nothing Do While xlApp.Workbooks.Count > 0 xlApp.Workbooks(1).Close False 'close without saving Loop xlApp.Quit Set xlApp = Nothing Alternatively, search Google Groups for loop through worksheet names audibly and you'll find a cunning function that retrieves worksheet names without automating Excel. On Sun, 11 Jun 2006 17:53:03 +1000, "Rob Parker" <NOSPAMrobpparker(a)optusnet.com.au.REMOVETHIS> wrote: >Apologies in advance for a long posting ... > >Running Access 20002 (XP), SP3 on a Windows XP Pro (SP2 and later updates) >machine: > >I've got a small subroutine which reads all sheets in an Excel file and >transfers the data into a table; this is called by routines which ask for a >specific Excel file, or which loop through all files in a specific folder. >The code is shown at the end of this post. > >My problem is that this code does not close the Excel process which is >created. This is not normally a problem, but can be under some >circumstances. And, it seems to me, it is generally bad practice to create >processes which are not closed when completed - if this happens often enough >(eg. on a machine which is normally left running and is not re-booted each >day) there will come a time when it falls in a heap. But enough of that - >details of my problem are: > >If I read an Excel file, when Excel is not open, a single Excel process is >created and remains after the routine finishes. If I immediately re-read a >file (via a command button on the same form), another Excel process is >briefly created while the file is read, and then closes. However, if I do >something else between the reads, such as closing and re-opening the calling >form, or switching to a different application and then back to Access, the >next attempt to read a file will often cause Access to hang, and both the >original and new Excel processes remain open. If I open Excel itself, the >next attempt to read an Excel file from Access will certainly hang, >regardless of whether the new Excel application is still open or has been >closed. The hung state can be switched back to active by closing both the >Excel processes via the Windows Task Manager (closing one is usually not >sufficient, but under some circumstances it is - I haven't fully explored >that track yet); when this is done my GetXLSData routine generates Error 462 >(The remote server machine does not exist or is unavailable). After >terminating the Excel process(es), the next time Excel itself is opened it >shows the Recovered Documents panel - not exactly what I want to happen ;-). > >The initial Excel process also remains open after Access is closed. If >Access is restarted, the read process still works as described above. > >The GetXLSData routine is shown below; note the the xlWrk.Close and >xlApp.Quit statements seem to have no effect. Neither does declaring xlApp, >xlWrk and xlSht as objects, rather than Excel-specific objects. > >And yet another strangeness: if I uncomment the xlApp.Visible = True >statement, the Excel application opens and closes as the file is read, and >then about 30 seconds later a 'File Now Available' dialog box appears, >stating "'Testfile.xls' is now available for editing. Choose Read-Write to >open it for editing.", with Read-Write and Cancel buttons available. >Selecting either button causes an Excel application titlebar to appear, and >that Excel application immediately hangs; fortunately, it can be closed via >the Close button at the right-hand-end of the title-bar. > >What have I missed? How can I get the Excel process created by this routine >to close - I'm pretty sure if that happened, all the other problems would >disappear. > >All this makes me think I'm at a location from an ancient main-frame Dungeon >text-adventure game - I'm at Wits End ;-) > >Rob > > >Private Sub GetXLSData(strFilename As String) >' Author : Rob Parker, 09 Jun 2006 >' Last Edit : 09 Jun 2006 >' Purpose : Read data from all sheets in an .xls file >'--------------------------------------------------------------------------------------- >' >Dim xlApp As Excel.Application >Dim xlWrk As Excel.Workbook >Dim xlSht As Excel.Worksheet >Dim strSht As String > > On Error GoTo GetXLSData_Error > > Set xlApp = CreateObject("Excel.Application") >' xlApp.Visible = True > With xlApp > Set xlWrk = .Workbooks.Open(strFilename) > Debug.Print xlWrk.Worksheets.Count > For Each xlSht In xlWrk.Worksheets > strSht = xlSht.Name > DoCmd.TransferSpreadsheet acImport, , "tblFSFTTest", strFilename, >True, strSht & "!" > Next > End With >' xlWrk.Close > Set xlWrk = Nothing > xlApp.Quit > Set xlApp = Nothing > >ExitPoint: > Exit Sub > >GetXLSData_Error: > If Err.Number = 2391 Then > If MsgBox("Sheet " & strSht & " in " & strFilename & vbNewLine _ > & "does not match the fields in the import table." _ > & vbNewLine & vbNewLine & "It has not been copied into this >database." _ > & vbNewLine & vbNewLine & "Select OK to continue with next sheet >in this file," _ > & vbNewLine & "or Cancel to move to the next file.", _ > vbOKCancel + vbExclamation, "") = vbOK Then > Resume Next > Else >' xlWrk.Close > Set xlWrk = Nothing > xlApp.Quit > Set xlApp = Nothing > Resume ExitPoint > End If > Else > MsgBox "Error " & Err.Number & " (" & Err.Description & ")" & vbNewLine >_ > & "in procedure GetXLSData" & vbNewLine _ > & "of VBA Document Form_frmReadSpreadsheet" > Resume ExitPoint > End If > Resume 'set this line as next statement, when debugging after Ctrl-Brk, >to find error line >End Sub > > -- John Nurick [Microsoft Access MVP] Please respond in the newgroup and not by email. |