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

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.