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.
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:
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.
Code:
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:
Code:
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
Last edited by a moderator: