Solved

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

Posted on 2016-08-17
10
44 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
[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
  • 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 36

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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 36

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

726 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