VBA To Import From Excel

[kd2017]

From a module within my Access database how can I open an excel workbook and force my sub to wait until the workbook finishes refreshing IT'S own data from it's data sources?

Public Sub ImportData()
On Error GoTo ErrHandler
    Dim ex As Excel.Application
    Dim wb As Excel.Workbook
    
    Debug.Print Now() & " Importing data..."
    
    Set ex = CreateObject("Excel.Application")
    Set wb = ex.Workbooks.Open(DATA_FILE)

    'My code shouldn't execute further until wb has refreshed it's own underlying data.

I have an excel file (raw data) that is processed by a second excel file. This second excel file uses Power Query to quickly parse, filter, sort, preprocess all my data that I then can easily import into my access tables. The power query is set to "Refresh data when opening file". Also, does a workbook even fresh it's own data when I open it from vba in this manner?

The power query works great and my import code will work to populate the tables fine, no issues there. it just doesn't wait for the workbook to refresh it's data first.

I've already tried using VBA to import my my data directly from the raw data file but that was turning out to be full of headaches.

[Micron]

You might get good suggestions on alternate methods to crunch or link your data, but in the meantime, here's how you can get Access to wait an arbitrary amount of time. In a standard module:

Public Function Pause(intSecs As Integer)
Dim Start As Variant
Start = Timer
Do While Timer < Start + intSecs
    DoEvents
Loop
End Function

and use as Pause 4. I know of no way to determine from Access if a workbook has finished updating from its data source.