Solved

Opening and Closing Excel from Access in VBA

Posted on 2014-03-05
8
38,264 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 51

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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 50

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
 

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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

738 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