Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Wait until ActiveWorkbook.RefreshAll finishes - VBA

Tags:

excel

vba

I have a sub that calls on ActiveWorkbook.RefreshAll to bring new data in from an XML source, and then performs multiple modifications to it. The problem is that not enough time is given for the RefreshAll command to finish, so the following subs and functions end up not executing correctly, which result in repeated rows not being correctly erased.

I have tried using Application.Wait and the Sleep function, but they seem to pause the refresh process too. I simply want the rest of the code to wait until the refresh process finishes before executing the rest of the code.

Any ideas on how to implement this? Right now I was only able to fix it by not calling on RefreshAll, which gives me the idea of implementing a second flow to be executed afterwards, but that's not a good workaround.

Please let me know if any of this wasn't clear. Thanks

EDIT So I tried a few suggestions from the posts below, and this is what I was able to come up with. Doing a "record macro" and then UNCHECKING the "Enable background refresh" in the table properties did not result in anything. I did a refresh as well afterwards. This was the result of the recorded macro:

With ActiveWorkbook.Connections("XMLTable")         .Name = "XMLTable"         .Description = "" End With ActiveWorkbook.Connections("XMLTable").refresh 

The class ActiveWorkbook.Connections does NOT have a BackgroundQuery option so that I can set it to False. Any ideas?

Just to be clear. This is an XML file hosted on a website which Excel goes and imports into a table. I then call that data into a pivot and other things. The goal here is to allow the import process from the website to the table to finish BEFORE executing any other commands. Thanks

EDIT2: After a little more research, I have found this page: http://www.mrexcel.com/forum/excel-questions/564959-execute-code-after-data-connection-refresh-finished.html It appears that an XML type of connection does not have a BackgroundQuery boolean. That option is only available for ODBC and OLEDB connections, which are types xlConnectionTypeODBC and xlConnectionTypeOLEDB, respectively. The XML connection I am using is of type xlConnectionTypeXMLMAP which does not have a BackgroundQuery option. Does anyone have any idea on where to go from here? The only solution I have in mind right now is to make two seperate macro buttons on the excel sheet, one for refreshing and one for data modification, but I'd rather keep that option to the very last.

like image 915
Mo2 Avatar asked Feb 28 '14 00:02

Mo2


People also ask

How do you refresh data connection in Excel VBA?

Update all data in the workbook Press CTRL+ALT+F5, or on the Data tab, in the Connections group, click Refresh All.

How do you delay a macro in Excel?

Use Sleep Function in VBA And then you need to make sure to append the “PtrSafe” statement if you are using 64 Bit Excel. Next, you need to call the sleep function in the code. In the end, specify the time (milliseconds) for which you want to delay the code.

How do you refresh all pivot tables in VBA?

Use the “Refresh All” Button to Update all the Pivot Tables in the Workbook. The “Refresh All” button is a simple and easy way to refresh all the pivot tables in a workbook with a single click. All you need to do it is Go to Data Tab ➜ Connections ➜ Refresh All.

How do you select entire ActiveSheet cells?

Note that since you want to select the cell in the active sheet, you just need to specify the cell address. But if you want to select the cell in another sheet (let's say Sheet2), you need to first activate Sheet2 and then select the cell in it.


2 Answers

I had the same issue, however DoEvents didn't help me as my data connections had background-refresh enabled. Instead, using Wayne G. Dunn's answer as a jumping-off point, I created the following solution, which works just fine for me;

Sub Refresh_All_Data_Connections()      For Each objConnection In ThisWorkbook.Connections         'Get current background-refresh value         bBackground = objConnection.OLEDBConnection.BackgroundQuery          'Temporarily disable background-refresh         objConnection.OLEDBConnection.BackgroundQuery = False          'Refresh this connection         objConnection.Refresh          'Set background-refresh value back to original value         objConnection.OLEDBConnection.BackgroundQuery = bBackground     Next      MsgBox "Finished refreshing all data connections"  End Sub 

The MsgBox is for testing only and can be removed once you're happy the code waits.

Also, I prefer ThisWorkbook to ActiveWorkbook as I know it will target the workbook where the code resides, just in case focus changes. Nine times out of ten this won't matter, but I like to err on the side of caution.

EDIT: Just saw your edit about using an xlConnectionTypeXMLMAP connection which does not have a BackgroundQuery option, sorry. I'll leave the above for anyone (like me) looking for a way to refresh OLEDBConnection types.

like image 100
Valiante Avatar answered Oct 18 '22 03:10

Valiante


Though @Wayne G. Dunn has given in code. Here is the place when you don't want to code. And uncheck to disable the background refresh.

enter image description here

like image 43
subro Avatar answered Oct 18 '22 03:10

subro