Closing Excel Application

  • Thread starter Thread starter Dave Bradshaw
  • Start date Start date
D

Dave Bradshaw

I am using VBA to export a report from Access into Excel, reformatting
it so it looks something like the original report, and leaving the
Excel file open so that the user can email it.

My code goes something like:

DoCmd.OutputTo acOutputReport, strReport, acFormatXLS, _
stFullPath, False

Set myXLS = New Excel.Application

Set myWorkbook = myXLS.Workbooks.Open(strExcelFile)

- code to reformat the workbook (size columns and rows, add titles
etc) -

strFileName = stPath & "\Production Schedule " _
& DatePart("yyyy", Date) & "-" _
& DatePart("m", Date) & "-" & DatePart("d", Date)
myWorkbook.SaveAs (strFileName)
myXLS.Visible = True
Set myXLS = Nothing

This all works as required. But when the user closes Excel, the window
closes, but Excel is still listed as a running process.
 
DoCmd.OutputTo acOutputReport, strReport, acFormatXLS, _
stFullPath, False

Set myXLS = New Excel.Application

Set myWorkbook = myXLS.Workbooks.Open(strExcelFile)

- code to reformat the workbook (size columns and rows, add titles
etc) -

strFileName = stPath & "\Production Schedule " _
& DatePart("yyyy", Date) & "-" _
& DatePart("m", Date) & "-" & DatePart("d", Date)
myWorkbook.SaveAs (strFileName)
MyWorkbook.Close
MyXLS.Quit
Set MyWorkbook = Nothing
Set myXLS = Nothing
Shell("excel " & Chr$(34) & strFileName & Chr$(34), vbMaximizedFocus)
 
Dave Bradshaw wrote in message
I am using VBA to export a report from Access into Excel, reformatting
it so it looks something like the original report, and leaving the
Excel file open so that the user can email it.

My code goes something like:

DoCmd.OutputTo acOutputReport, strReport, acFormatXLS, _
stFullPath, False

Set myXLS = New Excel.Application

Set myWorkbook = myXLS.Workbooks.Open(strExcelFile)

- code to reformat the workbook (size columns and rows, add titles
etc) -

strFileName = stPath & "\Production Schedule " _
& DatePart("yyyy", Date) & "-" _
& DatePart("m", Date) & "-" & DatePart("d", Date)
myWorkbook.SaveAs (strFileName)
myXLS.Visible = True
Set myXLS = Nothing

This all works as required. But when the user closes Excel, the window
closes, but Excel is still listed as a running process.
Click to expand...


If you don't release the workbook object prior to altering the
visible property of the excel object, and releasing that object,
I'd advise to do so. Perhaps a DoEvents too.

But what I suspect, is that somewhere along the code you didn't
show us, there are unqualified references to excel object,
properties or methods that causes what you're experiencing now.

Here's one reference
http://support.microsoft.com/default.aspx?kbid=178510

I must confess I've become a fan of instantiating excel
through createobject (as in the referenced article) in stead of
using the New keyword.
 
Thank you

I tried using getobject (since Excel was already initiated by the
DoCmd line), and all of a sudden I had a number of compile errors
where I had used the Cells collection as an unqualified reference.
Using New Excel.Application hid this problem. So now I am using
getobject and qualified my references, and everything is sweetness and
light.

The problem arose because the code was originally written in an Excel
workbook and set it to autorun when the workbook was opened, and I
would get it to run from Access by opening this workbook after the
Production Schedule workbook had opened. This seemed a little
inelegant so I copied the code into Access VBA, and modified it,
changing references where necesary. I missed referencing Cells because
it was hidden in Workbook.Worksheet.Range(Cells(), Cells()) .
 
Back
Top