Power Automate - set filename by variable in Add row to Excel action

%3CLINGO-SUB%20id%3D%22lingo-sub-2187105%22%20slang%3D%22en-US%22%3EPower%20Automate%20-%20set%20filename%20by%20variable%20in%20Add%20row%20to%20Excel%20action%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2187105%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20Form%20for%20employees.%20One%20text%20field%20is%20department%20name.%3C%2FP%3E%3CP%3EI%20add%20the%20survey%20row%20into%20a%20particular%20department%20excel%20workbooks%20on%20sharepoint%2C%20i.e.%3C%2FP%3E%3CP%3ECovid-mp.xlsx%3C%2FP%3E%3CP%3ECovid-odsh.xlsx%3C%2FP%3E%3CP%3ENow%20I%20use%20switch%20to%20select%20the%20way%20with%20particular%20excel%20workbook%20and%20I%20select%20the%20workbook%20manually%20in%20the%20folder%20icon%20of%20the%20action%20field%20in%20every%20case.%3C%2FP%3E%3CP%3EBut%20there%20are%20many%20departments%2C%20is%20it%20possible%20to%20set%20the%20excel%20file%20by%20variable%20in%20the%20%22add%20row%20to%20excel%22%20action%3F%3C%2FP%3E%3CP%3ECovid-%3CDEPARTMENT%3E.xlsx%3C%2FDEPARTMENT%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Zdenek_Moravec_0-1614924116260.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F261094iD08E962EF14CCFDC%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Zdenek_Moravec_0-1614924116260.png%22%20alt%3D%22Zdenek_Moravec_0-1614924116260.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EThank%20You%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2187105%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMicrosoft%20Flow%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2187683%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Automate%20-%20set%20filename%20by%20variable%20in%20Add%20row%20to%20Excel%20action%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2187683%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F295513%22%20target%3D%22_blank%22%3E%40Zdenek_Moravec%3C%2FA%3E%26nbsp%3Bno%20it's%20not%20possible%20because%20the%20Add%20a%20row%20into%20a%20table%20action%20won't%20know%20which%20table%20columns%20to%20add%20to%20the%20action%20when%20you%20are%20building%20the%20flow%20so%20you%20will%20always%20get%20an%20error%20when%20you%20try%20to%20save%20it.%20You%20can%20copy%20the%20action%20to%20your%20clipboard%20and%20then%20add%20it%20to%20each%20switch%20case%2C%20but%20you%20will%20need%20to%20change%20the%20file%20in%20each%20case.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERob%3CBR%20%2F%3ELos%20Gallardos%3CBR%20%2F%3E%3CFONT%20size%3D%222%22%20color%3D%22%23006400%22%3EMicrosoft%20Power%20Automate%20Community%20Super%20User%3C%2FFONT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2188725%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Automate%20-%20set%20filename%20by%20variable%20in%20Add%20row%20to%20Excel%20action%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2188725%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F174092%22%20target%3D%22_blank%22%3E%40RobElliott%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOK%2C%20I%20see%20now%2C%20the%20manually%20selected%20file%20in%20the%20field%20%22File%22%20triggers%20the%20list%20of%20tables%20in%20the%20next%20field%20%22Table%22.%20BTW%2C%20the%20option%20Add%20dynamic%20content%20in%20the%20field%20%22File%22%20is%20then%20useless%2C%20isn't%20it%3F%3C%2FP%3E%3CP%3EAnyway%2C%20I%20have%20created%2016%20more%20cases.%3C%2FP%3E%3CP%3ECopy%20the%20action%20is%20a%20good%20idea%2C%20but%20it%20does%20not%20work%20in%20my%20case%20-%20%22Failed%20to%20find%20xxx%20in%20definition%22%20(I%20have%20copied%20the%20Excel%20action%20in%20Case%202%2C%20created%20next%20case%2C%20clicked%20new%20action%20and%20selected%20My%20Clipboard)%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Zdenek_Moravec_1-1614971850472.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F261284iC33E33F35390D30A%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Zdenek_Moravec_1-1614971850472.png%22%20alt%3D%22Zdenek_Moravec_1-1614971850472.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2189406%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Automate%20-%20set%20filename%20by%20variable%20in%20Add%20row%20to%20Excel%20action%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2189406%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F295513%22%20target%3D%22_blank%22%3E%40Zdenek_Moravec%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20might%20be%20too%20late%20if%20you've%20already%20gone%20another%20route%2C%20but%20this%20post%20and%20video%20by%20John%20Liu%20might%20help%20you%20use%20dynamic%20Excel%20file%20names...%20I've%20used%20these%20methods%20to%20add%20rows%20to%20Excel%20files%20which%20have%20a%20different%20name%20on%20each%20flow%20run.%20It's%20a%20little%20advanced%2C%20using%20SharePoint%20http%20requests%2C%20but%20it's%20quite%20cool%20once%20it's%20working!%20%3CBR%20%2F%3EPlease%20see%20%3CA%20href%3D%22https%3A%2F%2Fjohnliu.net%2Fblog%2F2019%2F5%2Fworkarounds-needed-to-use-the-excel-connector-in-microsoft-flow%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fjohnliu.net%2Fblog%2F2019%2F5%2Fworkarounds-needed-to-use-the-excel-connector-in-microsoft-flow%3C%2FA%3E%20and%20%3CA%20href%3D%22https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3D64lSykOYmzI%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3D64lSykOYmzI%3C%2FA%3E%3CBR%20%2F%3EHope%20this%20helps!%3C%2FP%3E%0A%3CP%3ESandy%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2189733%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Automate%20-%20set%20filename%20by%20variable%20in%20Add%20row%20to%20Excel%20action%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2189733%22%20slang%3D%22en-US%22%3EPaste%20the%20action%20outside%20the%20Switch%20case%20then%20drag%20it%20inside.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2190641%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Automate%20-%20set%20filename%20by%20variable%20in%20Add%20row%20to%20Excel%20action%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2190641%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F16657%22%20target%3D%22_blank%22%3E%40Sandy%20Ussia%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20You%20for%20the%20link.%20The%20guide%20is%20really%20very%20advanced%20to%20me.%20I%20will%20stay%20by%20the%20basic%20level%20and%20I%20will%20study%20this%20guide%20once%20I%20get%20more%20advanced%20skills%20in%20power%20automate%20%3B)%3C%2Fimg%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Contributor

 

