VBA to refresh PowerQuery tables

[stuartmacdonald]

Help!!

I'm trying to use PowerQuery with PowerApps, however, you cannot have a PowerApps connection to a PowerQuery table, its stops refreshing the table once connected to PowerApps, however the PowerQuery itself still updates. As you also cannot use a macro-enabled workbook with a connection to PowerApps, I'm trying to develop a macro in a separate workbook that opens up the 'Project check.xlsm' workbook, deletes the PowerQuery tables (x5), reloads them and closes the workbook. This way I can manually refresh with one click....

However, I have no experience of VBA... Below is what I have come up with by inserting an instruction to open the workbook above a macro to delete and reload tables. But it's not working... doesn't even get into the macro - stops at Windows("Project check.xlsx").Activate

Any ideas (or alternative suggestions) very welcome....

Sub Open_Workbook_Basic()    
    Workbooks.Open Filename:="C:\Users\Stuart MacDonald\OneDrive - Cles\Order book\Project check.xlsx"
    
Sub Refresh_tables()
'
' Refresh_tables Macro
'
    Windows("Project check.xlsx").Activate
    Cells.Select
    Range("CLES_days_spent[[#Headers],[Contract_No&Desc]]").Activate
    Selection.ClearContents
    ActiveWorkbook.Connections("Query - CLES_days_spent").Refresh
    With ActiveSheet.ListObjects.Add(SourceType:=4, Source:=ActiveWorkbook. _
        Connections("Query - CLES_days_spent"), Destination:=Range("$L$1")). _
        TableObject
        .RowNumbers = False
        .PreserveFormatting = True
        .RefreshStyle = 1
        .AdjustColumnWidth = True
        .ListObject.DisplayName = "CLES_days_spent"
        .Refresh
    End With
    Sheets("CERN").Select
    Cells.Select
    Range("CERN_days_spent[[#Headers],[Contract_No&Desc]]").Activate
    Selection.ClearContents
    ActiveWorkbook.Connections("Query - CERN_days_spent").Refresh
    With ActiveSheet.ListObjects.Add(SourceType:=4, Source:=ActiveWorkbook. _
        Connections("Query - CERN_days_spent"), Destination:=Range("$B$1")). _
        TableObject
        .RowNumbers = False
        .PreserveFormatting = True
        .RefreshStyle = 1
        .AdjustColumnWidth = True
        .ListObject.DisplayName = "CERN_days_spent"
        .Refresh
    End With
    Sheets("Grant").Select
    Cells.Select
    Range("Grant_days_spent[[#Headers],[Contract_No&Desc]]").Activate
    Selection.ClearContents
    ActiveWorkbook.Connections("Query - Grant_days_spent").Refresh
    With ActiveSheet.ListObjects.Add(SourceType:=4, Source:=ActiveWorkbook. _
        Connections("Query - Grant_days_spent"), Destination:=Range("$L$1")). _
        TableObject
        .RowNumbers = False
        .PreserveFormatting = True
        .RefreshStyle = 1
        .AdjustColumnWidth = True
        .ListObject.DisplayName = "Grant_days_spent"
        .Refresh
    End With
    Sheets("Training_events").Select
    Cells.Select
    Selection.ClearContents
    ActiveWorkbook.Connections("Query - Training_events_days_spent").Refresh
    With ActiveSheet.ListObjects.Add(SourceType:=4, Source:=ActiveWorkbook. _
        Connections("Query - Training_events_days_spent"), Destination:=Range("$A$1") _
        ).TableObject
        .RowNumbers = False
        .PreserveFormatting = True
        .RefreshStyle = 1
        .AdjustColumnWidth = True
        .ListObject.DisplayName = "Training_events_days_spent"
        .Refresh
    End With
    Sheets("Membership").Select
    Cells.Select
    Selection.ClearContents
    ActiveWorkbook.Connections("Query - Membership_days_spent").Refresh
    With ActiveSheet.ListObjects.Add(SourceType:=4, Source:=ActiveWorkbook. _
        Connections("Query - Membership_days_spent"), Destination:=Range("$A$1")). _
        TableObject
        .RowNumbers = False
        .PreserveFormatting = True
        .RefreshStyle = 1
        .AdjustColumnWidth = True
        .ListObject.DisplayName = "Membership_days_spent"
        .Refresh
    End With
    ActiveWorkbook.Save
    ActiveWindow.Close
End Sub