[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 45059
  • Last Modified:

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
1
LJKMartin
Asked:
LJKMartin
5 Solutions
 
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 FyeCommented:
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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
 
LJKMartinAuthor 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
 
LJKMartinAuthor 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

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now