Importing Excel data into Access

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

George - 20 Jun 2005 22:26 GMT

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

Reply to this Message

Ken Snell [MVP] - 20 Jun 2005 22:53 GMT

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

Signature

Ken Snell
<MS ACCESS MVP>

> I'm using this logic to import Excel data into Access.
>
[quoted text clipped - 17 lines]
>
> Expand AllCollapse All

Reply to this Message

George - 28 Jun 2005 15:47 GMT

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?

> You need to set the oWb object to Nothing before you quit EXCEL:
>
[quoted text clipped - 33 lines]
> >
> > Expand AllCollapse All

Reply to this Message

Ken Snell [MVP] - 29 Jun 2005 01:01 GMT

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.

Signature

Ken Snell
<MS ACCESS MVP>

> Thanks for the reply. I tried this and I still have a problem. If I
> comment
[quoted text clipped - 39 lines]
>> >
>> > Expand AllCollapse All

Reply to this Message

Ken Snell [MVP] - 29 Jun 2005 14:01 GMT

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

Signature

Ken Snell
<MS ACCESS MVP>

>I don't think it will make a difference, but let's try this minor change:
>
[quoted text clipped - 84 lines]
>>> >
>>> > Expand AllCollapse All

Reply to this Message

George - 29 Jun 2005 15:26 GMT

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.

> 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
[quoted text clipped - 113 lines]
> >>> >
> >>> > Expand AllCollapse All

Reply to this Message

Ken Snell [MVP] - 29 Jun 2005 16:39 GMT

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

Signature

Ken Snell
<MS ACCESS MVP>

> 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
[quoted text clipped - 140 lines]
>> >>> >
>> >>> > Expand AllCollapse All

Reply to this Message

Ken Snell [MVP] - 29 Jun 2005 16:42 GMT

Also, try opening the EXCEL file in ReadOnly mode.

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

Signature

Ken Snell
<MS ACCESS 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?
[quoted text clipped - 152 lines]
>>> >>> >
>>> >>> > Expand AllCollapse All

Reply to this Message