How to fix closing Excel from inside Access

Question

I want to close an Excel workbook from inside Access using VBA code. The following code opens the workbook and runs a VBA module in the workbook correctly. Closing the workbook prompts with the standard Save dialogue which I want to avoid.

I tried modifying the statement .Workbooks.Close to .Workbooks.Close SaveChanges:=True but that actually crashes the code.

Public Function Open_Share_Price_Excel()

' Change Cursor to Hourglass
Screen.MousePointer = 11

Dim Expath, ModName As String
Dim XLApp As Object
Set XLApp = CreateObject("Excel.Application")

'
'Define where the Excel Spreadsheet is and the module to run
'

Expath = "C:\Users\peter\Documents\Financial Affairs\Shares\Share Price Bing.xlsm"
ModName = "Combined_Module"

With XLApp
    .Application.Visible = True
    .Application.DisplayAlerts = False
    .UserControl = True
    .Workbooks.Open Expath
    .Run ModName
    .Workbooks.Close
End With

XLApp.Quit
Set XLApp = Nothing

'Change Cursor back and display finished message

Screen.MousePointer = 0
MsgBox ("Price Update Finished - OK to Continue")

End Function

I tried modifying the statement .Workbooks.Close to .Workbooks.Close SaveChanges:=True but that actually crashes the code.

Any suggestions welcome.

Secondly, I would like to ensure that I close all the excel workbooks as I understand that the workbook Personal.xlsx may also be open. When I manually close the Excel spreadsheet it closes, but then EXcel immediately re-opens with a blank workbook.

Help this newbie please

Thank you, I changed that but it doesn't solve my problems. God programming tip though. — PLL

Pᴇʜ Pᴇʜ · Accepted Answer · 2019-09-04T05:49:01

What you need to do is to set the opened workbook to a variable Set OpenedWb = .Workbooks.Open(Expath) so you can access exactly that workbook to close it OpenedWb.Close SaveChanges:=True.

.Workbooks.Close does not work because Workbooks repersents all workbooks and not a specific one (but you must close each workbook on its own).
Please note that Dim Expath, ModName As String only declares ModName As String but Expath As Variant. In VBA you need to specify a type for every variable, otherwise it is Variant by default. So you must use: Dim Expath As String, ModName As String to make them both strings.
Your Function does not return anything. Therfore it should be a procedure Sub instead of a function.

You would end up using something like below:

Public Sub Open_Share_Price_Excel()    
    ' Change Cursor to Hourglass
    Screen.MousePointer = 11
    
    '
    'Define where the Excel Spreadsheet is and the module to run
    '
    Dim Expath As String 
    Expath = "C:\Users\peter\Documents\Financial Affairs\Shares\Share Price Bing.xlsm"
    Dim ModName As String
    ModName = "Combined_Module"

    Dim XLApp As Object
    Set XLApp = CreateObject("Excel.Application")
    With XLApp
        .Application.Visible = True
        .Application.DisplayAlerts = False
        .UserControl = True
    
        Dim OpenedWb As Object
        Set OpenedWb = .Workbooks.Open(Expath)
    
        .Run ModName
    
        OpenedWb.Close SaveChanges:=True
    End With

    XLApp.Quit
    Set XLApp = Nothing
    
    'Change Cursor back and display finished message
    Screen.MousePointer = 0

    MsgBox ("Price Update Finished - OK to Continue")
End Sub

How to fix closing Excel from inside Access

1 Answers