Opening and Closing Excel from Access in VBA

Laurence Martin
Laurence Martin used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016
Commented:
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
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010
Commented:
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.
Most Valuable Expert 2011
Top Expert 2011
Commented:
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
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
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
Laurence MartinTraining Director

Author

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
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.
Most Valuable Expert 2011
Top Expert 2011

Commented:
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?
Laurence MartinTraining Director

Author

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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial