cannot close/quit Excel

  • Thread starter Thread starter Keith G Hicks
  • Start date Start date
K

Keith G Hicks

I'm using vba automation to write to an Excel file. Everything works fine
except that I can't seem to quit a temporary instance of Excel that I'm
opening. Here's some of my code (also read the rest of my question after the
code):

Private Function fncBulkCellCopy(sTableToTransfer As String, sRangeToCopy As
String, sStartPasteCell As String) As Boolean
On Error GoTo Err_fncBulkCellCopy

Dim objExcelAppForTransfer As New Excel.Application
Dim objExcelBkForTransfer As New Excel.Workbook
Dim objExcelShtForTransfer As New Excel.Worksheet

'Don't use the "subOpenExcel" routine above because that is for the
template files only. This is for the one local to this function.
Set objExcelAppForTransfer = CreateObject("Excel.Application")
objExcelAppForTransfer.Visible = bDev
If bDev Then
objExcelAppForTransfer.WindowState = wdWindowStateMaximize
End If

fncBulkCellCopy = False

'Do a grungy but VERY FAST export to Excel
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
sTableToTransfer, sExcelTemplatesPath &
"TempExcelFileForLittleCaesarsOutputs.xls"

'Open the grungy excel file and copy all but the first row of data
objExcelAppForTransfer.Workbooks.Open sExcelTemplatesPath &
"TempExcelFileForLittleCaesarsOutputs.xls"
Set objExcelBkForTransfer = objExcelAppForTransfer.ActiveWorkbook
Set objExcelShtForTransfer = objExcelBkForTransfer.Worksheets(1)

objExcelShtForTransfer.Range(sRangeToCopy).Select 'skip the header row
objExcelAppForTransfer.Selection.Copy

'Paste the cells from the grungy one into the template for this report
Set objExcelBk = objExcelApp.ActiveWorkbook
Set objExcelSht = objExcelBk.Worksheets(1)

objExcelSht.Range(sStartPasteCell).Select
objExcelSht.Paste

'Close the grungy file and delete it
objExcelAppForTransfer.DisplayAlerts = False

objExcelAppForTransfer.Workbooks("TempExcelFileForLittleCaesarsOutputs.xls")
..Close SaveChanges:=False
objExcelAppForTransfer.ActiveWindow.Close SaveChanges:=False
objExcelAppForTransfer.DisplayAlerts = True
objExcelAppForTransfer.Quit
Kill sExcelTemplatesPath & "TempExcelFileForLittleCaesarsOutputs.xls"

fncBulkCellCopy = True

Exit_fncBulkCellCopy:
Set objExcelShtForTransfer = Nothing
Set objExcelBkForTransfer = Nothing
Set objExcelAppForTransfer = Nothing
Exit Function

Err_fncBulkCellCopy:
MsgBox Err.Description & ", # " & Str(Err.Number) & "
modExcelAutomation.fncBulkCellCopy"
Resume Exit_fncBulkCellCopy

End Function


The variables that end in "ForTransfer" are local to the above routine.
objExcelAppForTransfer is the instance of Excel that I want to completely
quit out of so that it's no longer running at all. objExcelSht is an
instance of Excel taht's opened by the calling routine. All that works
fine. i don't want to make any changes to that. The routine above is simply
used to do the TransferSpreadsheet to a temporary excel file and then I do a
copy from that to the final excel file. The temporary workbook closes just
fine but when everything is done running I end up with an empty instance of
Excel running from objExcelAppForTransfer. No errors in the code above. The
copy/paste all works just fine. It all runs fine. Just doesn't quit out of
Excel. The global variable bDev is set to True.

Any ideas?

Thanks,

Keith
 
Never mind. I didn't realize there was an error occurring on
objExcelAppForTransfer.ActiveWindow.Close SaveChanges:=False.
I threw that line in later on before I added the line
objExcelAppForTransfer.Quit and since the workbook was being closed before
that, it was crashing. When I took that out, the line
objExcelAppForTransfer.Quit did its job. All set. Thanks anyway.
 
Back
Top