asked on

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

Kelvin Sparks

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

PatHartman

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.

Fordraiders

ASKER

that is all the code i have to open the excel file.

ASKER CERTIFIED SOLUTION

PatHartman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Fordraiders

ASKER

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

SOLUTION

Norie

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Nick67

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Fordraiders

ASKER

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

Fordraiders

ASKER

thanks to all

Nick67

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