Close Excel after TransferSpreadsheet
in [Forms]
|
Prev: ADHResize2K error
Next: Highlight Current Row
From: Rob Parker on 13 Jun 2006 18:39 Thanks for that, John, I'll give your suggestions a try later today when I've got time, and post back what happens. Now that you mention it, perhaps my problem is arising because I've got Excel set up to silently open a workbook with various custom macros; the standard xlApp.Quit/Set xlApp = Nothing which I'm using might be failing because of this. Rob "John Nurick" <j.mapSoN.nurick(a)dial.pipex.com> wrote in message news:7m0u821eajeb4obv03bnu171goom18gdi5(a)4ax.com... > 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.
From: Rob Parker on 14 Jun 2006 04:09 Hi John, Well, I've done some testing, with the following results: Your suggestion to loop through all open workbooks fails. If I uncomment my xlWrk.Close line, then Set xlWrk = Nothing before the loop, a debug.print just before the loop tells me that xlApp.Workbooks.Count = 0. So the problem wasn't some hidden workbook causing Excel to remain open. The issue of why Set xlApp = Nothing fails to close the Excel process still remains. Fortunately, the cunning function that retrieves worksheet names without automating Excel does an excellent job! Many thanks for that reference - I suspect it might be something I'll use a lot ;-) Rob For the benefit of any others watching this thread, my code is now as follows: Private Sub GetXLSData(strFilename As String) Dim vWSNames As Variant Dim vShtName As Variant On Error GoTo GetXLSData_Error vWSNames = GetWSNames(strFilename) For Each vShtName In vWSNames DoCmd.TransferSpreadsheet acImport, , "tblFSFTTest", strFilename, True, vShtName & "!" Next vShtName ExitPoint: Exit Sub GetXLSData_Error: ' specific error handling snipped for this posting End Sub And the function which makes this all work (in a separate module): '--------------------------------------------------------------------------------------- ' Author : Jake Marx, June 2004 ' Posted in : microsoft.public.excel.programming ' Purpose : Returns names of all worksheets in Excel file, without opening Excel via automation method. '--------------------------------------------------------------------------------------- ' Public Function GetWSNames(ByVal WBPath As String) As Variant Dim adCn As Object Dim adRs As Object Dim asSheets() As String Dim nShtNum As Long Dim nRows As Long Dim nRowCounter As Long Dim sSheet As String Dim sChar1 As String Dim sChar2 As String Const INDICATOR_SHEET As String = "$" Const INDICATOR_SPACES As String = "'" Set adCn = CreateObject("ADODB.Connection") With adCn .ConnectionString = "Provider=Microsoft.Jet.OLEDB" & _ ".4.0;Data Source=" & WBPath & ";Extended " & _ "Properties=""Excel 8.0;HDR=Yes""" .CursorLocation = 3 .Open End With Set adRs = adCn.OpenSchema(20) With adRs nRows = .RecordCount For nRowCounter = 0 To nRows - 1 sSheet = !TABLE_NAME sChar1 = vbNullString sChar2 = vbNullString On Error Resume Next sChar1 = Mid$(sSheet, Len(sSheet), 1) sChar2 = Mid$(sSheet, Len(sSheet) - 1, 1) On Error GoTo 0 Select Case sChar1 Case INDICATOR_SHEET sSheet = Left$(sSheet, Len(sSheet) - 1) Case INDICATOR_SPACES If sChar2 = INDICATOR_SHEET Then sSheet = Mid$(sSheet, 2, Len(sSheet) - 3) End If Case Else sSheet = vbNullString End Select If Len(sSheet) > 0 Then ReDim Preserve asSheets(nShtNum) ' Un-escape asSheets(nShtNum) = Replace(sSheet, _ INDICATOR_SPACES & INDICATOR_SPACES, _ INDICATOR_SPACES) nShtNum = nShtNum + 1 End If .MoveNext Next .Close End With adCn.Close GetWSNames = asSheets End Function "John Nurick" <j.mapSoN.nurick(a)dial.pipex.com> wrote in message news:7m0u821eajeb4obv03bnu171goom18gdi5(a)4ax.com... > 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. > <large snip of text and code form original post>
From: RoyVidar on 15 Jun 2006 12:16 Rob Parker wrote in message <#QSGtn4jGHA.4368(a)TK2MSFTNGP03.phx.gbl> : [snip] > The issue of why Set xlApp = > Nothing fails to close the Excel process still remains. [snip] I *think* the challenge stems from accesing the same file both through an automated instance of Excel, and through the transfer-thingie at the same time. Either both of them invokes Excel, or the usage of both methods on the same file creates the hiccup (methinks). To use automation to retrieve the workseet names, I think I'd stuff them (the sheet names) into for instance an array, then close/quit Excel before attempting to use the transfer-thingie. It could be a possibility some strategically placed DoEvents could yield sufficient to clean up between attempts (though I doubt it). -- Roy-Vidar
From: John Nurick on 15 Jun 2006 13:53 I'd wondered about that too. TransferSpreadsheet uses the Excel ISAM which - as far as I know - doesn't invoke an instance of Excel; but I've never got round to working out how to confirm this (short of setting up a machine with Access but not Excel and then trying to import from a workbook file). On Thu, 15 Jun 2006 18:16:27 +0200, RoyVidar <roy_vidarNOSPAM(a)yahoo.no> wrote: >Rob Parker wrote in message <#QSGtn4jGHA.4368(a)TK2MSFTNGP03.phx.gbl> : >[snip] >> The issue of why Set xlApp = >> Nothing fails to close the Excel process still remains. >[snip] > >I *think* the challenge stems from accesing the same file both through >an automated instance of Excel, and through the transfer-thingie at the >same time. Either both of them invokes Excel, or the usage of both >methods on the same file creates the hiccup (methinks). > >To use automation to retrieve the workseet names, I think I'd stuff >them >(the sheet names) into for instance an array, then close/quit Excel >before attempting to use the transfer-thingie. > >It could be a possibility some strategically placed DoEvents could >yield >sufficient to clean up between attempts (though I doubt it). -- John Nurick [Microsoft Access MVP] Please respond in the newgroup and not by email.
From: Rob Parker on 15 Jun 2006 22:07
Hi John and Roy, I can't say for certain, but it appears (from carefully watching the Process tab in Windows task Manager) that the TransferSpreadsheet method does not invoke an Excel process. However, following Roy's comment, I tried commenting out the TransferSpreadsheet line in the innermost loop of my , and simply debug.printing the name of each sheet. In that case, the first instance of Excel IS removed when the loop finishes. Thanks, Roy. Now I know what the real problem is. And, as I said in my last reply to John, the function to get the sheet names without opening Excel allows me to do what I need to. Rob "John Nurick" <j.mapSoN.nurick(a)dial.pipex.com> wrote in message news:0h7392l3hl5shh497ucvg7ehqhma8q8qqd(a)4ax.com... > I'd wondered about that too. TransferSpreadsheet uses the Excel ISAM > which - as far as I know - doesn't invoke an instance of Excel; but I've > never got round to working out how to confirm this (short of setting up > a machine with Access but not Excel and then trying to import from a > workbook file). > > On Thu, 15 Jun 2006 18:16:27 +0200, RoyVidar <roy_vidarNOSPAM(a)yahoo.no> > wrote: > >>Rob Parker wrote in message <#QSGtn4jGHA.4368(a)TK2MSFTNGP03.phx.gbl> : >>[snip] >>> The issue of why Set xlApp = >>> Nothing fails to close the Excel process still remains. >>[snip] >> >>I *think* the challenge stems from accesing the same file both through >>an automated instance of Excel, and through the transfer-thingie at the >>same time. Either both of them invokes Excel, or the usage of both >>methods on the same file creates the hiccup (methinks). >> >>To use automation to retrieve the workseet names, I think I'd stuff >>them >>(the sheet names) into for instance an array, then close/quit Excel >>before attempting to use the transfer-thingie. >> >>It could be a possibility some strategically placed DoEvents could >>yield >>sufficient to clean up between attempts (though I doubt it). > > -- > John Nurick [Microsoft Access MVP] > > Please respond in the newgroup and not by email. |