See original post here: https://powerusers.microsoft.com/t5/Building-Flows/Update-macro-enabled-Excel-file/m-p/59123
We cannot do actions such as "insert row", "delete row" etc. on macro-enabled files.
We can try working around by updating files with the "File content" data from an edited Excel file, but since it's not the same format, the target .xlsm file gets corrupted.
Allowing for selecting macro-enabled files and for updating them from the content of non-macro-enabled files would be helpful.
You may well be right with script but I think this will take several years to become the replacement for VBA as Macros are so well entrenched in companies.I have got round this issue by creating a form via excel which auto links the form data as / when submitted and then I have a second excel file which on opening auto macros a copy from the form linked file to a data sheet in the analysis file and then can run say pivot table refresh etc. Hope you find this of interest.
Hi @MikeHod ,
Could you elaborate on that please? Sounds like something I've been trying to do but never found any good tutorials on how this would work, I would need to have Data from e.g. Microsoft Forms or Jotform saved to a list and the same data being used in an Excel template where the data would be filled out in certain cells.
Thanks in advance.
please send connection request on LinkedIn and I will then be able to discuss and explain potential solution.
This is a VERY good idea - cannot believe this wasn't added from the get-go.
Macro's are the backbone of Excel in my opinion.
Friends, I believe we might have some work to do outside of Excel / VBA / OfficeScripts...
With the announcement of Microsoft Lists at Build 2020, I feel like a lot of previous Excel VBA macros handling data will find a worthy successor in that project. From forms to automation, I can't wait to put my hands on that app and test it out.
Microsoft, Please get this to work. It's seems like it should be a basic function of Power Automate
Would greatly increase usefulness of Power Automate for my company's warehouse KPI data collection and reporting needs!!
Reading through the list, it looks like many of the scenarios related to macro can be achieved instead using Office Scripts, which is a TypeScript based script that can be run (like a macro) within the Excel app and also from Power Automate. If you can manage your scenarios with Office Scripts instead of a VBA macro then you cn avoid having to use the macro (and use xlsm) in the first place.
Check out - https://docs.microsoft.com/en-us/office/dev/scripts/develop/power-automate-integration
@sumurthy will it be possible to convert from an excel file to a PDF in Office Scripts?
The reason I'm asking is, I'm trying to feed data from powerapps to excel file table and then take the info from the added rows and put them into a template to be converted to PDF. The only thing that I've found that works well is Microsoft Word to PDF but unfortunately is a premium feature.
@Hunter22 The Word to PDF action is pretty much the only option. Office Scripts can't yet invoke any library as such to perform one off actions like that and it depends on Power Automation for achieving external functions.