VBA Excel File staying open in the background

Question

I am currently using a module on Microsoft Access to Open an Excel file and paste the results into an email. The module is working properly, but the Excel file is remaining open in the background. This is causing an issue when I try to run the same module using the same file.

The Excel file I am using also automatically updates a date field, so I also need the close call to save the file beforehand, or ignore the save changes pop-up.

Public Function emailPaste(exFile As String, exSheet As String, exRange As String, _
EmailSubject As String, To_Field As String, Optional CC_Field As String)

Dim rng As Range
Dim OutApp As Object
Dim OutMail As Object
Dim ApXL As Object

Set ApXL = CreateObject("Excel.Application")
ApXL.Workbooks.Open (exFile)


Set rng = Nothing
' Only send the visible cells in the selection.

Set rng = Sheets(exSheet).Range(exRange).SpecialCells(xlCellTypeVisible)

'If rng Is Nothing Then
    'MsgBox "The selection is not a range or the sheet is protected. " & _
           vbNewLine & "Please correct and try again.", vbOKOnly
    'Exit Sub
'End If

With ApXL.Application
    .EnableEvents = False
    .ScreenUpdating = False
End With

Call OpenOutlook
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)


With OutMail
    .To = To_Field
    .CC = CC_Field
    .Subject = EmailSubject
    .HTMLBody = "<BODY style=font-size:12pt;font-family:Calibri> The report: " & EmailSubject & " " & _
"is pasted below. <br><br> Please review it and contact me if there are any issues.<br><br> " _
& RangetoHTML(rng) & ""
    ' In place of the following statement, you can use ".Display" to
    ' display the e-mail message.
    .Display
End With
On Error GoTo 0

With ApXL.Application
    .EnableEvents = True
    .ScreenUpdating = True
End With

ApXL.Quit

Set ApXL = Nothing
Set OutMail = Nothing
Set OutApp = Nothing
End Function

How can I add at the end the code needed to save the excel file and close it without any user intervention?

remove that `On Error GoTo 0`. Close the Workbook first, without saving. — cyboashu, Aug 27 '18 at 14:53
I tried just closing the workbook, but the save changes pop-up appears. I tired to save it first but i get an error every time i run it. The code I'm using to save it is: ApXL.Workbooks.Save — Kevin F, Aug 27 '18 at 15:36

score 0 · Answer 1 · answered Aug 27 '18 at 15:36

0

You must be very strict in opening the Excel objects and closing them in reverse order - as done in this example:

Public Sub RenameWorkSheet()

    Dim xls     As Excel.Application
    Dim wkb     As Excel.Workbook
    Dim wks     As Excel.Worksheet

    Set xls = New Excel.Application
    Set wkb = xls.Workbooks.Open("c:\test\workbook1.xlsx")
    Set wks = wkb.Worksheets(1)

    wks.Name = "My New Name"
    wkb.Close True

    Set wks = Nothing
    Set wkb = Nothing

    xls.Quit

    Set xls = Nothing

End Sub

answered Aug 27 '18 at 15:36

Gustav

53,498
7
29
55

So I did what you suggested, and it seemed to be working, but shutting down showed me windows was still running the excel file in the background – Kevin F Aug 28 '18 at 12:37
`Dim ApXL As Excel.Application Dim WbXL As Excel.Workbook Set ApXL = New Excel.Application Set WbXL = ApXL.Workbooks.Open(exFile) Set rng = Nothing ' Only send the visible cells in the selection. Set rng = WbXL.Sheets(exSheet).Range(exRange).SpecialCells(xlCellTypeVisible) End With ApXL.DisplayAlerts = False WbXL.Save WbXL.Close Set WbXL = Nothing ApXL.DisplayAlerts = True ApXL.Quit Set ApXL = Nothing End Function` – Kevin F Aug 28 '18 at 12:40
Well. You opened `rng` without using a/the worksheet object, and without setting `rng = Nothing` before exiting. That will leave the Excel instance open. So try again: _You must be very strict in opening the Excel objects and closing them in reverse order._ – Gustav Aug 28 '18 at 12:56

score 0 · Answer 2 · answered Aug 27 '18 at 15:44

You should try to tell the Application, that the Worksheet is saved as it is.
Then Close the Worksheet
Then try to Close the Application.

something like this:

exFile.Sheets(exSheet).Saved = True
exFile.Sheets(exSheet).Close
ApXL.Quit

Or tell, that it doesn't have to save on closing...:

exFile.Sheets(exSheet).Close False
ApXL.Quit

I'd also propose, that you should store a direct reference to the Sheet and not implicitly calling the sheet via the active window...

Something like

dim wsh as Worksheet
set wsh = exFile.Sheets(exSheet)

then you can work with the variable wsh... more comfortable

VBA Excel File staying open in the background

2 Answers2

Linked