Update Web Query On File Open Excel VBA Macro

In a previous post we saw how to import data from a webpage into a web query table in Excel using VBA macros. In this post, we see how to update or refresh an existing web query table when the file opens. The macro updates the COVID numbers for different countries as per the table in the worldometers website every time the file is open. That could be changed to update the number when a button is clicked, or using any other trigger.

Macro/VBA code:

 

  [In standard module: Module1]

  Sub UpdateWebQueryTable()

 

      Dim qtCount As Integer

      qtCount = ActiveSheet.QueryTables.Count

 

      If qtCount = 1 Then

          ActiveSheet.QueryTables(1).Refresh

      Else

          'AddWebQueryTable

      End If

 

  End Sub

 

  [In ThisWorkbook module]

  Private Sub Workbook_Open()

      UpdateWebQueryTable

  End Sub 

 

Macro explained:

• In a standard module (Module1 for example), we insert a Sub procedure: UpdateWebQueryTable
• We declare an integer variable to store the count of queries (qtCount), and in the next line we assign the count of query tables in the active sheet to it.
• Then we check whether there is an existing query table in the active sheet or not. If so, we use the Refresh method to update the results in that query. Otherwise (Else), we call other macro to add the query table. This was covered in this other post here. You just need to change the website to get the COVID numbers with the following URL: https://www.worldometers.info/coronavirus
• Finally, we insert an event procedure in the module ThisWorkbook by selecting “Workbook” on the left panel above the module window, and the Open event in the right panel. Then we add the name of the macro we want to run on file open (UpdateWebQueryTable in this example).
• Alternatively, we could initially set the web query to automatically refresh on file open using the RefreshOnFileOpen method when inserting the query table in the first place (qt.RefreshOnFileOpen = True).
• We may not want to refresh every time we open the file. In such case, we can use any other trigger or add a button to update the query table when needed.

This is how we update a web query table on file open in Excel with VBA macros.

Other examples:

• How to Insert Picture From Google Drive with Excel VBA macros
  
• How to AutoFill Table With SpecialCells with Excel VBA macros
  
• How to Resize All Charts In Worksheet with Excel VBA macros