Excel remains open

  • Thread starter Thread starter John
  • Start date Start date
J

John

I am using code I found in other posts by Alex Dybenko to import from Excel
into my access database. The Excel file is password protected.

This is my code:

'strFile and strPassword are supplied on a form prior to running the code.

Public Sub Test(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, True, "test!"
oWb.Close SaveChanges:=False
oExcel.Quit
Set oWb = Nothing
Set oExcel = Nothing
End Sub

The import side of things works fine as the new table is appearing in my db,
but there is a excel.exe sitting in the background stopping the code running
again.

The data I am importing is not always on the first sheet, hence the "test!"
in the transferspreadsheet - I am wondering if this is what is causing the
problem?

Any help would be much appreciated.
Cheers
John
 
John said:
I am using code I found in other posts by Alex Dybenko to import from
Excel
into my access database. The Excel file is password protected.

This is my code:

'strFile and strPassword are supplied on a form prior to running the
code.

Public Sub Test(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, True, "test!"
oWb.Close SaveChanges:=False
oExcel.Quit
Set oWb = Nothing
Set oExcel = Nothing
End Sub

The import side of things works fine as the new table is appearing in
my db,
but there is a excel.exe sitting in the background stopping the code
running
again.

The data I am importing is not always on the first sheet, hence the
"test!"
in the transferspreadsheet - I am wondering if this is what is causing
the
problem?

Any help would be much appreciated.
Cheers
John
Click to expand...


What do you mean "there is an excel.exe sitting in the background
stopping the code running again"

Is there an Excel.exe still present in your task list?

Even if you hadn't closed Excel and destroyed the Excel object(which it
looks like you have), I don't see how having it "sitting in the
background" prevents the code from running again. You can have multiple
instances of an Excel object existing simultaneously. Often when I'm
debugging code and break out prior to the close/=Nothing statements, I
end up with 3 or 4 instances of Excel in my Windows task list which I
have to manually shut down but it doesn't prevent my code from
re-running.

Maybe I'm not clear on what's happening in your case ...


--
 
John said:
I am using code I found in other posts by Alex Dybenko to import from
Excel into my access database. The Excel file is password protected.

This is my code:

'strFile and strPassword are supplied on a form prior to running the
code.

Public Sub Test(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, True, "test!"
oWb.Close SaveChanges:=False
oExcel.Quit
Set oWb = Nothing
Set oExcel = Nothing
End Sub

The import side of things works fine as the new table is appearing in
my db, but there is a excel.exe sitting in the background stopping
the code running again.

The data I am importing is not always on the first sheet, hence the
"test!" in the transferspreadsheet - I am wondering if this is what
is causing the problem?

Any help would be much appreciated.
Cheers
John
Click to expand...

I think the reason is that you have the same excel file open through
two processes at the same time.

Once through the Excel automation code, and once through the
DoCmd.TransferThingie.

If you're not doing anything with your automation code, why not use
only

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
"Import", strFile, True, "test!"

If you need to do some formatting etc, try recreating your routine
so that only one process at the time has the file open.
 
Change these lines of code:

oExcel.Quit
Set oWb = Nothing
Set oExcel = Nothing


to these lines of code:

Set oWb = Nothing
oExcel.Quit
Set oExcel = Nothing


It's always best to destroy an object that is a child of another object
before you "quit" or "close" the parent object.
 
Back
Top