I have a Form for employees. One text field is department name.

I add the survey row into a particular department excel workbooks on sharepoint, i.e.

Covid-mp.xlsx

Covid-odsh.xlsx

Now I use switch to select the way with particular excel workbook and I select the workbook manually in the folder icon of the action field in every case.

But there are many departments, is it possible to set the excel file by variable in the "add row to excel" action?

Covid-<department>.xlsx

Zdenek_Moravec_0-1614924116260.png

Thank You

6 Replies

@Zdenek_Moravec no it's not possible because the Add a row into a table action won't know which table columns to add to the action when you are building the flow so you will always get an error when you try to save it. You can copy the action to your clipboard and then add it to each switch case, but you will need to change the file in each case.

 

Rob
Los Gallardos
Microsoft Power Automate Community Super User

Hello @RobElliott 

OK, I see now, the manually selected file in the field "File" triggers the list of tables in the next field "Table". BTW, the option Add dynamic content in the field "File" is then useless, isn't it?

Anyway, I have created 16 more cases.

Copy the action is a good idea, but it does not work in my case - "Failed to find xxx in definition" (I have copied the Excel action in Case 2, created next case, clicked new action and selected My Clipboard)

Zdenek_Moravec_1-1614971850472.png

 

 

Hi @Zdenek_Moravec 

I might be too late if you've already gone another route, but this post and video by John Liu might help you use dynamic Excel file names... I've used these methods to add rows to Excel files which have a different name on each flow run. It's a little advanced, using SharePoint http requests, but it's quite cool once it's working!
Please see https://johnliu.net/blog/2019/5/workarounds-needed-to-use-the-excel-connector-in-microsoft-flow and https://www.youtube.com/watch?v=64lSykOYmzI
Hope this helps!

Sandy

Paste the action outside the Switch case then drag it inside.

Hello @Sandy Ussia 

Thank You for the link. The guide is really very advanced to me. I will stay by the basic level and I will study this guide once I get more advanced skills in power automate ;)

@RobElliott, thank You for the magic touch :)