VBA for excel running Macros

Question

I have a macro running sheets in excel where the data being shown is connected to an external database. I am trying to have the data that is being shown refreshed as the data source is updated, all while the macro is running. To be clear, by "running" I mean the macro is displaying each sheet in the workbook for a number of seconds and just looping.

Here is the code:

Option Explicit
Private Declare Function GetAsyncKeyState Lib "user32" (ByVal vKey As             
Long) As Integer 


Public Sub Switch()
Dim ws As Worksheet 

    Do
      For Each ws In ThisWorkbook.Worksheets
        ws.Activate
        Application.Wait Now() + TimeValue("00:00:05")
        If GetAsyncKeyState(vbKeyShift) Then Exit Sub
        DoEvents
      Next ws
    Loop

End Sub

I have tried to insert ActiveWorkbook.RefreshAll just before 'Next ws' and I have also tried ThisWorkbook.RefreshAll in the same location.

As of now, when I am told the data has been updated, I stop the macro from running, which lets the data refresh, then I run the macro again. This is what I am trying to avoid having to do.

Thank you in advance for your help.

Thanks,

Derek

I read your question several time and I'm still not sure what you are trying to do. If you want to wait until a query refreshes and continue, call .Refresh False on the query table. If you have asynchronously refreshing queries that you do not control, subscribe to their AfterRefresh event. — GSerg
Hi GSerg, So when I'm running the macro, it's playing through each sheet for 5 seconds, and just looping through, just like watching a slide show. While this is running, data from the server which is the data that is being displayed on the sheets is being updated once or twice a day. I would like to have the macro to refresh while it is running to display the new data. — D. Van

Kev Kev · Accepted Answer · 2017-03-23T19:14:45

You can turn screen updating on or off during execution of your macro. The macro will run slower with it on.

Application.ScreenUpdating = True

VBA for excel running Macros

1 Answers