Solved

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

Posted on 2016-08-17
10
35 Views
Last Modified: 2016-08-17
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

0
Comment
Question by:fordraiders
  • 4
  • 2
  • 2
  • +2
10 Comments
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 41760087
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
 
LVL 34

Expert Comment

by:PatHartman
ID: 41760092
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
 
LVL 3

Author Comment

by:fordraiders
ID: 41760094
that is all the code i have to open the excel file.
0
 
LVL 34

Accepted Solution

by:
PatHartman earned 250 total points
ID: 41760103
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
 
LVL 3

Author Comment

by:fordraiders
ID: 41760118
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
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.

 
LVL 33

Assisted Solution

by:Norie
Norie earned 125 total points
ID: 41760137
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
 
LVL 26

Assisted Solution

by:Nick67
Nick67 earned 125 total points
ID: 41760157
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
 
LVL 3

Author Comment

by:fordraiders
ID: 41760161
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
 
LVL 3

Author Closing Comment

by:fordraiders
ID: 41760162
thanks to all
0
 
LVL 26

Expert Comment

by:Nick67
ID: 41760165
Note that without
xlApp.UserControl = True
the user can SEE the sheet, book and Excel, but can't DO anything with them!
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

743 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

10 Experts available now in Live!

Get 1:1 Help Now