Update macro-enabled Excel file?

I'm trying to build a calendar for my team, where everyone would update a shared Excel file with Flows and specific meeting categories for holiday, training and travel.The data I get in such a way would then be compiled via a VBA macro to get a matrix view of everyone's availability.

I have an annoyance and a double problem:

The annoyance is that I can't seem to be able to directly update a shared Excel file on a team sharepoint site via Flow. Instead, I worked around this by having two Flows: one for updating the file on my OneDrive, and then another for copying the changes to the shared file on the team SharePoint

The double problem is that Flow doesn't seem to be able to handle .xlsm files. I can't update the macro file via the first Flow, since it is not considered "an Excel file", and when I try to update it via the second Flow, the file gets corrupted.

Re: Update macro-enabled Excel file?

It is true that you cannot directly only add/update some rows to excel files that stored in SharePoint libraries. But we do have the action to update the whole file content in sharepoint library. I'm guessing that you are using the below action in the second flow to copy all the updates to the library:

In this action we could get the .xlsm file. But with the Excel-Insert row action, the .xlsm file cannot be found and recognized by flow. And I think this is what you mean by "I can't update the macro file via the first Flow". It seems that this file type cannot be identified by Excel action in flow so far. If you really need this, please consider to post an idea for this requirement so that others can vote on it.

Re: Update macro-enabled Excel file?

It is true that you cannot directly only add/update some rows to excel files that stored in SharePoint libraries. But we do have the action to update the whole file content in sharepoint library. I'm guessing that you are using the below action in the second flow to copy all the updates to the library:

In this action we could get the .xlsm file. But with the Excel-Insert row action, the .xlsm file cannot be found and recognized by flow. And I think this is what you mean by "I can't update the macro file via the first Flow". It seems that this file type cannot be identified by Excel action in flow so far. If you really need this, please consider to post an idea for this requirement so that others can vote on it.

Re: Update macro-enabled Excel file?

That is exactly what I did, but updating the .xlsm file in such a way corrupts it.

I have worked a temporary workaround where I update an Excel file on an O365 Team site, I synchronise the file with OneDrive for Business, and I get the data imported via macro in my macro-enabled file, but that's a real hassle and not what I would expect from automation of tasks

I have created an Idea as suggested, hoping something is done for that. Thanks!

Re: Update macro-enabled Excel file?

I'm trying to build a calendar for my team, where everyone would update a shared Excel file with Flows and specific meeting categories for holiday, training and travel.The data I get in such a way would then be compiled via a VBA macro to get a matrix view of everyone's availability.

I have an annoyance and a double problem:

The annoyance is that I can't seem to be able to directly update a shared Excel file on a team sharepoint site via Flow. Instead, I worked around this by having two Flows: one for updating the file on my OneDrive, and then another for copying the changes to the shared file on the team SharePoint

The double problem is that Flow doesn't seem to be able to handle .xlsm files. I can't update the macro file via the first Flow, since it is not considered "an Excel file", and when I try to update it via the second Flow, the file gets corrupted.