From MS Access (VBA) determine if Excel is running and workbook is open. If not, open it. Otherwise, go to Excel file.

[eleone8]

In MS Access 2016, I have a subroutine which currently 1) runs an Access query, 2) opens Excel application, 3) opens an Excel workbook, 4) activates the workbook, 5) selects a specific worksheet and 6) refreshes all pivot tables (with results from query that was run in Access).

Unfortunately, if I return to Access and run the subroutine (form command button) again with, let's say, new criteria, it opens another instance of Excel and another (read only) copy of the workbook. I would like the subroutine to be able to first check if Excel is running and if the workbook is already open. If it is, simply go to the workbook and worksheet and then refresh the pivot tables.

This is what I have so far; as you can see, it's missing the steps to first check if the workbook is already open before opening Excel application and another copy of the workbook (NOTE: I'm fairly new with VBA). Any help would be greatly appreciated.

Private Sub cmdOK2_Click()
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "qryPaymentsByPayDate", acViewNormal, acReadOnly

    Dim xlApp As Object
    Dim xlWorkbook As Object
    Dim xlSheet As Object
    Set xlApp = CreateObject("Excel.Application")
    xlApp.Visible = True
    Set xlWorkbook = xlApp.Workbooks.Open("C:\Users\f252922\Documents\0-Databases\AR Payments Report.xlsx")
    Set xlSheet = xlWorkbook.Sheets("By Payment Date")
    xlWorkbook.Activate
    xlSheet.Select
    xlApp.ActiveWorkbook.RefreshAll
    Set xlSheet = Nothing
    Set xlWorkbook = Nothing
    Set xlApp = Nothing

End Sub

FYI...I tried this too. It doesn't error out, but it doesn't work. Still opens second instance of Excel app and second read only copy of workbook:

Function CheckFileIsOpen(chkSumfile As String) As Boolean

    On Error Resume Next
    CheckFileIsOpen = (Excel.Application.Workbooks(chkSumfile).Name = chkSumfile)
    On Error GoTo 0

End Function

Private Sub cmdOK1_Click()
     DoCmd.SetWarnings False
     DoCmd.OpenQuery "qryPaymentsByInvDate-2", acViewNormal, acReadOnly

    Dim xlApp As Object
    Dim xlWorkbook As Object
    Dim xlSheet As Object

If CheckFileIsOpen("C:\Users\f252922\Documents\0-Databases\AR Payments Report.xlsx") = True Then
    Set xlApp = Excel.Application
    xlApp.Visible = True
    Set xlWorkbook = Excel.Application.Workbooks("C:\Users\f252922\Documents\0-Databases\AR Payments Report.xlsx")
    Set xlSheet = xlWorkbook.Sheets("By Invoice Date")

Else
    Set xlApp = CreateObject("Excel.Application")
    xlApp.Visible = True
    Set xlWorkbook = xlApp.Workbooks.Open("C:\Users\f252922\Documents\0-Databases\AR Payments Report.xlsx")
    Set xlSheet = xlWorkbook.Sheets("By Invoice Date")

End If

    xlWorkbook.Activate
    xlSheet.Select
    xlApp.ActiveWorkbook.RefreshAll
    Set xlSheet = Nothing
    Set xlWorkbook = Nothing
    Set xlApp = Nothing
     
End Sub

 

[Norie]

You could use GetObject to try and find the workbook if it's open.

If GetObject is unsuccessful then it means the workbook isn't open and you can then use CreateObject to open a new instance of Excel, open the workbook etc.

PS Why aren't you closing the workbook and quitting Excel in your current code?

 

[RoryA]

Try this:

Private Sub cmdOK2_Click()
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "qryPaymentsByPayDate", acViewNormal, acReadOnly

    Dim xlApp                 As Object
    Dim xlWorkbook            As Object
    Dim xlSheet               As Object
    Set xlWorkbook = GetObject("C:\Users\f252922\Documents\0-Databases\AR Payments Report.xlsx")
    Set xlApp = xlWorkbook.Application
    xlApp.Visible = True
    xlWorkbook.Activate
    xlSheet.Select
    xlWorkbook.RefreshAll
    Set xlSheet = Nothing
    Set xlWorkbook = Nothing
    Set xlApp = Nothing

End Sub