Closing Excel Reference

[Joker via AccessMonster.com]

Hello,

I have a database that is opening excel and doing a few things with it. My
problem comes at the end it doesn't want to close the Excel process so if the
dB is run again it returns an error. My code is below. Thanks.



Option Compare Database
Option Explicit

Function ExcelFormat()
On Error GoTo ErrHnd_Err

'***************************************************************************
' Variables
'***************************************************************************
Dim intResponse As Integer
Dim strFolder As String
Dim strSQL As String
Dim strTblName As String
Dim dbs As Database
Dim rst As Recordset
Dim rst1 As Recordset
Dim appXL As Excel.Application
Dim wbkXL As Excel.Workbook
Dim shtXL As Excel.Worksheet
Dim i As Integer
Dim Z As Integer
Dim strFilter As String
Dim strInputFileName As String

DoCmd.SetWarnings False
DoCmd.SetWarnings False
Set dbs = CurrentDb


'***************************************************************************
' Select Cutoff File
'***************************************************************************

'This is where the dialog box opens to select the cutoff file.
strFilter = ahtAddFilterItem(strFilter, "Spreadsheets (.xls)", "*.xls")
strInputFileName = ahtCommonFileOpenSave(Filter:=strFilter, OpenFile:
=True, DialogTitle:="Select a Cutoff file for the invesor you are running",
Flags:=ahtOFN_HIDEREADONLY)

'***************************************************************************
' Excel Functionality for Import
'***************************************************************************
Set appXL = CreateObject("Excel.Application")
appXL.DisplayAlerts = False
'USE FOR DEBUGGING ONLY
'appXL.Visible = True
'Open File in Excel and delete uneeded stuff as well as replaces functions
with actual values
appXL.Workbooks.Open strInputFileName
Set wbkXL = appXL.ActiveWorkbook
Set shtXL = wbkXL.ActiveSheet
With shtXL
.Rows("1:5").Select
.Range("A5").Activate
.Rows("1").Delete
.Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
.Rows("6:6").Select
.Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
.Rows("1:4").Select
.Range("A4").Activate
Selection.Delete Shift:=xlUp


'***************************************************************************
' Saves Cutoff file to temp
'***************************************************************************
'Saves the new file to the temp folder to be imported the db then deleted
ChDir "C:\Temp"
ActiveWorkbook.SaveAs FileName:="C:\Temp\PTSExcelFile.xls", FileFormat:=
_
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:
=False _
, CreateBackup:=False
End With

DoCmd.SetWarnings False
'***************************************************************************
' Check to see if the table exists, if so delete it
'***************************************************************************
If IsTableQuery("", "tbl_CutoffImport") Then
DoCmd.DeleteObject acTable, "tbl_CutoffImport"
Else
GoTo SKIPALONG
End If

SKIPALONG:


DoCmd.SetWarnings True

'***************************************************************************
' Transfer Spreadsheet to dB
'***************************************************************************

'DoCmd.TransferSpreadsheet acImport, 8, "tbl_CutoffImport", strInputFileName,
True, ""

DoCmd.TransferSpreadsheet acImport, 8, "tbl_CutoffImport", "C:\Temp\
PTSExcelFile.xls", True, ""

GoTo Closing

ErrHnd_Err:
MsgBox ("Error Handler")
Resume Closing
Closing:

'***************************************************************************
' Close and exit Excel
'***************************************************************************
ActiveWorkbook.Close

Set dbs = Nothing
'wbkXL.Close
Set wbkXL = Nothing

Set shtXL = Nothing
appXL.Quit

Set appXL = Nothing
DoCmd.SetWarnings True
End Function

 

[Ken Snell \(MVP\)]

Often, the use of the ActiveSheet, ThisWorkbook, etc. names can cause
another EXCEL instance. You might try replacing these lines of code with
references to specific workbook and worksheet:

Set wbkXL = appXL.ActiveWorkbook
Set shtXL = wbkXL.ActiveSheet


