How can we solve when Microsoft Excel 2007 stays in memory

Please post the VBA code.  It has likely either created an instance of Excel, or has kicked off a procedure to run at some point in the future (e.g., via OnTime or other related API).

Post the whole workbook if you can - you can just sanitize the data or even delete all the worksheets but one and delete the data in that, but I need to see your modules and how it all comes together.

Dave

Sub UpdateSelectedLinks()
Dim aLinks As Variant
Dim i As Integer
Application.ScreenUpdating = False
aLinks = ActiveWorkbook.LinkSources(xlExcelLinks)
On Error Resume Next
If Not IsEmpty(aLinks) Then
    For i = 1 To UBound(aLinks)
        ActiveWorkbook.UpdateLink Name:=aLinks(i), Type:=xlExcelLinks
    Next i
End If
On Error GoTo 0
Application.ScreenUpdating = True
End Sub

Sub RelinkWorkbooks()
Dim aLinks As Variant, vLink As Variant
Dim flPath As String
Dim wb As Workbook
aLinks = ThisWorkbook.LinkSources(xlExcelLinks)
If Not IsEmpty(aLinks) Then
    If IsArray(aLinks) Then
        For Each vLink In aLinks
            If Dir(vLink) = "" Then
                flPath = Application.GetSaveAsFilename(vLink)
                If flPath <> "False" Then
                    ThisWorkbook.ChangeLink vLink, flPath, xlExcelLinks
                End If
            End If
        Next
    Else
        If Dir(aLinks) = "" Then
            flPath = Application.GetSaveAsFilename(vLink)
            If flPath <> "False" Then
                ThisWorkbook.ChangeLink aLinks, flPath, xlExcelLinks
            End If
        End If
    End If
End If
End Sub

Sub ImportTransFromOutlook()
Dim olkApp As Object
Dim olkns As Object
Dim myfolder As Object
Dim mai As Object
Dim olMailItems As Object
Dim strFilter As String
Dim sh As Worksheet
Dim arr As Variant
Dim ln As Variant
Const olFolderInbox As Integer = 6
    
    Set sh = ThisWorkbook.Sheets("Ficohsa (HN LPS) a618 c5991")
    Set olkApp = CreateObject("Outlook.Application")
    Set olkns = olkApp.GetNamespace("MAPI")
    Set myfolder = olkns.GetDefaultFolder(olFolderInbox)
    strFilter = "[subject] = " & "VISA" 'here we set the condition that SUBJETC has to be VISA in order to proceed
    Set olMailItems = myfolder.items.Restrict(strFilter)
    With sh
        For Each mai In olMailItems
            With sh.Range("B" & sh.Range("B" & sh.Rows.Count).End(xlUp).Row).Offset(1, 0)
                .Value = Format(mai.receivedtime, "dd mmm yyyy") 'Transaction Date
                arr = Split(mai.body, vbCrLf) 'split body to be line by line using vbCrLf
                .Offset(0, 2).Value = arr(0)  'Transaction Main type CARD or ATH
                .Offset(0, 3).Value = arr(1)  'Type, ex: PROFF, MEAL, etc.
                .Offset(0, 4).Value = arr(2)  'Transaction description
               'Begin to read the rest of the body and find the value of the trans by looking for Currency "L."
                For Each ln In arr
                    located = InStr(ln, "L.")
                    If located <> 0 Then
                       Length = Len(ln)
                       lvalue = Mid(ln, located + 2, Length - (located + 2)) 'Set the value to export
                      'The next 2 if/then is if incase the value has more than one".", like the 1000 values usually does
                      'These If/Then will delete thos "." so it can export the correct value
                       If Right(lvalue, 1) = "." Then
                          lvalue = Left(lvalue, Len(lvalue) - 1)
                            If Right(lvalue, 1) = "." Then
                               lvalue = Left(lvalue, Len(lvalue) - 1)
                               End If
                          End If
                        .Offset(0, 6).Value = lvalue
                    End If
                Next
            End With
        Next
    End With
    
Set olMailItems = Nothing
Set olkns = Nothing
Set olkApp = Nothing
Set myfolder = Nothing
 
End Sub

Select allOpen in new window

Is this the only code you have in the Excel workbook?

Does Excel terminate properly if you DON'T run these macros?

Does it stay in memory if you DO run the ImportTransFromOutlook() macro?

Do you have this problem if you do the same thing on another machine?

Dave

Answers:

Is this the only code you have in the Excel workbook?
Yes

Does Excel terminate properly if you DON'T run these macros?
No

Does it stay in memory if you DO run the ImportTransFromOutlook() macro?
No (ok here)

Do you have this problem if you do the same thing on another machine?
No

This only happens with the Excel with VBA.  All other excel when exiting, also exits the memory as it should.

Nevertheless, let's try.  Where do I disable add-in.

