Macro problem, refresh data and call other macro, call is happening before refresh is complete

Will85

Board Regular
Joined
Apr 26, 2012
Messages
240
Office Version
  1. 365
Platform
  1. Windows
I have a connection to a SQL table in my excel workbook pulling accounting data.

I have a macro that refreshes the workbook, so that my table is up to date.

I have another macro that hides blank rows (Income Statement accounts with no activity)

When I refresh my connection, there is an approximate 7 second delay from hitting refresh (whther through the macro ThisWorkbook.RefreshAll) or going to data and clicking refresh) and when it actually begins updating my table (it takes another 40 seconds to load about a 100k records).

I want my macro to refresh the data then call the other macro to hide blank rows. Except that its calling the other macro during the 7 second delay, before my data is ever refreshed. Forcing a delay between refresh and call doesnt work.

Current code that doesnt work (with or without the Wait). Its hiding blank rows before the table is refreshed.

Sub Refresh()

ThisWorkbook.RefreshAll

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

Call Hide_Blank_Rows

End Sub
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
I went to the query, right clicked, went to properties, and disabled the background refresh. This solved the issue.
 
Upvote 0

Forum statistics

Threads
1,212,928
Messages
6,110,734
Members
448,294
Latest member
jmjmjmjmjmjm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top