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
LJKMartinTraining DirectorAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rgonzo1971Commented:
Hi,

pls try EDITED

Dim xlApp As Object
On Error Resume Next
Set xlAppp = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
    'Excel wasn't running, start it from code
    Set xlApp = CreateObject("Excel.Application")
End If
On Error GoTo 0

xlAppp .Workbooks.Open(FileName)

Open in new window

Regards
0
Dale FyeOwner, Developing Solutions LLCCommented:
The key to automating and working with Excel and getting the process to close when you are done is that you have to physically clear references to all excel objects created in your code.

Although you only refer to myXL above, I assume that you also are declaring workbook, worksheet, and possibly Range objects within your code.  You must implicitly clear those references when you are done in  order for Excel to close.

Set wbk = nothing
set sht = nothing
set rng = nothing

those are the variables I use.  You will have to check your code and look for any other Excel objects that you are creating.
0
Rory ArchibaldCommented:
Additionally, I suspect that in your Access code, you have referred to an Excel object without properly qualifying it back to the myXL object. This will create an implicit reference to Excel that you cannot destroy until Access is closed. Until you post the code, this is all speculation though... ;)

Regards,
Rory
0
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Gustav BrockCIOCommented:
Here's the order to open and close Excel:

    Dim xls     As Excel.Application
    Dim wkb     As Excel.Workbook
    Dim wks     As Excel.Worksheet
   
    Set xls = New Excel.Application
    Set wkb = xls.Workbooks.Open("c:\test\workbook1.xlsx")
    Set wks = wkb.Worksheets(1)
   
    wks.Name = "My New Name"
    wkb.Close True
   
    Set wks = Nothing
    Set wkb = Nothing
   
    xls.Quit
   
    Set xls = Nothing

You _MUST_ be very specific to this. If you set your xlApp to Nothing, its feet are kicked away.

/gustav
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
LJKMartinTraining DirectorAuthor Commented:
Rory, your speculation is correct.

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
0
[ fanpages ]IT Services ConsultantCommented:
Hi Laurence,

Gustav added the ".Quit" Method of the Excel.Application object that was absent from your own code.  Perhaps this is all that is required to assist you.

Rgonzo1971's code does indeed check for an existing EXCEL.exe session (instance) & uses that in preference to creating another, but would you wish to close an existing session (if one exists) once the export is completed?  What if you have pending changes in an open workbook that need to be saved?

Additionally, any existing EXCEL.exe session may have a dialog box open, or be in a hidden (invisible) state, so it may not be possible, or practical to use an existing session for an export routine.  Using a dedicated EXCEL.exe application object is safer, as long as you implement some error handling in your code in case system resources are too low to support an additional instance at that time.

Finally, setting all objects created to 'Nothing' is tidy, & was considered standard practice many years ago.  Since Visual Basic for Applications has developed from the early releases (of Access Basic), when an 'End Sub' or 'End Function' is encountered, object resources should now be released back to the operating system, so explicit use of Set ObjectName = Nothing is no longer required.

That said, sometimes the automatic release of objects is not performed immediately, or at all (!), so continuing to reset objects in this manner is still recommended (although probably not necessary for a high majority of cases).

BFN,

fp.
0
Rory ArchibaldCommented:
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?
0
LJKMartinTraining DirectorAuthor Commented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.