Laurence Martin
asked on
Opening and Closing Excel from Access in VBA
Hi,
What's the best way to open Excel from an Access module and then close it?
My code exports some data to Excel and at the end I display the resulting workbook.
The problem is that the creates a new instance of Excel but I don't know how to close it.
The code:
Dim myXL As excel.Application
Set myXL = New excel.Application
myXL.Workbooks.Open stOrderCard
'it then populates the workbook
myXL.Visible= True 'at the end
Set myXL = Nothing
Exit Sub
The result in Excel is fine, but when the user closes Excel the instance remains as a process.
Can you help me with:
A better way to start Excel, especially if it's running
and
How to give the user the control of closing the instance/process?
Thank-you
Laurence
What's the best way to open Excel from an Access module and then close it?
My code exports some data to Excel and at the end I display the resulting workbook.
The problem is that the creates a new instance of Excel but I don't know how to close it.
The code:
Dim myXL As excel.Application
Set myXL = New excel.Application
myXL.Workbooks.Open stOrderCard
'it then populates the workbook
myXL.Visible= True 'at the end
Set myXL = Nothing
Exit Sub
The result in Excel is fine, but when the user closes Excel the instance remains as a process.
Can you help me with:
A better way to start Excel, especially if it's running
and
How to give the user the control of closing the instance/process?
Thank-you
Laurence
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You don't need to but, if you're going to leave it open for the user anyway, why wouldn't you use an existing instance of Excel rather than creating a new one with all its overhead?
ASKER
Thanks Everyone,
I have added references to the worksheet and range object and close them down.
I don't quit at the end because I want the user to see the result of the export.
It works - the instance closes once the user has closed the workbook.
I have added references to the worksheet and range object and close them down.
I don't quit at the end because I want the user to see the result of the export.
It works - the instance closes once the user has closed the workbook.
ASKER
Am I right in thinking that if I take the approach Gustav is suggesting that will create all the references I need, and close them down in a controlled way, so that I don't need to use Rgonzo's codes to see if excel is already open.
Thanks