Solved

Opening and Closing Excel from Access in VBA

Posted on 2014-03-05
8
32,063 Views
1 Endorsement
Last Modified: 2014-03-12
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
Comment
Question by:LJKMartin
8 Comments
 
LVL 48

Assisted Solution

by:Rgonzo1971
Rgonzo1971 earned 20 total points
ID: 39906066
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
 
LVL 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 40 total points
ID: 39906088
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
 
LVL 85

Assisted Solution

by:Rory Archibald
Rory Archibald earned 40 total points
ID: 39906211
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
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 350 total points
ID: 39906583
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
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:LJKMartin
ID: 39907973
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
 
LVL 35

Assisted Solution

by:[ fanpages ]
[ fanpages ] earned 50 total points
ID: 39908488
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
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 39908490
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
 

Author Closing Comment

by:LJKMartin
ID: 39922792
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now