Excel macro refresh data then format cells

Question

I have a worksheet which contains a Pivot Table, the data is populated from a file output by another system.

Within Power Query I transform the data, specifically I use one of the fields to construct a URL.

"'=HYPERLINK(""" & "https://website.com/"&[Code] & """, """ & [Code] & """) "

When the data is in the Pivot Table, I need to manipulate the formatting to get it to display correctly.

In Excel I use a find and replace to convert the string into a proper URL showing the friendly name (and I resize the column).

Sub Macro1()
    Cells.Replace What:="'=", Replacement:="=", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Columns("A:A").EntireColumn.AutoFit
End Sub

Refreshing the data means that the macro above needs running again.

I was keen to automate this for the users. However, running the following macro looks to do the formatting first THEN the refresh, what am I doing wrong?

Sub Macro2()
    ActiveWorkbook.Connections("Query - XXX"). _
        Refresh
'
    Cells.Replace What:="'=", Replacement:="=", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Columns("A:A").EntireColumn.AutoFit
End Sub

NOTE the two parts of the macro work correctly independently, just not when combined as above.

score 1 · Accepted Answer · answered Feb 01 '19 at 19:17

If you find the query in the "Connections" sections on the Data ribbon, open it's properties, and uncheck the "Background Refresh" option, it will pause any VBA code until it is done refreshing. This property can also be set in VBA.

Sub Macro2()

    ActiveWorkbook.Connections("Query - XXX").OLEDBConnection.BackgroundQuery = False
    ActiveWorkbook.Connections("Query - XXX").Refresh

    Cells.Replace What:="'=", Replacement:="=", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Columns("A:A").EntireColumn.AutoFit
End Sub

Excel macro refresh data then format cells

1 Answers1