Power Query Python (Openpyxl) Refresh on Open

Hernysmath 

Accessing an Excel workbook with openpyxl does not trigger the 'Refresh data when opening the file' property because openpyxl does not open the file in the traditional sense. It reads and writes the file without invoking Excel's built-in functions or triggering events like refreshing data connections.

To ensure that your Power Query connection refreshes each time the script runs, you need a method that actually opens the file in Excel and triggers the refresh. Here are a few approaches to achieve this:

Method 1: Use Excel's COM Interface with Python

You can use the win32com.client library to open the Excel application, refresh the workbook, and then save and close it. This approach will trigger the 'Refresh data when opening the file' property.

Example:

Python Code is untested bachup your file and data.

import win32com.client

def refresh_excel_workbook(file_path):
    # Open Excel application
    excel_app = win32com.client.Dispatch("Excel.Application")
    excel_app.Visible = False  # Keep Excel application invisible
    
    # Open the workbook
    workbook = excel_app.Workbooks.Open(file_path)
    
    # Refresh all data connections
    workbook.RefreshAll()
    
    # Wait until refresh is complete
    excel_app.CalculateUntilAsyncQueriesDone()
    
    # Save and close the workbook
    workbook.Save()
    workbook.Close(False)
    
    # Quit Excel application
    excel_app.Quit()

# Path to your Excel workbook
file_path = "path_to_your_workbook.xlsx"
refresh_excel_workbook(file_path)

Method 2: Use Windows Task Scheduler

You can set up a task in Windows Task Scheduler to open the workbook at specified intervals. This will open Excel, which will then refresh the data according to the 'Refresh data when opening the file' setting.

1. Create a VBA Macro to Refresh and Save the Workbook:

• Open Excel and press Alt + F11 to open the VBA editor.
• Insert a new module and add the following code:

Vba Code

Sub AutoRefresh()
    ThisWorkbook.RefreshAll
    Application.Wait (Now + TimeValue("00:00:30")) ' Wait for 30 seconds for the refresh to complete
    ThisWorkbook.Save
    Application.Quit
End Sub

• Save the workbook as a macro-enabled file (.xlsm).

2. Create a Task in Windows Task Scheduler:

• Open Task Scheduler and create a new task.
• Set the trigger to your desired schedule.
• In the actions tab, set the action to "Start a program".
• In the program/script box, enter the path to Excel (e.g., C:\Program Files\Microsoft Office\root\Office16\EXCEL.EXE).
• In the "Add arguments" box, enter the path to your workbook (e.g., "C:\path_to_your_workbook.xlsm").
• Save the task.

Method 3: Use Power Automate

If you have access to Power Automate (formerly Microsoft Flow), you can create a flow that opens the Excel file and refreshes it. Power Automate can handle the automation in a cloud environment, ensuring your data is always up to date.

1. Create a Flow:
• Go to Power Automate and create a new flow.
• Set a trigger, such as a schedule or an event in another application.
• Add an action to "Run a script" in Excel for the web, where you can specify a script that refreshes the data connections.

By using one of these methods, you can ensure that your Power Query data is refreshed every time your script runs, or on a regular schedule, without manually opening the Excel file.

NOTE: My knowledge about Power Query Python is very limited, but since no one has answered it for at least one day or more, I entered your question in AI too. Maybe it will help you further in your project, if not please just ignore it.

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and Like it!

This will help all forum participants.