Excel File Not Closing After Import - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Excel File Not Closing After Import

Howard Chapell

7 months ago

Hi I have a routine to import Multiple Worksheets from a Workbook. It generally works the First Time but if I retry for whatever reason it hangs. I go into Taskmanager and find Excel is still loaded. The following is my Code>

Details

Public Sub CmdImportAllWorkSheets_Click()

Dim fdialog As FileDialog
Dim varFile As Variant
Dim objXL As New Excel.Application
Dim LastRow As Long
Dim LastCol As Long
Dim wkb As Excel.Workbook
Dim wks As Excel.Worksheet
Dim tblName As String
Dim RecordCount As Long
Dim RecStr As String
Dim LastRowStr As String
Dim RecordCountStr As String


    On Error GoTo Cleanup
    ' On Error Resume Next
    
'set up the file Dialog.
Set fdialog = Application.FileDialog(msoFileDialogFilePicker)
With fdialog
    .AllowMultiSelect = False
    .Filters.Add "Excel Files", "*.xls;*.xlsx;*.xlsm"
    If .Show = True Then
        For Each varFile In .SelectedItems
           Set wkb = objXL.Workbooks.Open(varFile)
           tblName = "tblWorkList5"
        For Each wks In wkb.Worksheets
        LastRow = wks.Cells(wks.Rows.Count, 1).End(-4162).Row
        RecordCount = LastRow - 1
        
        LastRowStr = CStr(LastRow)
        RecordCountStr = CStr(RecordCount)
        
        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, tblName, varFile, True, wks.Name & "$"
                 
        Status RecordCountStr
        Status "No Of Records"
        Status LastRowStr
        Status "Last Row"
        Status wks.Name
        Status "WorkSheet Name"
        Status "Commencing Import"
        Sleep 2000
        Next wks
         wkb.Close False
        Next varFile
     End If
End With


Cleanup:
   ' wkb.Close False
   Set wks = Nothing
   Set wkb = Nothing
   objXL.Quit
   Set objXL = Nothing
   '  If Not objXL Is Nothing Then objXL.Quit
   '  Set objXL = Nothing

   End Sub

Richard Rost @Reply

7 months ago

Two quick fixes. Best: don't start Excel at all - TransferSpreadsheet reads the workbook directly, so loop the sheet names and import each one, no Excel process to hang. If you keep automation, don't use "As New" - use Set ObjXL = CreateObject("Excel.Application"), open ReadOnly, and in your Cleanup always wkb.Close False, Set wks = Nothing, Set wkb = Nothing, ObjXL.Quit, Set ObjXL = Nothing - Excel sticks around if any ref is still alive.

	Howard Chapell OP @Reply 7 months ago
Thanks for your response Richard much appreciated

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View