VBA wait to close source until RefreshAll complete

Question

Here is my code:

Private Sub Workbook_Open()

Dim entry

entry1 = InputBox("Please enter requested date.")

Dim wkb1, wbk2 As Workbook

Set wkb1 = Workbooks.Open("file1")

wkb1.Sheets("Report Date").Range("B3").Value = entry1

wkb1.RefreshAll

Set wkb2 = Workbooks.Open("file2")

wkb2.RefreshAll

wkb1.Close SaveChanges:=True

Application.DisplayFullScreen = True

ThisWorkbook.Close SaveChanges:=False

End Sub

ThisWorkbook - File with the above code

File 1 - Excel-Access query file with charts created from the tables of table pulled from access via the query

File 2 - Charts copied with links to those in File 1 

My objective: Have a user input a date and the file 2 display board updates the charts accordingly.

Steps:

1. Open Master file "ThisWorkbook"
2. Display input box for user to enter a date
3. The date if pushed to a cell location in file 1. This cell is the date criteria in Excel for some MS Access queries.
4. RefreshAll file 1 which ensures that queries are updated, and any charts created off of the query tables are updated.
5. Open File 2
6. RefreshAll file 2 to update dashboard charts.
7. Close File 1 and master leaving the display showing and updated.

Problem: Closing file 1 happens before refreshing file 2 occurs, so the charts do not update. The only success I have had is by leaving file one open and allowing enough time for the charts to update. I have tried different forms of Do until, DoEvents, and Application.Wait with no success. I have also seen that the charts in file 2 will not update unless it is opened while file 1 is open.

Thanks for any help,

Tim F.

Answer 1

Refer to the Querytables in each file directly, add a loop that checks the Refreshing property, e.g.:

Set Q = wbk2.Worksheets(1).QueryTables(1)

  Do While Q.Refreshing

    'Wait

    Application.StatusBar = "Query is currently refreshing: please wait"

    DoEvents

  Loop

  'Done

  Application.StatusBar = False

Do the same with the other file and Querytables.

Andreas.