Processing - Excel XLSM Files Issues

Like (2) Share

Report

Posted on 11 Apr 2025 18:02:50 by CU08042159-0

Hello, Spent over 30 Hours No Lock. Any help would be appreciated!

Environment:

Power Automate Cloud

SharePoint Online

I have bunch of Microsoft Excel files that are XLSM (Macro-Enabled) files. I am trying to read XLSM file, Extract Data from a Hidden Sheet called ExportData and then Append that Data to an Excel file that is standard XLSX file. XLSM file's, ExportData sheet does not have table. However; all files and their structure is consistent.

This step is failing for me. It seems that Excel Online connecter does not have action that can read data from XLSX. I cannot modify source files. I am stuck because without this operation a lot of what can be automated cannot be done.

Any ideas?

Categories:

Process Advisor

All responses (4)

Answers (1)

Suggested answer

takolota1 4,859 Super User 2025 Season 1 on 13 Apr 2025 at 23:25:34

Like (0)

Report

Processing - Excel XLSM Files Issues

Here is an Office Script template for getting non-formatted table data from an Excel sheet (works on xlsm files too)

https://community.powerplatform.com/galleries/gallery-posts/?postid=9f7a7bcf-c88f-ef11-ac20-7c1e525bd67d
Suggested answer

rzaneti 3,984 Super User 2025 Season 1 on 13 Apr 2025 at 17:29:46

Like (1)

Report

Processing - Excel XLSM Files Issues

Hi @CU08042159-0,

When working with Excel tasks not related to tables in Power Automate, you should use Graph API or an Office Script. I usually prefer to go with the second option, which is kind of a VBA for Excel on the Web, and integrates easily with Power Automate. The downside: the Scripts must be written in TypeScript, a programming language. Also, a few companies restrict the access to this feature, so please double check if this approach complies to the organization's policy before implementing it :)

For your case, you would need two scripts: one for extracting the data from xlsm, and other to load it to xlsx. The first one could extract the Worksheet object or only its value (we would need to test it to see what works better), while the second would simply create a new sheet in the xlsx and paste the data or replicate the same characteristics from the Worksheet object extracted from xlsm.

I'm also sharing some contents about Office Scripts, so you can evaluate if it works to your case:

- Blog: Office Scripts: the new VBA - https://digitalmill.net/2023/06/10/office-scripts-the-new-vba/

- Blog: Get started with Office Scripts - https://digitalmill.net/2023/06/19/get-started-with-office-scripts/

- Video: Writing data into Excel ranges (outside of tables) with Power Automate and Office Scripts - https://youtu.be/-OGYQEQSP-0

- Blog: Sending values from Power Automate to Excel with Office Scripts - https://digitalmill.net/2024/01/17/sending-values-from-power-automate-to-excel-with-office-scripts/

- Blog: Managing worksheets in Office Scripts - https://digitalmill.net/2025/02/03/managing-worksheets-in-office-scripts/

If this approach makes sense for you, I will be happy in providing you with a more detailed step-by-step on how to implement these scripts to your use case.

Let me know if it works for you or if you need any additional help!

✅ If this solved your issue, please mark it as Accepted Answer.
👍 If it helped, feel free to give it a like!

🌐 Explore more Power Platform content on my Website or on my ▶️ YouTube
💼 Find me on LinkedIn
CU08042159-0 9 on 12 Apr 2025 at 04:26:14

Like (0)

Report

Processing - Excel XLSM Files Issues

@Michael E. Gernaey

Thanks for the info. Apologies for the typo. I have read all the the links and while I understand, I am still experiencing the challenge. It's the XLSM files. I am able to Get Files, Get Files Content in Power Automation - No Problems there. My issues comes when I need to read data from a sheet within XLSM file that is hidden and also the sheet does not have a table defined. My understanding is:

1- Excel Online For Business Connector does not have access to Hidden Sheets.

If there is a work around, that would be awesome. I am running out of ideas. All I want to is extract data from ExportData Sheet that is hidden in a Excel file that is of type XLSM.

Thanks in advance!
Verified answer

Michael E. Gernaey 41,874 Super User 2025 Season 1 on 11 Apr 2025 at 20:35:32

Like (1)

Report

Processing - Excel XLSM Files Issues

Hi @CU08042159-0

You said your files are XSLM then asked if it could read XLSX.

Its not so much that they cannot completely read them its more they cannot execute the scripts. Also even if it was a standard XLSX, it would require a table, or execute a script to return data, or Graph API to read it.

Here are some good reads though

Use macro-enabled files in Power Automate flows - Office Scripts | Microsoft Learn

Extract information in Excel using Power Automate - Azure AI services | Microsoft Learn <== newer

Use Graph Api in Power Automate to read (very)large Excel files from SharePoint | by Jean Paul Dosher | Medium

How to Get Worksheets from an Excel XLSX Document in Power Automate | by Cloudmersive | Medium

If these help you, i'd appreciate if you Marked the answer as such and maybe a like.

If you ahve further questions, please let us know.

Cheers,

Community site session details

Processing - Excel XLSM Files Issues

Helpful resources

News and Announcements

Quick Links

Understanding Microsoft Agents - Introductory Session

Warren Belz – Community Spotlight

Congratulations to the April Top 10 Community Stars!

Leaderboard > Power Automate - General Discussion

Featured topics

Product updates

Community site session details

Processing - Excel XLSM Files Issues

Helpful resources

News and Announcements

Quick Links

Understanding Microsoft Agents - Introductory Session

Warren Belz – Community Spotlight

Congratulations to the April Top 10 Community Stars!

Subscribe to this forum!

Select categories

Leaderboard > Power Automate - General Discussion

Featured topics

Product updates