Unable to get excel to close after import

[Batty]

Hi Guys,

I created a module to import all of the worksheets from a static list of excel files.

However when the program finnishes excel is still running as a process.

my code is

Sub ImportExcelData()



Dim strFile As String
Dim strFilter As String
Dim objXL As Object
Dim objXLWB As Excel.Workbook
Dim objXLWS As Excel.Worksheet
Dim objXLRNG As Excel.Range
Dim MySheet As String
Dim strRange As String

Set objXL = CreateObject("Excel.Application")

Set rstimportdata = CurrentDb.OpenRecordset("Import Files", dbOpenTable)

Do

    strFile = rstimportdata("Path")
    
    If strFile = "" Or Null Then
    
        rstimportdata.MoveNext
        
      Else
        
        Set objXLWB = objXL.Workbooks.Open(strFile)
          
        For Each objXLWS In objXLWB.Worksheets
         
          If objXLWS.Name <> "Cover Sheet" Then
            
            Set objXLRNG = objXLWS.UsedRange
         
            strRange = objXLWS.Name & "!" & objXLRNG.Address(False, False, xlA1, False)
         
            DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "MyOne", strFile, True, strRange, True
            
            
          End If
         
        Next
        
        Set objXLWB = Nothing
        Set objXLWS = Nothing
        
       
       rstimportdata.MoveNext

    End If
    
    

Loop Until rstimportdata.EOF

objXL.Quit
Set objXLWB = Nothing
Set objXLWS = Nothing
Set objXLRNG = Nothing
Set objXL = Nothing


End Sub

If i remove the line

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "MyOne", strFile, True, strRange, True

Excel will close, but if its in there it wont.

Any help would be appreiciated.

Batty

 

[Sergeant]

Not entirely sure, but could this line be responsible:
Dim objXL As Object

Perhaps...
Dim objXL as Excel.Application
or...
Dim objXL as New Excel.Application

 

[modest]

Dim objXL As Object

Perhaps...
Dim objXL as Excel.Application
or...
Dim objXL as New Excel.Application

objXL is an object in Access; the CreateObject() reinforces/confirms this

I think the problem is that you're opening it, but never close it.
Set objXLWB = objXL.Workbooks.Open(strFile)

I would put
objXL.Workbooks.Close after the "Next" or after the "Loop" line.
I'm not sure which because I didn't really look at the logic behind your code. (Trial and error may help)

 

[Batty]

Guys,

Thanks for the ideas but niether of them worked.

i know its the line DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "MyOne", strFile, True, strRange, True

Do you guys know any other ways of Copying a spreadsheet appart from DoCmd.TransferSpreadsheet ?

or weather i need to do something to close the transfer?

Thanks

Batty

 

[modest]

strRange = objXLWS.Name & "!" & objXLRNG.Address(False, False, xlA1, False)

I haven't worked in Access in some time so I dont remember what the "!" does. It's used as the bang operator in access.

I do know that strRange should normally be in this format "A1:C3" (notice the semicolon). It may be that your range is not defined correctly and the workbook is still "importing"