How to get the refresh status of each power query of a excel workbook via VBA?

[Anonymous]

Dear experts. 

I am doing some PoC test for a PowerQuery project via VBA. Here are the requirement

1. Need to refresh the queries in a given order
2. The query behind needs to start once the previous query finishes and successes, if fails, need to provide error reason. Msgbox(query name, refresh status, error reason if applied)

I tried to use AfterRefresh event But I couldn't get the individual query refresh status, just overall status. And I tried to disable the background fresh , but didn't find a way if it worked or not. Please could I have your help. Thanks in advance, Here are the codes:

1. Class Modules( I expect to show the queryname in the Msgbox, how could add ?)

I found the BackgroundQuery=True must be set. otherwise, if it set to False the afterfresh event will not be triggered. I got lost....

Here are the codes:

Thanks!

Corydon

--------------------------

Option Explicit

Public WithEvents MyQuery As QueryTable

'Public s_QueryName As String

Private Sub MyQuery_AfterRefresh(ByVal Success As Boolean)

    If Success Then

        MsgBox (s_QueryName & "   has been refreshed.")

       ' count = count + 1

        'MsgBox (count)

    Else

    End If

End Sub

Private Sub MyQuery_BeforeRefresh(Cancel As Boolean)

    If MsgBox("Refresh query?", vbYesNo) = vbNo Then

        Cancel = True

    Else

    End If

End Sub

---------------------------------------------------------------------

1. Modules

-------------------------------------------------------------------

   Option Explicit

Dim colQueries As New Collection

Public s_QueryName As String

'Public count As Integer

Sub InitializeQueries()

Dim clsQ As clsQuery

Dim WS As Worksheet

Dim QT As QueryTable

Dim LO As ListObject

For Each WS In ThisWorkbook.Worksheets

    For Each QT In WS.QueryTables

        MsgBox (WS.Name)

        Set clsQ = New clsQuery

        Set clsQ.MyQuery = QT

        colQueries.Add clsQ

    Next QT

    For Each LO In WS.ListObjects

        MsgBox (WS.Name)

        Set QT = LO.QueryTable

        Set clsQ = New clsQuery

        Set clsQ.MyQuery = QT

        colQueries.Add clsQ

    Next LO

Next WS

End Sub

Private Sub Button1_Click()

    Call InitializeQueries

   ' ActiveWorkbook.Connections("Query - CombineErrorlists").Refresh

    Dim bRfresh As Boolean

   s_QueryName = "Query - CombineErrorlists"

    With ThisWorkbook.Connections("Query - CombineErrorlists").OLEDBConnection

        'bRfresh = .BackgroundQuery

        .BackgroundQuery = True

        .Refresh

        '.BackgroundQuery = bRfresh

    End With

 '  MsgBox ("CombineErrorlists" & "refeesh done")

   ' Application.Wait DateAdd("s", 15, Now)

    s_QueryName = "Query - FilesTransform"

 '   ActiveWorkbook.Connections("Query - FilesTransform").Refresh

    With ThisWorkbook.Connections("Query - FilesTransform").OLEDBConnection

        'bRfresh = .BackgroundQuery

        .BackgroundQuery = True

        .Refresh

        '.BackgroundQuery = bRfresh

    End With

'    MsgBox ("FilesTransform" & " refeesh done")

End Sub

[otravers]

Power Query is increasingly becoming a cloud tool as it's in Power BI dataflows, datasets, and datamarts; PowerApps; Azure Data Factory; and Excel Online. In the latter case support is still limited, but it's improving. Eventually jobs like this will be done entirely in the cloud with a combination of Power Automate and OfficeScripts.

VBA is desktop bound and always will be. I'm sure there are people automating Power Query with VBA, but the lack of response to your various posts is a hint that they're few and far between. I've been working recently with one of my clients to migrate their unmaintainable VBA solution to the cloud, it comes with its own challenges but it's where Microsoft and the industry are going.

[otravers]

You might have better luck in an Excel forum. The intersection of people developing with Power BI and people still using VBA is getting smaller by the day. For the requirement you're describing I'd be using Power Automate.

[Anonymous]

Thanks Otravers. Actually I asked the same question at Excel forum and Stackflow 2 few monthes ago, but haven't got any feedback.  I reckon the scenario I described shoule be very common.  The power query does provide a good way to transform the datam but it doesn't provide enough interfaces working with MS inhouse products eg VBA. My thought was based on the traditional coding approach, considering the customized power queries as functions, using VBA to call them and get the statuses . Maybe I am wrong. There might be  no seamless synergy between VBA and Power Query.  I do hope MS experts here could give me a clear instruct whether my way is feasible or not. Thanks! Btw, I will try Power automate. 

[Jasposs]

Appreciate this is super late, but I think this might help, it certainly helped for me (just today in fact!)

https://stackoverflow.com/questions/43548543/how-to-check-whether-connection-refresh-was-successful