How can I automatically delete data connections in Excel ONLINE (Sharepoint)?

[alee023]

I have created an Excel workbook in Sharepoint with PivotTables with a PowerBI connection (a Lakehouse in Fabric). Since the data itself gets updated monthly, I'd like a copy of the Excel without the connections in Sharepoint - so a non-refreshing copy of the Excel. 

I found that deleting the connections is only possible in the app. I want to do it entirely online because I'd like to automate the copying file and deleting connection process. I know auto-deleting connections is possible with a VBA script but I believe those cannot run in Sharepoint unless someone opens the file in the app. I'd like the connections deleted before the file is even opened by anyone in the app - preferably after the copy is created. Or if this is possible through VBA in Sharepoint and I can automate it, I'd like to hear it :)!

One thing I've researched is Office Scripts, which seems to be Javascript-like code. Is creating an Office Script to delete connections entirely online possible in the first place (since I cannot manually delete them online)? 

Thank you!

[#2]

@alee023 

Directly deleting data connections in Excel Online (SharePoint) without opening the file in the Excel app or using VBA scripts isn't supported…so far I know at the time. However, you can consider a workaround using Microsoft Power Automate (formerly known as Microsoft Flow) to achieve a similar result.

Here's a high-level approach:

1. Create a Flow: Use Microsoft Power Automate to create a flow that triggers when a new file is created or modified in your SharePoint library.
2. Add an Action: Within the flow, add an action to make a copy of the Excel file to a different location in SharePoint.
3. Modify the Copied File: After copying the file, add another action in the flow to modify the copied file. You can utilize the "Update file properties" action or the "Send an HTTP request to SharePoint" action to update the metadata of the copied file, which could include removing the data connections.
4. Implement the Logic: To remove the data connections, you might need to manipulate the XML structure of the Excel file, which is quite complex and might not be straightforward. This step might require custom code or advanced knowledge of the Excel file format.
5. Testing and Deployment: Once the flow is configured, test it thoroughly to ensure that it copies the file and removes the data connections as expected. Then deploy it to your SharePoint environment.

Regarding Office Scripts, while they allow you to automate tasks in Excel Online, they have limitations compared to VBA, and currently, there isn't direct support for manipulating data connections. However, you can explore if there are any new updates or features added to Office Scripts that might support your requirement.

Keep in mind that Microsoft regularly updates its products and services, so it's always a good idea to check the latest documentation and announcements for any new features or improvements that might align with your needs. The text, steps and formulas was created with the help of AI.

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.

[#3]

@alee023 

To my knowledge we can't remove connection with Office Script. All workbook methods are here ExcelScript.Workbook interface - Office Scripts | Microsoft Learn

[#4]

@NikolinoDE 

Could you please clarify how this option

 "Send an HTTP request to SharePoint" action to update the metadata of the copied file, which could include removing the data connections.

exactly works

[#5]

You're correct, and I apologize for the confusion. Directly modifying the content of an Excel file, such as removing data connections, isn't feasible through Power Automate or SharePoint's REST API alone.