Excel not closing when opened from Access (again...)

[Jeff Hunt]

I've asked this question before and got it resolved, but now it's back on
another form and I can't seem to get rid of it. I am opening two Excel
workbooks, moving data from one to the other, doing a SaveCopyAs, then
closing both of the original files. When it's all done, Excel is still
listed in my TaskMan. The code calling Excel is as follows:

Dim xlApp As Excel.Application
Dim xlWb1 As Excel.Workbook
Dim xlSht1 As Excel.Worksheet
Dim xlWb2 As Excel.Workbook
Dim xlSht2 As Excel.Worksheet


strTempPath = "\\ccnp-fileprint\data\Investments\Public Corp Actions\Journal
Entries\"
strTempName = "JournalEntryTemp.xls"
strTempFileName = strTempPath & strTempName

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"tblJournalEntryExportTemp", strTempFileName

strTemplatePath = "\\ccnp-fileprint\data\Investments\Public Corp
Actions\Journal Entries\"
strTemplateName = "JETemplateFAF.xls"
strTemplateFileName = strTemplatePath & strTemplateName

strNewTemplateName = "JETemplateFAF_" & Format(Date, "yyyymmdd") & ".xls"
strNewTemplateFileName = strTemplatePath & strNewTemplateName


Set xlApp = CreateObject("Excel.Application") '''create an instance of Excel
Set xlWb1 = xlApp.Workbooks.Open(strTempFileName) '''Open the Excel file of
export
Set xlSht1 = xlWb1.Worksheets(1) '''select the first
worksheet as active
Set xlWb2 = xlApp.Workbooks.Open(strTemplateFileName, , True) '''Open the
Excel file of JE Template
Set xlSht2 = xlWb2.Worksheets(1) '''select the first
worksheet as active

xlApp.DisplayAlerts = False
'xlApp.Visible = True

xlSht2.Activate
xlSht2.Range("A8", "K5000").ClearContents

xlSht1.Activate
xlSht1.Range("A2", Cells(Rows.Count, 1).End(xlUp)).Resize(, 11).Select
xlApp.Selection.Copy

xlSht2.Activate
xlSht2.Range("A8").Select
xlApp.Selection.PasteSpecial Paste:=xlPasteValues
xlWb2.SaveCopyAs strNewTemplateFileName

MsgBox "A new Journal Entry Template file has been created at: " & vbCrLf &
strNewTemplateFileName, _
vbInformation, "Export OK!"

ExitSub:
xlApp.DisplayAlerts = True
xlWb1.Close False
xlWb2.Close False
xlApp.Quit

Set xlSht1 = Nothing
Set xlWb1 = Nothing
Set xlSht2 = Nothing
Set xlWb2 = Nothing
Set xlApp = Nothing

There is more code to this Sub, but it doesn't touch excel except right here
at the end. I guess there is also transferSpreadsheet just before this
section (DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"tblJournalEntryExportTemp", strTempFileName) but I don't think it actually
calls an instance of Excel.

Anyone see what I am doing wrong? I even tried having two separate
Excel.Application instances, one for each file, and one closed but not the
other.

Thanks.

 

[Ken Snell \(MVP\)]

This line of code has an unqualified reference to Cells collection and to
Rows:

xlSht1.Range("A2", Cells(Rows.Count, 1).End(xlUp)).Resize(, 11).Select


It should be this:

xlSht1.Range("A2", xlSht1.Cells(xlSht1.Rows.Count, 1).End(xlUp)).Resize(,
11).Select

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

 

[Jeff Hunt]

Yep, that fixed it! It's obvious now that you point it out. I had just
found that line of code (for selecting all cells that have data) in the
forums the other day and it worked way better than my previous workaround,
but apparently was also causing some problems. Thanks a ton!
--
....jeff...
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

This line of code has an unqualified reference to Cells collection and to
Rows:

xlSht1.Range("A2", Cells(Rows.Count, 1).End(xlUp)).Resize(, 11).Select


It should be this:

xlSht1.Range("A2", xlSht1.Cells(xlSht1.Rows.Count, 1).End(xlUp)).Resize(,
11).Select

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/