VBA code to refresh an Excel table generated by a power query

Disclaimer: I’m not good at Excel VBA. That’s why I take advantage of ChatGPT to generate the code for me.

The requirements

Here are my requirements:

  1. When I hit the button, refresh the table called AgingTable in the Aging sheet
  2. The AgingTable  is connected to a Power Query called the same name: AgingTable
  3. Since the AgingTable query is a big table and runs several transformation, it may take a while for the query to finish
  4. However, before proceeding to the next script in the VBA I want it to wait for the query to complete

ChatGPT code that I modified

When I first asked ChatGPT, here’s the code I got:

    ' Refresh the specific Power Query named "AgingTable"
    ThisWorkbook.Queries("AgingTable").Refresh
    
    ' Wait for the refresh to finish
    Do While ThisWorkbook.Queries("AgingTable").Refreshing
        DoEvents
    Loop

However, this did not work for my case. The query kept on running without end. Here’s how I modified this to make it work:

    ' Refresh the specific Power Query named "AgingTable"
    ThisWorkbook.Sheets("Aging").ListObjects(1).QueryTable.Refresh BackgroundQuery:=False
    
    ' Wait for the refresh to finish
    Do While ThisWorkbook.Sheets("Aging").ListObjects(1).QueryTable.Refreshing
        DoEvents
    Loop

ChatGPT explains

ChatGPT explains this new code:

ThisWorkbook.Sheets("Aging").ListObjects(1).QueryTable.Refresh BackgroundQuery:=False
  1. ThisWorkbook: This refers to the workbook containing the VBA code.
  2. Sheets("Aging"): This refers to a specific worksheet in the workbook named “Aging”. Sheets is a collection of all the sheets in the workbook, and ("Aging") specifies which sheet to access.
  3. ListObjects(1): This refers to the first table (ListObject) on the “Aging” sheet. ListObjects is a collection of all the tables on the sheet, and (1) specifies the first table in the collection. Note that tables are represented as ListObjects in Excel VBA.
  4. QueryTable.Refresh: This method is used to refresh the data query associated with the ListObject. The data query could be a Power Query (also known as Get & Transform in newer Excel versions) or a query from an external data source like a database or web service. By calling Refresh, you’re instructing Excel to fetch the latest data according to the query’s instructions.
  5. BackgroundQuery:=False: This is an optional parameter used to specify whether the refresh should occur in the background or not. When set to False, as in this case, it means that the refresh will happen in the foreground, and Excel will wait until it completes before allowing further code execution. If set to True, the refresh would occur asynchronously in the background, allowing the code to continue executing without waiting for the refresh to finish.

Furthermore:

    Do While ThisWorkbook.Sheets("Aging").ListObjects(1).QueryTable.Refreshing
        DoEvents
    Loop

This code snippet creates a loop that continuously checks whether the query associated with the first ListObject (table) on the “Aging” sheet is currently refreshing. Let’s break it down:

  1. ThisWorkbook: Refers to the workbook containing the VBA code.
  2. Sheets("Aging"): Refers to a specific worksheet in the workbook named “Aging”.
  3. ListObjects(1): Refers to the first table (ListObject) on the “Aging” sheet.
  4. QueryTable.Refreshing: This property returns True if the query associated with the QueryTable (the data retrieval mechanism linked to the ListObject) is currently refreshing. The loop continues to execute as long as this condition is true.
  5. DoEvents: This statement allows the Excel application to process other events, such as user input or other background processes, while the loop is running. This helps prevent Excel from becoming unresponsive during the loop.

Putting it all together, the loop will continue to execute and repeatedly call DoEvents until the query associated with the first ListObject on the “Aging” sheet has finished refreshing. Once the refresh is complete, the loop exits, and the code proceeds to the next line. This loop ensures that the code waits for the refresh to finish before moving on to the next steps.

Leave a Reply