Unable to close Excel from Access 2007

[Phred]

My skill level is moderate.

My access DB opens and imports an Excel spreadsheet successfully. When I test it again it says Excel is in use.

I checked the Task Manager, no Excel. I shut down my Access DB and restarted. No success.

Finally I shut everything down and rebooted. No success. Still says Excel is open.

I have read most of the posts and tried several code suggestions. None work.

I have attached a screen snap of error messages. My variable TxtFilePath works fine.

This import code has worked 2 times.

Private Sub CmdImport_Click()
Dim Dlg As FileDialog
Dim txtFilePath As String

Set Dlg = Application.FileDialog(msoFileDialogFilePicker)
With Dlg
.title = "Select the file you want to import"
.AllowMultiSelect = False


If .Show = -1 Then
txtFilePath = .InitialFileName
Else

Exit Sub
End If
End With

DoCmd.TransferSpreadsheet acImport, 10, "Tbl_ImportVendorsTemp", txtFilePath, True, "A1:I407"
End Sub

I have tried many different chunks of close Excel code. I understand Excel may be running invisible. I read about that but it was above my level to understand or implement. After rebooting I am not sure that is actually my problem.

I need to test and see if Excel is open. Then I need to close or Kill Excel. Then import the new spreadsheet. It is fine if Excel opens and displays the spreadsheet.

No variation of the code below works.

Dim objXL As Object
Dim xlWB As Object

Set objXL = CreateObject("Excel.Application")
Set xlWB = objXL.Workbooks.Open("C:\Temp\Book1.xlsx")
xlWB.Close False
objXL.Quit

Thanks, Phred

[June7]

Check this thread which discusses this issue with the TransferSpreadsheet method
http://forums.aspfree.com/microsoft-...el-413629.html

[Phred]

I read the issues from your link carefully. A lot of it I don't understand. The code is about exporting to Excel.

I copied the code and tried commenting out the Export parts. I placed the code in a Private Sub. Don't know if that makes a difference. Each line below errors out Compile Error User-Defined type not defined.

Dim oExcel As Excel.Application
Dim oBook As Excel.Workbook
Dim oSheet As Excel.Worksheet
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim varField As Variant
Dim lngRow As Long, lngCol As Long
Dim wb As String

The only thing I saw about closing Excel is the end. Is this the part you saw as relevant to me? I have no problem opening Excel, locating the spreadsheet, importing the sheet to my temp table, I just can't close Excel.

oExcel.Visible = True 'or oExcel.Quit
Set oSheet = Nothing
Set oBook = Nothing
Set oExcel = Nothing


Thanks for the effort but without more explanation (or experience on my part) I can't see how this helps.


Phred

[June7]

User-Defined type error usually indicates that a VBA Reference Library is not set. Do you have these selected:
Microsoft Excel 14.0 Object Library
Microsoft ActiveX Data Objects 2.8 Library

I tried your original code and I get the User-Defined error on the Dlg declaration. I had to set the Microsoft Office 14.0 Object Library.

Everything worked when I made this change: txtFilePath = .SelectedItems(1)

Step debug and you will see that txtFilePath has the path to the file but not the filename itself. Code is incomplete. Check this article http://msdn.microsoft.com/en-us/libr...ffice.11).aspx

[June7]

User-Defined type error usually indicates that a VBA Reference Library is not set. Do you have these selected:
Microsoft Excel 14.0 Object Library
Microsoft ActiveX Data Objects 2.8 Library

I tried your original code and I get the User-Defined error on the Dlg declaration. I had to set the Microsoft Office 14.0 Object Library.

Step bebug shows txtFilePath has the path to the file but not the filename itself. Code is incomplete. Check this article http://msdn.microsoft.com/en-us/libr...ffice.11).aspx

Everything worked when I made this change: txtFilePath = .SelectedItems(1)