excel staying in memory when trying to run code a second time

Access 2010 vba
from a command button:

Problem: The Excel file is opening ok.
But Excel is staying in memory.

         Dim strFile As String
          Dim strDirectory As String
          Dim CurrentWorkbookName As String
           Dim strFileopen As String
              strDirectory = "C:\Program Files\Enterprise\iuom\" 
  

          strFile = "VersioniUom.xls"


            strFileopen = strDirectory & strFile
           Workbooks.Open strFileopen  'strDirectory & strFile




Workbooks.Application.Visible = True

Open in new window

LVL 3
FordraidersAsked:
Who is Participating?
 
PatHartmanConnect With a Mentor Commented:
There has to be code elsewhere that dims the Workbook object and opens Excel.  Here's a snippet from one of my apps that automates Excel.
    Set appExcel = New Excel.Application
    Set wbkNew = appExcel.Workbooks.Open(TargetFile)
    Set wksNew = appExcel.Worksheets("QueuePerformance")
    With appExcel
        .Range("C3").Value = Me.cboProductionID
        .Range("B2").Value = Date
        .Range("C2").Value = Format(Now(), "hh:mm")
    End With
    wbkNew.Save
    appExcel.Visible = True

Open in new window

 Notice it sets appExcel to a "new" Excel object
0
 
Kelvin SparksCommented:
When you have finished running the code, you need to close the workbook, and then close the instance of Excel.

AS you haven't shown all the code, I don't known what you've defined Excel as, but it would be something like xls.Quit


Kelvin
0
 
PatHartmanCommented:
You don't have the whole procedure posted so I don't know if you are using an existing instance of Excel or opening a new one.  I always open a new one because I do not want to interfere with any work the user is doing outside of Access and if I attempt to use an existing instance of Excel, I run the risk of causing his work in process to be lost.

When you are done importing/exporting a workbook, close the workbook and set the object to nothing.  If this doesn't make Access let go of the workbook, you will also need to close the instance of Excel that you opened and then open a new one each time you open a new workbook.  Even though you might have multiple instances of Excel open, you might run into a problem running code because Excel only seems to allow one instance of the Excel VBE to be open at one time even though there are multiple workbooks and even multiple instances of Excel.
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
FordraidersAuthor Commented:
that is all the code i have to open the excel file.
0
 
FordraidersAuthor Commented:
ok, I have this running ok below:

But as usual, another problem creeps up.

After i open this particular workbook. and then CLOSE IT.
ALL MY EXCEL FILES i have open are closed ?




' Note Must have object References set
Dim strFile As String
Dim strDirectory As String
Dim strFileopen As String
Dim xlApp As Excel.Application
Dim ExcelRunning As Boolean
            
            strDirectory = "C:\Program Files\Enterprise\iuom\" 'List directory with some excel files, slash required
            strFile = "Version_Enterprise.xls"
            strFileopen = strDirectory & strFile
ExcelRunning = IsExcelRunning()
If ExcelRunning Then
Set xlApp = GetObject(, "Excel.Application")
Else
Set xlApp = CreateObject("Excel.Application")
End If
xlApp.workbooks.Open strFileopen, True, False
xlApp.Visible = True
If Not ExcelRunning Then xlApp.Quit
Set xlApp = Nothing
End Sub
Function IsExcelRunning() As Boolean
Dim xlApp As Excel.Application
On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")
IsExcelRunning = (Err.Number = 0)
Set xlApp = Nothing
Err.Clear
End Function

Open in new window

0
 
NorieConnect With a Mentor VBA ExpertCommented:
Is all this code in Access?

If it is then things like this are going to cause problems.
Workbooks.Open strFileopen

Open in new window

Workbooks doesn't really mean anything to Access VBA but if you are using early binding it might try and 'resolve' it in some way.

That way might be to create a new instance of Excel.

So you might end up with multiple instances of Excel floating about, like 'ghosts'.
0
 
Nick67Connect With a Mentor Commented:
After i open this particular workbook. and then CLOSE IT.
 ALL MY EXCEL FILES i have open are closed ?


This is what @PatHartman was talking about in firing up an Excel instance of your own to work with.
You are not doing that
Here, you are looking for existing Excel instances
Function IsExcelRunning() As Boolean
Dim xlApp As Excel.Application
On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")
IsExcelRunning = (Err.Number = 0)
Set xlApp = Nothing
Err.Clear
End Function

Open in new window


and here you are using the existing instance if you find one
ExcelRunning = IsExcelRunning()
If ExcelRunning Then
Set xlApp = GetObject(, "Excel.Application")
Else
Set xlApp = CreateObject("Excel.Application")

Open in new window


So this is expected behavior <grin> but you didn't expect it.
You closed a pre-existing Excel app--of course all the files opened in it are getting closed.

Fire up your own Excel instance  by dispensing with this check.
Just use
Set xlApp = CreateObject("Excel.Application")
This is all that you need.

If you make it visible
Workbooks.Application.Visible = True 'not that this is good code to use this way in Access!
You should either turn it over to UserControl = True or close the open books and quit.

oSheet.SaveAs (myfilename)
oBook.Close
xlApp.UserControl = True
xlApp.Quit

Open in new window


You are very, very likely to run into grief with
Workbooks.Application.Visible = True
Sometimes you can get away with sloppy syntax, but you usually get a bite in the arse.
You need an Excel app
Set xlApp = CreateObject("Excel.Application")
Then you need a workbook object
Dim oBook as object
Set oBook = oApp.Workbooks.Open(strFileopen, , True)

Then you need a sheet object
Dim oSheet as Object
Set oSheet = oBook.Worksheets("SomeSheetName")

Although sometimes I have difficulty using the name and have to code a loop to match the ordinal to the name and use the ordinal

Once you are on the sheet, go crazy
oSheet.Cells(1, 1).Value = "Client Name"
oSheet.Cells(1, 2).Value = "Nick67"


But if you try to skip this creation of objects and use copy-and-paste code straight from Excel, it tends to foul out quickly and in unexepected places
Method Cells of ... is invalid is one that you run into when you've been sloppy.
0
 
FordraidersAuthor Commented:
Thanks,
adjusted the code to this:
' Note Must have object References set
Dim strFile As String
Dim strDirectory As String
Dim strFileopen As String
Dim xlApp As Excel.Application

     Set xlApp = CreateObject("Excel.Application")
       strDirectory = "C:\Program Files\Ent\iuom\" 'List directory with some excel files, slash required
       strFile = "Version_Enterprise.xls"
       strFileopen = strDirectory & strFile
xlApp.Visible = True
xlApp.workbooks.Open strFileopen, True, False
' clear memory
Set xlApp = Nothing
0
 
FordraidersAuthor Commented:
thanks to all
0
 
Nick67Commented:
Note that without
xlApp.UserControl = True
the user can SEE the sheet, book and Excel, but can't DO anything with them!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.