Wait for external database to update before proceeding with code VBA

[Viper147]

Hi all,

Been struggling a few days now with this one and cannot find a solution on the forum, or anywhere else for that matter. I have a file that is used for monthly reporting, and the financial results are being imported via an Excel add-in (no live connection via the Data menu in Excel). Currently we have to cycle through all the various entities on the main cover sheet, and then update them one by one via the add-in by pressing F9 every time the new entity code has been entered. The report per entity then gets printed to PDF and published. This is working fine.
I then decided to expand on the code to perform this automatically by cycling through the list of entity codes and printing them all in one go. Problem is that the data is not being updated before being printed to PDF. I tried a few different options (DoEvents with a loop, switching calculation to manual, Application.wait) but none of them worked. When I only use the one-liner Application.SendKeys ("{F9}") in the code, the data actually updated as it takes around 30-45 seconds for all the numbers to update and reflect on the report. But when I include any additional form of code resulting in waiting then nothing updates. See below what I have at the moment. Any tips would greatly appreciated.

Sub Test()
Application.SendKeys ("{F9}")
ThisWorkbook.RefreshAll
Application.CalculateUntilAsyncQueriesDone
Call Application.Wait(Now + TimeValue("0:01:00"))
MsgBox ("Done")
End Sub

 

[offthelip]

Have you tried this:

Application.OnTime Now + TimeValue("00:00:45"), "my_Procedure"

this will exit vba entirely which may allow the addin to update,

 

[Viper147]

No haven't tried that, but the problem is that in the complete code there are lines of code following the above piece. Hence, I don't wish to exit vba. Just need to find a way for VBA to wait until the data is updated and then proceed with the rest of the code. But all the solutions I've tried thus far, for some unknown reason, does not allow the numbers to be updated. Only when I use Application.SendKeys ("{F9}") as a one-liner does it work and the numbers are updated.