Closing Excell from Access

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

John

I'm am stuck on trying to get the instance of Excel that I am using to copy
data over to Access to close once I am done. No matter what I try, when I
look in my Task Manager there is always a hidden copy of Excel running.

Here is my code, any help would be greatly apprechiated.

Sub ImportFile()
'Asks user to select the import file and then uploads the information into
the Import table

Dim MyRS As DAO.Recordset
Dim ExcelFile As String
Dim fd As FileDialog
Dim result As Variant

Set fd = Application.FileDialog(msoFileDialogFilePicker)

'Create Open File window
With fd
.Title = "Select the Import Excel file from Card Services"
.Filters.Add "Excel File", "*.xls"
.FilterIndex = 2
.AllowMultiSelect = False
.InitialFileName = CurrentProject.Path
result = .Show
If (result <> 0) Then
ExcelFile = Trim(.SelectedItems.Item(1))
End If
End With

'Create Excel Application and open Import Excel file
Dim objXL As Excel.Application
'Set objWkb = Workbooks.Open(ExcelFile)
Set objXL = CreateObject("Excel.Application")
objXL.ScreenUpdating = True
objXL.Visible = True
objXL.Workbooks.Open (ExcelFile)

'Transfers Excel data to Access
DoCmd.SetWarnings False
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel5, "Import",
ExcelFile, True
DoCmd.SetWarnings True

'Close Excel Application
objXL.ActiveWorkbook.Close False
objXL.Quit
Set objXL = Nothing
End Sub
 
John said:
I'm am stuck on trying to get the instance of Excel that I am using to
copy
data over to Access to close once I am done. No matter what I try, when I
look in my Task Manager there is always a hidden copy of Excel running.

Here is my code, any help would be greatly apprechiated.

Sub ImportFile()
'Asks user to select the import file and then uploads the information into
the Import table

Dim MyRS As DAO.Recordset
Dim ExcelFile As String
Dim fd As FileDialog
Dim result As Variant

Set fd = Application.FileDialog(msoFileDialogFilePicker)

'Create Open File window
With fd
.Title = "Select the Import Excel file from Card Services"
.Filters.Add "Excel File", "*.xls"
.FilterIndex = 2
.AllowMultiSelect = False
.InitialFileName = CurrentProject.Path
result = .Show
If (result <> 0) Then
ExcelFile = Trim(.SelectedItems.Item(1))
End If
End With

'Create Excel Application and open Import Excel file
Dim objXL As Excel.Application
'Set objWkb = Workbooks.Open(ExcelFile)
Set objXL = CreateObject("Excel.Application")
objXL.ScreenUpdating = True
objXL.Visible = True
objXL.Workbooks.Open (ExcelFile)

'Transfers Excel data to Access
DoCmd.SetWarnings False
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel5, "Import",
ExcelFile, True
DoCmd.SetWarnings True

'Close Excel Application
objXL.ActiveWorkbook.Close False
objXL.Quit
Set objXL = Nothing
End Sub
Click to expand...


Is that all the relevant code? Is there any other code before you close and
destroy the Excel application object, that you haven't included in your
post? I don't get the same result -- when I execute that code, no hidden
copy of Excel remains.

I'm curious -- why are you opening Excel at all? TransferSpreadsheet
doesn't require that the Excel be running, or that the worksheet you want to
import be open.
 
Try changing this line:

objXL.ActiveWorkbook.Close False


to this:

objXL.Workbooks(1).Close False


However, I do agree with Dirk that your original code shouldn't leave an
EXCEL application running.
 
Thanks!

I'm new to Access, and I wasn't aware you didn't have to open Excel in order
to copy over a spreadsheet.
 
Back
Top