Excel stays in Memory

[azizrasul]

I have the following snippet of code in Access: -

            strFilename = "U:\Reports\" & Left(strTrustName, 49)
            DoCmd.TransferSpreadsheet acExport, 8, "qryAllTrustsSummarisedReport", strFilename, True, ""
            
            Set objExcelApp = CreateObject("Excel.Application")
            
            objExcelApp.Visible = True
            Workbooks.Open Filename:="U:\Reports\" & strTrustName & ".xls", ReadOnly:=False
            Worksheets(1).Name = "Cost Savings Report on Food"
            objExcelApp.DisplayAlerts = False
            Worksheets(1).SaveAs strFilename

            objExcelApp.Quit
            Set objExcelApp = Nothing

When I try to open the xls file, I only see the header of the application. I can resolve the problem by ending the application in ther task manager. How do I amend the code so that Excel leaves memory automatically.

Just realised I have placed this in the wrong section. My apologies.

 

[Cbrine]

worksheets(1).SaveAs strFilename
objExcelApp.Quit


Here's your problem.

You are calling the saveas dialog, with excel not shown? Then trying to quit. The quit won't work because the dialog is still showing(on the Visible=false instance of Excel). Change it to this.

worksheets(1).close true, strfilename
objExcelApp.quit

HTH
Cal

 

[azizrasul]

I tried that but I get error 438 (Object doesn't support this property or method). The library references I have are: -

Visual Basic for Applications
Microsoft Access 9.0 Object Library
Microsoft Excel 9.0 Object Library
Microsoft DAO 3.6 Object Library
OLE Automation

 

[Cbrine]

Give this a try.

Dim wb as workbook, ws as worksheet
strFilename = "U:\Reports\" & Left(strTrustName, 49) 
DoCmd.TransferSpreadsheet acExport, 8, "qryAllTrustsSummarisedReport", strFilename, True, "" 
            
Set objExcelApp = CreateObject("Excel.Application") 
            
objExcelApp.Visible = True 
set wb=Workbooks.Open Filename:="U:\Reports\" & strTrustName & ".xls", ReadOnly:=False 
set ws = worksheets(1)

ws.Name = "Cost Savings Report on Food" 
objExcelApp.DisplayAlerts = False 
wb.close true, strfilename 

objExcelApp.Quit 
Set objExcelApp = Nothing

 

[azizrasul]

I get an error on the following line

Set wb = Workbooks.Open Filename:="U:\Reports\" & strTrustName & ".xls", ReadOnly:=False

I get a compile error where Filename is highlighted.

 

[Cbrine]

Try changing it to this, I fogot to add the brackets, which you need to do when setting it to a variable.

Set wb = Workbooks.Open("U:\Reports\" & strTrustName & ".xls", , False)

Cal

 

[azizrasul]

Since I last visited, a colleague of mine has been trying to help me out. Here's the revised code which now works.Thanks for your input.

            Set objExcelApp = New Excel.Application
            objExcelApp.Visible = False
            
            With objExcelApp
               .Workbooks.Open Filename:=strFilename & ".xls", ReadOnly:=False
               .Workbooks.Item(.Workbooks.Count).Activate
               .ActiveWorkbook.Worksheets(1).Activate
               .ActiveSheet.Name = "Cost Savings Report on Food"
               .DisplayAlerts = False
               .ActiveWorkbook.SaveAs strFilename
               .ActiveWorkbook.Close SaveChanges:=False
               .DisplayAlerts = True
               If .Workbooks.Count = 0 Then
                  .Quit
               End If
            End With
            Set objExcelApp = Nothing

I will try to get my original code and try your last amendment and see if it worked.