How to Automatically Update Excel from a Power BI (Fabric) Model with New Data and Workspaces

In my opion, Do not rotate workspaces if you want Excel to update itself. Stabilize the semantic model endpoint, then schedule refresh of the workbook.

Here is a practical setup that works in enterprises.

1. Stop recreating workspaces
   • Keep one “Prod” workspace.
   • Replace the semantic model in place. Use Fabric Deployment Pipelines or a release script.
   • This preserves the dataset GUID. Excel points to a stable GUID, so no relink.

2. Use a service account, not VBA-embedded creds
   • Create a dedicated AAD user with the right licenses and MFA via Conditional Access exception or authentication strength that supports unattended flows.
   • Grant Viewer + Build on the workspace, or direct permission on the semantic model.
   • Do not store secrets in VBA or in the workbook.

3. Store the workbook in SharePoint or OneDrive
   • Excel Online supports unattended refresh via Office Scripts and Power Automate.
   • Keep the workbook connection as “From Power BI semantic model” so it uses your tenant’s Analysis Services endpoint.

4. Schedule background refresh of Excel
   Use Power Automate, not VBA. Flow outline:
   • Trigger: Recurrence.
   • Action: Run script on workbook (Office Scripts).
   • Script does two things: workbook.refreshAll(); then workbook.save().
   • Connection identity is the service account behind the Power Automate Excel connector. No password in code.

Office Scripts snippet (paste in Automate tab in Excel Online):

function main(workbook: ExcelScript.Workbook) {
  // Refresh all data connections and PivotTables
  workbook.refreshAllDataConnections();
  // Some tenants need both calls
  workbook.refreshAll();
  // Save after refresh so downstream users get fresh cache
  // Saved automatically at end of script in Excel Online, but call setDirty to force save
  workbook.getApplication().calculate(ExcelScript.CalculationType.full);
}

Notes
• This refreshes the Excel view of the Power BI model. The model itself still needs its own refresh schedule in Fabric.
• Keep the Fabric semantic model refresh on a separate schedule using Power BI service or REST API. Use a service principal or the same service account.

If you must keep creating new workspaces and new models
You need an indirection layer so the Excel connection never changes.

Option A, “Hub model” in a stable workspace
• Create a thin composite model in a permanent “Hub” workspace.
• The thin model uses DirectQuery for AS to a “current” semantic model.
• When a new workspace and model show up, run an XMLA or TMSL “alter” to point the thin model to the new target.
• Excel stays bound to the thin model’s GUID, which never changes.
Operational steps:

1. Create thin model once. Publish to Hub workspace.

2. Grant Build to consumers and the service account.

3. After each ingestion, a small job runs:
   • Finds the newest semantic model by name pattern via Power BI REST Admin APIs.
   • Issues an XMLA TMSL Alter to update the remote server/database for the thin model’s DirectQuery-for-AS source.

4. Excel refresh picks up the new backend automatically.

Option B, programmatically retarget the Excel connection
• Last resort. Use a script to overwrite the connection string in the workbook to the new dataset GUID, then refresh.
• Do this from a secure process, not from user VBA.
• Pattern:

1. Azure Function with service principal calls Power BI REST APIs to resolve the dataset GUID by name.

2. The function returns only the GUID to a flow.

3. The flow runs an Office Script that edits the OLAP connection to use the new GUID, then refreshes and saves.
   Caveat: Office Scripts does not expose OLAP connection editing in all tenants. If blocked, use the thin “Hub model” approach instead.

Credential handling, best practice

• Use a service account or service principal wherever an API is involved.
• Store secrets in Azure Key Vault.
• In Power Automate, use a custom connector or HTTP action with Managed Identity when possible.
• Never place client secrets in workbook cells, Names, or VBA modules.

Security and governance checklist

• Workspace access: Viewer for consumers, Contributor for automation, Admin for release.
• Build permission on the semantic model for Excel users.
• Endorse the model, add a friendly description, and pin it to OneLake Data Hub for discoverability.
• Turn on sensitivity labels if needed, they flow to Excel.