Quitting excel using access

[bushman69]

Hi

I am trying to find out how to quit excel after importing 2 worksheets from an excel file. I have done a lot of searching on the net, but I cannot seem to solve this problem. I would really appreciate some help.

I have isolated it down to the DoCmd.TransferSpreadsheet command. If I comment it out then excel loads, does its thing and then quits. With the docmd statement included Excel will not quit. The code is:

Dim appExcel As Object
Dim wb As Object
Dim sh As Object
Dim objRange As Object
Dim strValue As String
Dim intRow As Integer
Dim intCol As Integer
Dim rs As Recordset
Dim intNoOfSheets As Integer, intCounter As Integer, intLastDataColumn As Integer
Dim strFilePath As String, strLastDataColumn As String
Dim strLastDataRow As String, strLastDataCell As String
Dim tbl As TableDef
Dim tdef As TableDef
Dim boolXl As Boolean
Set rcst = Nothing
Set rst = Nothing
strTextFile = GetOpenFile("Excel", ExportImportPath, "Import Text File")
If strTextFile = "" Then
MsgBox "No import file selected. Import cancelled."
Exit Function
End If
Set dbs = CurrentDb
If fIsAppRunning("Excel") Then
Set appExcel = GetObject(, "Excel.Application")
boolXl = False
Else
Set appExcel = CreateObject("Excel.Application")
boolXl = True
End If
Set wb = appExcel.workbooks.Open(strTextFile)
intNoOfSheets = wb.Worksheets.Count
i = 1
With appExcel
For Each sh In wb.Sheets
If sh.Name = "Allocation Details" Or sh.Name = "Teachers" Then
strLastDataColumn = Chr(.selection.SpecialCells(11).Column + 64)
strLastDataRow = wb.Worksheets(sh.Name).Cells(.Rows.Count, 1).End(-4162).Row
strLastDataCell = strLastDataColumn & strLastDataRow 'e.g. J123
DoCmd.TransferSpreadsheet acImport, , _
"TblTmp" & sh.Name, strTextFile, True, wb.Worksheets(i).Name & "!A1:" & strLastDataCell
wb.Close SaveChanges:=False
End If
If i = intNoOfSheets Then Exit For
i = i + 1
Next
End With
Set rcst = Nothing
Set rst = Nothing
If boolXl = True Then appExcel.Application.Quit
Set appExcel = Nothing
ImportTimetableExcel = True
Exit Function
ErrorHandlerExit:
Set rcst = Nothing
Set rst = Nothing
If boolXl = True Then appExcel.Application.Quit
Exit Function

ErrorHandler:
MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
Resume ErrorHandlerExit
ImportTimetableExcel = False
End Function

Any help is greatly appreciated as I just cannot solve this by myself.

 

[Remou]

This line:

If boolXl = True Then appExcel.Application.Quit

Says to exit Excel if it was not already loaded. In what way is it not working?

 

[bushman69]

Hi Remou

The if statment works fine, but excel does not quit when I am using the transfer spreadsheet docmd.

If I comment out the docmd.transferspreadsheet line of code then it all works fine and excel quits. As soon as I reinstate the docmd statement then excel does not quit. I just cannot work out what it is in the docmd.transfer spreadsheet line that would stop excel from quitting.

 

[PHV]

I'd put the following line:
wb.Close SaveChanges:=False
after the Next instruction.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[bushman69]

Hi PH

Thanks for the tip, I tried it but it didn't help. It does, make for better code so I appreciate it. I just cannot get excel to quit. I am sure that it is tied up with the transferspreadsheet statement, but I just do not know where in that statement the problem is.