Extract data from the latest file in SharePoint

[_Teh_]

Greetings,

Is there a way to retrieve the most updated file and retrieve content from it in a Power Automate flow? Currently, Power Automate doesn't support the macro-enabled format (xlsm), so I'm exploring an alternative approach. My idea is to use a macro in Excel to copy and create a new file in excel workbook format (xlsx). Every time I close the macro-enabled file, it will create a new file in xlsx. The new file will always have the same name and will replace the previous version (using the "kill" method in VBA), so that the data in xlsx will always be the latest version. Then, the file is stored in SharePoint.

Since the file is constantly being replaced, the table ID keeps changing. I want to create a flow that can retrieve the table and data from the this file, but I need a method to always access the most updated file. Is there a way to achieve this in Power Automate?

[rzaneti]

Hi @_Teh_ ,

Could you please share the error that you found when you tried to add the .xlsm file to a Power Automate action? I have some flows running that succesfully access .xlsm files to run scripts or copy data:

To capture any modifications in the file, there is a trigger from SharePoint connector called "When an Item or File is modified", which gets changes in lists or document libraries from SP. The inconvenient of this trigger is that it will have to input a folder (and not a file), and it will be capturing changes in any file inside that folder:

To capture only the changes to a specific file inside a folder, you have 2 options: (1) create a folder only to store you Excel file (and no other files); or (2) add a conditional action after the trigger to test if the name of the modified file is relevant for you. 

Let me know if it helps or if you need any additional clarification! 

[_Teh_]

Hi @rzaneti , the error is I couldn't find xlsm file when I try to use List rows in a table. That's why I am creating a new file of xlsx format, instead of deleting and recreating, I now modify my VBA code to update the file. For run the scripts, I am not sure how it works. Everytime I close my xlsm file, the VBA code will help me update the xlsx file. The error I got was the table was not found or removed when I was using List rows in a table. 

[rzaneti]

Hi @_Teh_ ,

Based on your description, I'm thinking that your VBA code may be "replacing" the table of the .xlsx file, which may generate a new "table id" for it. When Power Automate access your table in the "List rows" action, it probably will be using the "old id" and returning an error as it will not find it, even that your "new" table looks the same like the overwrote one.

For now, you can try to temporarily add a "Get tables" action to your flow, before the "List rows", just to identify how Power Automate is reading the tables from your Excel file (if it is reading at all). If it returns your table in the output, you can use this dynamic value as input for your "List rows".

If you are not sure about how to do this, please post an image of the output of the "Get tables" here (removing any sensitive data) and I will be happy to help you with that!

[_Teh_]

Hi @rzaneti, 

Here's the pictures, it somehow different when I try to get the column values for condition control after this looping flow. Even within the loop, I could only get ID, body and value, but within value I couldn't get the column as usual.

[rzaneti · Accepted solution]

Hi @_Teh_ ,

Once your "List rows present in a table" action works properly, you will be able to see your table data in the output of the action, like here:

If you need to filter the table in this action, you will have to use the filter query. The syntax is simple ([column] [expression] [value]) and allow a large range of conditions (equal, not equal grater/lower than...). In this example, note that I'm filtering the column "A" for the values that are equal to "4" (which returns only one row).

You can check this docs for more details about the filter query: https://learn.microsoft.com/en-us/dynamics365/business-central/dev-itpro/webservices/use-filter-expr...

Also, let me know if you need any additional help!

[_Teh_]

Hi @rzaneti , it works when I am using expression to get the variables and the values, thank you so much for helping, really appreciate the effort!