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.
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.
VBA Code:
Sub Test()
Application.SendKeys ("{F9}")
ThisWorkbook.RefreshAll
Application.CalculateUntilAsyncQueriesDone
Call Application.Wait(Now + TimeValue("0:01:00"))
MsgBox ("Done")
End Sub