EXCEL.EXE getting stuck in memory - How to clear please

knookie

New Member
Joined
Aug 3, 2012
Messages
7
I have the following code which happily creates an excel document but after it runs i am seeing an EXCEL.EXE still residing in memory. If i run it multiple times i will see several of them in memory. Does anyone know what i need to do to close it down properly and free up the memory.

Code:
Sub ExportToExcel()
'***************************************************************************
'write and format results to excel
'***************************************************************************
    strPathExcel = "H:\Circulation\Data ops\Blue Sheep\Daily Report File\"
    strFile = "Audit_Report_"
    strSaveFile = strPathExcel & strFile
    
    Set objExcel = CreateObject("Excel.Application")
    
    With objExcel
        .Visible = False
        Set XLDoc = .Workbooks.Add        'new workbook
        .ActiveWorkBook.Worksheets.Add    'new worksheet within new workbook
        'create a worksheet
        Set XLSheet = .Worksheets(1)
    End With
    
    'widen worksheet columns to stop text wrapping in cells
    With objExcel
        .Worksheets(1).Columns("A").ColumnWidth = 30
        .Worksheets(1).Columns("B").ColumnWidth = 35
        .Worksheets(1).Columns("C").ColumnWidth = 0        'Hide the empty column
    End With    
        
    ActiveDocument.GetSheetObject("CH_Audit").CopyTableToClipboard true        'copy QV results chart to clipboard
    XLSheet.Paste XLSheet.Range("A1")                                        'paste in QV results pivot chart at cell A1 on sheet1
    objExcel.ActiveSheet.Name = "Daily Data Load - Audit Report"            'rename sheet1
    
    objExcel.Worksheets(1).Rows("1").HorizontalAlignment = -4130            'left align header row on first sheet
    objExcel.Worksheets(1).Rows("1").Font.Bold = True                        'Bold header row

    XLSheet.UsedRange.Columns.AutoFit            'autofit each column width
    XLSheet.Columns("C").ColumnWidth = 0        'rehide the empty column
    
    objExcel.Range("D2").Select                    'Freeze first row and first 3 columns
    objExcel.ActiveWindow.FreezePanes = True

    objExcel.DisplayAlerts = False            'disable any popup messages that may appear such as the overwrite existing file message
    objExcel.ActiveWorkBook.SaveAs strSaveFile & replace(date, "/", "-") & ".xlsx"    'save file with todays date, and overwrite if it exists already
    objExcel.DisplayAlerts = True
    
    objExcel.Quit                'Close spreadsheet
    Set objExcel = Nothing        'clear objects from memory
    
    Msgbox "Spreadsheet Saved"

End Sub
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

BrianMH

Well-known Member
Joined
Mar 26, 2008
Messages
1,490
Code:
    objExcel.Quit                'Close spreadsheet
    Set objExcel = Nothing        'clear objects from memory
These lines are what you need. Is the code stopping due to errors and you have to rerun it? If so it's not getting to the point of closing the object. You could add error handling in there to do this.
 

Watch MrExcel Video

Forum statistics

Threads
1,095,423
Messages
5,444,374
Members
405,278
Latest member
karen1

This Week's Hot Topics

Top