Additionally, it's important that you close objects in reverse order of how
you create them and set to Nothing right after closing. Also, be sure to
close all "child" objects before you close a parent object -- for example,
if you reference a worksheet through a workbook object, be sure to set the
worksheet object to Nothing before you close the workbook object and then
set it to Nothing.

 

[Joker via AccessMonster.com]

Thanks for the response. I changed the closing to:

Set shtXL = Nothing
wbkXL.Close
Set wbkXL = Nothing
appXL.Quit
Set appXL = Nothing

Which is the reverse order of them opening.

I am having a problem with changing the following.
Set wbkXL = appXL.ActiveWorkbook
Set shtXL = wbkXL.ActiveSheet

I have set
Set shtXL = wbkXL.Sheets(1)

but wbkXL comes from a variable and I'm having issues to set it to that. Can
you provide an example or more thoughts. I have still having the Excel
instance problem. Thanks.

Often, the use of the ActiveSheet, ThisWorkbook, etc. names can cause
another EXCEL instance. You might try replacing these lines of code with
references to specific workbook and worksheet:

Set wbkXL = appXL.ActiveWorkbook
Set shtXL = wbkXL.ActiveSheet

Additionally, it's important that you close objects in reverse order of how
you create them and set to Nothing right after closing. Also, be sure to
close all "child" objects before you close a parent object -- for example,
if you reference a worksheet through a workbook object, be sure to set the
worksheet object to Nothing before you close the workbook object and then
set it to Nothing.

Hello,

[quoted text clipped - 132 lines]

DoCmd.SetWarnings True
End Function

 

[Ken Snell \(MVP\)]

Open the workbook this way (replaces the two code steps that you're now
using):

Set wbkXL = appXL.Workbooks.Open(strInputFileName)

--

Ken Snell
<MS ACCESS MVP>

Thanks for the response. I changed the closing to:

Set shtXL = Nothing
wbkXL.Close
Set wbkXL = Nothing
appXL.Quit
Set appXL = Nothing

Which is the reverse order of them opening.

I am having a problem with changing the following.
Set wbkXL = appXL.ActiveWorkbook
Set shtXL = wbkXL.ActiveSheet

I have set
Set shtXL = wbkXL.Sheets(1)

but wbkXL comes from a variable and I'm having issues to set it to that.
Can
you provide an example or more thoughts. I have still having the Excel
instance problem. Thanks.

Often, the use of the ActiveSheet, ThisWorkbook, etc. names can cause
another EXCEL instance. You might try replacing these lines of code with
references to specific workbook and worksheet:

Set wbkXL = appXL.ActiveWorkbook
Set shtXL = wbkXL.ActiveSheet

Additionally, it's important that you close objects in reverse order of
how
you create them and set to Nothing right after closing. Also, be sure to
close all "child" objects before you close a parent object -- for example,
if you reference a worksheet through a workbook object, be sure to set the
worksheet object to Nothing before you close the workbook object and then
set it to Nothing.

Hello,

[quoted text clipped - 132 lines]

DoCmd.SetWarnings True
End Function

 

[Joker via AccessMonster.com]

Thanks Ken for all of your help. Below is what I actually ended up doing and
it's working fine now. I think I was setting it too many times.

Set appXL = CreateObject("Excel.Application")
appXL.Visible = False
appXL.Workbooks.Open strInputFileName
'With shtXL
appXL.Sheets(1).Select
appXL.Sheets(1).Rows("7").Rows.Delete
appXL.Sheets(1).Rows("1:5").Rows.Delete
appXL.Sheets(1).Cells.Select
appXL.Selection.Copy
appXL.Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
appXL.DisplayAlerts = False
appXL.Workbooks(1).SaveAs ("C:\Temp\PTSExcelFile.xls")
appXL.DisplayAlerts = True
appXL.Range("A1:A2").Select
appXL.Quit
Set appXL = Nothing

Open the workbook this way (replaces the two code steps that you're now
using):

Set wbkXL = appXL.Workbooks.Open(strInputFileName)

Thanks for the response. I changed the closing to:

[quoted text clipped - 37 lines]