0

Let's say I have two macros in Excel. One for Refresh Data (get them via query from database), one for email sending.

Now, I have Master Macro, which looks like this:

 Call RefreshAll
 Application.Wait (Now + TimeValue("0:00:10"))
 Call SendMail

I just needs to clarify my solution that it works this way when I run Master Macro: Call RefreshAll so data are loaded from database to Excel, wait 10 seconds so the query has enough time to load them all and then SendMail.

The reason why I am asking it seems it does not work properly (old data are sent, not the updated ones). Does the Application.Wait (Now + TimeValue("0:00:10")) also cause the query to pause? If so, how should I modify my code so it works as described solution I need?

Community
  • 1
  • 1
DNac
  • 2,343
  • 7
  • 26
  • 52
  • If you built your database query to excel as a data table and you refresh that connection, the refreshing a database connection should make excel wait automatically for the data to refresh before the code is allowed to continue execution so there should be no need to add any wait.. – Han Soalone Sep 08 '14 at 06:54
  • Also to read the developer reference [here](http://office.microsoft.com/client/helppreview14.aspx?AssetId=HV080000484&lcid=2057&NS=EXCEL%2EDEV&Version=14&tl=2&respos=0&CTT=1&queryid=28977997%2Df56b%2D452c%2D9d40%2Df14459a73a3a) – Han Soalone Sep 08 '14 at 06:59
  • 1
    possible duplicate of [Wait until ActiveWorkbook.RefreshAll finishes - VBA](http://stackoverflow.com/questions/22083668/wait-until-activeworkbook-refreshall-finishes-vba) – L42 Sep 08 '14 at 07:17
  • You should be able to simply change the connection properties to not refresh in the background. That way your code won't continue until the query has refreshed and you can eliminate the trial and error aspect. ;) – Rory Sep 08 '14 at 07:25
  • 2
    Thanks. Ill go for the "DoEvents" and uncheck "Enable Refrsh Background" and see. – DNac Sep 08 '14 at 08:31

0 Answers0