0

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

Community
  • 1
  • 1
D. Van
  • 1
  • 2
    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](http://stackoverflow.com/q/26983053/11683). – GSerg Mar 23 '17 at 18:35
  • 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 Mar 23 '17 at 19:30

1 Answers1

0

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

Application.ScreenUpdating = True
Kev
  • 100
  • 8