cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PowerBI_Query
Helper II
Helper II

Background Query Completion

I have created a VBA code to import data from CSV convert it into a table and refresh the query that is already setup.

I want the user to be informed when the background query is completed by displaying a VBA msgbox.

How to sync background query completion with VBA msgbox?

Is there anyother way to display background query completion.

1 ACCEPTED SOLUTION
PowerBI_Query
Helper II
Helper II

Posted the question on VBA forum and got the below solution it works flawless.

 

With ThisWorkbook
        For Each objConnection In .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
        'Save the updated Data
        '.Save
End With
MsgBox "Completed"

 

 

View solution in original post

4 REPLIES 4
PowerBI_Query
Helper II
Helper II

Posted the question on VBA forum and got the below solution it works flawless.

 

With ThisWorkbook
        For Each objConnection In .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
        'Save the updated Data
        '.Save
End With
MsgBox "Completed"

 

 

Hi @PowerBI_Query,

 

Well done!

Sorry, misunderstood the requirements. The code that you've posted is not running queries in the background, therefore the user would not be able to do anything until it is completed. My initial thought was that you want the query to run in the background while the user is working on something else and notify once completed to track their attention.

 

Cheers,

John

 

N.B. Could you please mark your last post as asolution to close the topic and help others to find answers for a similar problems? 

jbwtp
Solution Sage
Solution Sage

Hi @PowerBI_Query,

 

You need to capture QueryTable_AfterRefresh event in VBA. How to do it is quite separate question. Search on the Internet as non-standard events may at time be quite complicated, it depends on the scenario you would like to implements, etc.

 

Kind regards,

John  

edhans
Super User
Super User

I am not aware of this being possible. There is no trigger from Power Query refreshing in the background that the VBA object model can pick up on, but to be 100% sure your best bet is in the VBA forums of the Excel groups. This is a Power BI forum and it doesn't have, nor will ever have, VBA.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show episode 9

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 25

Ted's Dev Camp - August 25, 2022

Watch Session 25 of Ted's Dev Camp.

Top Kudoed Authors