Opening and working in other workbooks while VBA is running

[sdoppke]

Hello - I am a workbook that counts down and scrapes a website every 30 minutes. However I am unable to open and work in other workbooks while this workbook countdown is running.

I use the DoEvents code, but this still does not let me work in other WBs at the same time.

Any tips or direction would really be appreciated.

Thanks!
SD

 

[jmacleary]

Hi there. Have a look at using the Application.Ontime method. This schedules a macro to run in the future (i.e. your scraper code), but in the meantime will let you work on other workbooks.

 

[sdoppke]

Oh wow, thanks a ton! I will do that.

SD

 

[sdoppke]

Hi there. Have a look at using the Application.Ontime method. This schedules a macro to run in the future (i.e. your scraper code), but in the meantime will let you work on other workbooks.

Thought I would share what worked for me. Thanks again John. BTW, I'm a dirty coder, so be aware my code is not the most efficient.

Option Explicit
Public ScheduledTime As Double
Public Const Interval = 5 '5 seconds
Public Const MyProc = "ScrapeThis"

Sub Timer()

ScheduledTime = Now + Workbooks("RTM_Tool").Sheets("RTM").Range("K9").Value
Application.OnTime ScheduledTime, MyProc

End Sub

Sub stopTimer()

On Error Resume Next
Application.OnTime ScheduledTime, "ScrapeThis", , False
MsgBox "Timer Stopped, needs to be re-started to work again..."

End Sub

 

[jmacleary]

Thanks for the feedback and well done on deriving a solution from my small guidance. We're nearlyall dirty coders!