Close Excel after TransferSpreadsheet

[Rob Parker]

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

 

[DW]

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

 

[Rob Parker]

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]

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
(e-mail address removed)

and I will do the same
DW

 

[John Nurick]

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.

 

[Rob Parker]

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

 

[Rob Parker]

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

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>

 

[RoyVidar]

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]

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).

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).

 

[Rob Parker]

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

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).

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]

Thanks for the feedback, Rob.

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

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).

Rob Parker wrote in message <#[email protected]> :
[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).

 

[Rob Parker]

You're welcome. I figure that every little bit helps, for everyone who
might be following this.

My problem is trying to remember all the little bits ;-)

Rob

Thanks for the feedback, Rob.

<snip>

 

[John Nurick]

My problem is trying to remember all the little bits ;-)

Isn't that what databases are for? <g>