vba access - Check if excel file is open

Question

Using VBA Access, I'm trying to edit the below code. So that it first checks if an excel file is open , if already open wait till file is closed then resume code? this needs to be done for each file 1,2,3.

Check if excel file is open
If open wait(Pause) till closed then resume code (Refresh table ,save, close)

Repeat process for the next file.

  Function RefreshExcelTables()

  Dim ExcelApp As Object

  Set ExcelApp = CreateObject("Excel.Application")

  ExcelApp.workbooks.Open "c:\test\Test_Sheet1.xlsb"
  ExcelApp.ActiveWorkbook.refreshall
  ExcelApp.ActiveWorkbook.Save
  ExcelApp.ActiveWindow.Close


  ExcelApp.workbooks.Open "c:\test\Test_Sheet2.xlsb"
  ExcelApp.ActiveWorkbook.refreshall
  ExcelApp.ActiveWorkbook.Save
  ExcelApp.ActiveWindow.Close


  ExcelApp.workbooks.Open "c:\test\Test_Sheet3.xlsb"
  ExcelApp.ActiveWorkbook.refreshall
  ExcelApp.ActiveWorkbook.Save
  ExcelApp.ActiveWindow.Close


  Set ExcelApp = Nothing


  End Function

brettdj brettdj · Accepted Answer · 2015-05-21T03:18:45

tested in Outlook

Sub Test()

Dim ExcelApp As Object
Dim X, X1

X = Array("c:\test\Test_Sheet1.xlsb", "c:\test\Test_Sheet2.xlsb")

For Each X1 In X
    On Error Resume Next
    Set ExcelApp = GetObject(X1).Application
    On Error GoTo 0

    If Not ExcelApp Is Nothing Then
        With ExcelApp.Workbooks(Right$(X1, Len(X1) - InStrRev(X1, "\")))
            .RefreshAll
            .Save
            .Close
    End With
    Set ExcelApp = Nothing
    End If

Next X1
End Sub

vba access - Check if excel file is open

1 Answers