Does this problem happen when you run another workbook that has VBA as well?  Any workbook?  If so, then these steps are very important as there's an Excel problem, not a VBA problem.

-------------- Steps:
1.  Uninstall add-ins:  http://office.microsoft.com/en-us/excel-help/load-or-unload-add-in-programs-HP010096834.aspx

2.  If that doesn't work, then do the Office Detect and repair: http://www.howtogeek.com/howto/microsoft-office/detect-and-repair-applications-in-microsoft-office-2007/

3.  If that doesn't work, I recommend uninstalling and re-installing office.  But, let's proceed one step at a time.

Dave

That is the only excel with VBA.  But to test it, we created a simple VBA in a Book and saved.  Enter and exit and ok; didnt stay in memory.

This is the VBA:

Sub Macro1()
'
' Macro1 Macro
'

'
    ActiveCell.FormulaR1C1 = "123"
    Range("I11").Select
    ActiveCell.FormulaR1C1 = "1231"
    Range("I12").Select
    ActiveCell.FormulaR1C1 = "2312"
    Range("I13").Select
    ActiveCell.FormulaR1C1 = "3123"
    Range("I14").Select
    ActiveCell.FormulaR1C1 = "123"
    Range("I15").Select
    ActiveCell.FormulaR1C1 = "12"
    Range("I16").Select
    ActiveCell.FormulaR1C1 = "31"
    Range("I17").Select
    ActiveCell.FormulaR1C1 = "23"
    Range("I18").Select
    ActiveCell.FormulaR1C1 = "123"
    Range("I19").Select
    ActiveCell.FormulaR1C1 = "123"
    Range("I20").Select
    ActiveCell.FormulaR1C1 = "1231"
    Range("I21").Select
    ActiveCell.FormulaR1C1 = "2"
    Range("I22").Select
End Sub

Select allOpen in new window

Also noticed that in the Excel with the problem when entering and quickly exiting, it always ask if we want  to save, like it does an update to the Excel.

Also noticed that with all Excel,current, new, VBA, no VBA, when entering Excel, it waits a bit until we can start pressing keys.

Strange

Keep following the steps I gave you.

Can you post your workbook?

Dave

Wasn't authorized to post the workbook.

just delete the data/sheets, add a blank sheet - need to see what you have in VBA and references, etc.

Dave

As for add-in, the links indicate to uninstall.  Can we disable them or should we uninstall them (see add-ins pic):


(I don't want damage anything)

You can just try disabling them for starters.

Dave

The curious thing is when preparing your sample excel, we first deleted all data and left the sheets, exited and it worked.  So can it be DATA sensitive? Something in the data, a calculation or format of a cell that can be causing this?

Anyways, here is the file the sheets deleted.
ExcelVBA.xlsm

A more curious thing.  If I open excel in blank and then open the ExcelVBA via "Recent Document", all is ok and it exit from memory.  But if we open the Excel by clicking it directly, it stays in memory.

Please advice.

But does it happens to you?

But did u test it in your PC?  

Does Excel stays in memory?

Did u open directly and from recent documents?

Sorry - I didn't see the file post.  Yes, I loaded it by using File->Open, then exited excel.  I also ran it by double clicking the file in my folder, then exited Excel.  Task manager all clear of Excel.

See my last post - check another profile and see if that works, otherwise continue down the path.

Dave

Sorry Ramante - I tested your file, but on re-reading your post, you asked some questions that I didn't answer.

Do you have any controls on your worksheets?  E.g., any buttons or dropdown combo boxes, listboxes, images?  They could be causing a corruption, or your workbook itself may be corrupt.

Let's work this path for a moment...

Dave

No, no buttons or drop-down, only the VBA I sent you.

I did test the file in another user within the same user and it didnt get stuck.  So it can be the user profile?

The problem with using recent files versus other methods resonates with a profile problem potentially

The fact you run fine with another user appears to confirm that

Creating a new profile for the problem user profile now appears to be in order

Save

Yeah but what does that mean? What are this steps to follow to change user-profile?  There has to be another way to go about without doing all that process of changing user-profile; is there?

What operating system version?

For Vista:

http://windows.microsoft.com/en-US/windows-vista/fix-a-corrupted-user-profile?SignedIn=1

For Windows 7:

http://windows.microsoft.com/en-US/windows7/Fix-a-corrupted-user-profile

Dave

It seems like a long process.  The PC has over 16gb of data and over 10 installed apps that the user uses on a daily bases.  Beside fixing the Excel problem, will this process damage any of the current installation?

Sorry for the constant concern.  Is that the user is very picky and if we go thru the process and the user cant access non of his apps, will have trouble.

Thanx, will try Office Repair.

Didn't work.

Ok - then re install Office.  I just did that at home today as a matter of fact - it doesn't take that long.

Barring that - recreating the user profile may be the last resort.  Probably will need to be, but I'll hold out hope on the office re install.

Dave

Thanx