Importing Excel data into Access

[George]

I'm using this logic to import Excel data into Access.

Public Sub ImportProtected(strFile As String, strPassword As String)
Dim oExcel As Object, oWb As Object

Set oExcel = CreateObject("Excel.Application")
Set oWb = oExcel.Workbooks.Open(FileName:=strFile, _
Password:=strPassword)
DoCmd.TransferSpreadsheet acImport, _
acSpreadsheetTypeExcel9, "Import", strFile, -1
oWb.Close
oExcel.Quit
Set oExcel = Nothing
End Sub

It works great but it leaves an instance open of Excel in my task list.
Can someone tell me what is wong with this logic and why it is not actually
exiting Excel?



Expand AllCollapse All

 

[Ken Snell [MVP]]

You need to set the oWb object to Nothing before you quit EXCEL:


Public Sub ImportProtected(strFile As String, strPassword As String)
Dim oExcel As Object, oWb As Object

Set oExcel = CreateObject("Excel.Application")
Set oWb = oExcel.Workbooks.Open(FileName:=strFile, _
Password:=strPassword)
DoCmd.TransferSpreadsheet acImport, _
acSpreadsheetTypeExcel9, "Import", strFile, -1
oWb.Close
Set oWb = Nothing
oExcel.Quit
Set oExcel = Nothing
End Sub

 

[George]

Thanks for the reply. I tried this and I still have a problem. If I comment
out
the TransferSpreadsheet line it closes just fine. Any other ideas?

 

[Ken Snell [MVP]]

I don't think it will make a difference, but let's try this minor change:

Public Sub ImportProtected(strFile As String, strPassword As String)
Dim oExcel As Object, oWb As Object
On Error Resume Next
Set oExcel = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
Err.Clear
Set oExcel = CreateObject("Excel.Application")
End If
On Error GoTo 0
Set oWb = oExcel.Workbooks.Open(FileName:=strFile, _
Password:=strPassword)
DoCmd.TransferSpreadsheet acImport, _
acSpreadsheetTypeExcel9, "Import", strFile, -1
oWb.Close
Set oWb = Nothing
oExcel.Quit
Set oExcel = Nothing
End Sub


If this doesn't fix the problem, then try this:

Public Sub ImportProtected(strFile As String, strPassword As String)
Dim oExcel As Object, oWb As Object
On Error Resume Next
Set oExcel = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
Err.Clear
Set oExcel = CreateObject("Excel.Application")
End If
On Error GoTo 0
Set oWb = oExcel.Workbooks.Open(FileName:=strFile, _
Password:=strPassword)
DoCmd.TransferSpreadsheet acImport, _
acSpreadsheetTypeExcel9, "Import", strFile, -1
DoEvents
oWb.Close
Set oWb = Nothing
oExcel.Quit
Set oExcel = Nothing
End Sub


I have not found other solutions for this issue, and am asking some MS
experts about it.
--

Ken Snell
<MS ACCESS MVP>

 

[Ken Snell [MVP]]

My fellow experts have suggested another change to the code...telling the
EXCEL workbook to close without saving any changes. If the workbook that you
open has formulas in it, they may recalculate and thus the workbook is
asking the user if you want to save the changes, but your code of course
ignores that request, thus the workbook remains open invisibly waiting for
an answer.

I've added False as the "save changes" argument for the oWb.Close line:

Public Sub ImportProtected(strFile As String, strPassword As String)
Dim oExcel As Object, oWb As Object
On Error Resume Next
Set oExcel = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
Err.Clear
Set oExcel = CreateObject("Excel.Application")
End If
On Error GoTo 0
Set oWb = oExcel.Workbooks.Open(FileName:=strFile, _
Password:=strPassword)
DoCmd.TransferSpreadsheet acImport, _
acSpreadsheetTypeExcel9, "Import", strFile, -1
DoEvents
oWb.Close False
Set oWb = Nothing
oExcel.Quit
Set oExcel = Nothing
End Sub

--

Ken Snell
<MS ACCESS MVP>

 

[George]

Ken, I really appreciate all your help with this. Last week I had changed
the code and added the Doevents and oWB.Close SaveChanges:=False. Note your
recomendation is oWb.Close False but I think oWB.Close SaveChanges:=False is
the same. I tried just using oWb.Close False and got the same results.
Yesterday I added
If Err.Number <> 0 Then
Err.Clear
Set oExcel = CreateObject("Excel.Application")
End If
On Error GoTo 0

With all this still leaves an instance of Excel open.

I found another frustrated user is having the same problem. See the nine
threads in "Excel Object Will Not Close (I did read other threads) in
access.modulesdaovba" http://support.microsoft.com/newsgroups/?pr=915. As
you can see I posted there also.

Thanks again.

 

[Ken Snell [MVP]]

Does the EXCEL file that you're opening have any links to other workbooks?
Does that file run any EXCEL VBA macros when it's opened?

I appreciate the feedback regarding the other suggestions. Sorry that they
haven't worked, but we'll keep looking for an answer.

Yes,
oWB.Close SaveChanges:=False
is the same as
oWB.Close False

 

[Ken Snell [MVP]]

Also, try opening the EXCEL file in ReadOnly mode.

Set oWb = oExcel.Workbooks.Open(FileName:=strFile, _
Password:=strPassword, ReadOnly:=True)