0

The macro below updates Column A with a hyperlink function.

How do I run this after a data refresh on my query/datasource is clicked?

I tried writing a change condition on my worksheet.

Sub InsertHyperlinkFormulaInCell()

currentRow = 2   
While Cells(currentRow, 2) <> "" 'check whether Column B is empty, stop if it is
ActiveWorkbook.Worksheets("Query").Cells(currentRow, 1) = "=HYPERLINK(CONCAT(X" & currentRow & ",B" & 
currentRow & "),W" & currentRow & ")"
currentRow = currentRow + 1
Wend

End Sub
Community
  • 1
  • 1
Adam
  • 83
  • 1
  • 10
  • 2
    Side note: normally the better practice is to [find the last row](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba) and write the formula to the entire range in one step, instead of looping. – BigBen May 05 '20 at 17:29
  • 2
    See https://learn.microsoft.com/en-us/office/vba/api/excel.querytable.afterrefresh – Tarik May 05 '20 at 17:40

1 Answers1

2

Instead of manually refreshing your query, do it from the macro and then continue with your code.

Sub InsertHyperlinkFormulaInCell()

Query.QueryTable.Refresh

'rest of your code here

End Sub
Gitty
  • 166
  • 8
  • 1
    Thank you that worked, I also added a line to deactivate the background refresh option. Much appreciated! ActiveWorkbook.Connections("query1").OLEDBConnection.BackgroundQuery = False ActiveWorkbook.Connections("query1").Refresh – Adam May 06 '20 at 18:59