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
Solved

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

Posted on 2016-08-17
10
43 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 35

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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 35

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

856 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