I am trying to extract data from forms filled from one Excel file into several Excel files with a template of the form in Power Automate Desktop.
So far I have done this:
1. Start the Excel file that I want data extracted from. %ExcelInstance%
2. Start Excel file with template I want to use. %ExcelTemplate%
3. Copy cells from %ExcelTemplate%
4. Read from Excel sheet and store into %ExcelData%
5. "For each" loop with %CurrentItem% in %ExcelData%
6. Start Excel, to create an empty Excel file. %ExcelTarget%
7. Paste Template into empty Excel file.
8. Write to Excel using %CurrentItem% as value
With this it creates a new Excel file for each row I am extracting, pasting in the template I want to use and writing in the data. Unfortunately it writes it in one row, while I want it to write the data down column B instead so that it fits the template. Is there a way to make it write the row down a column instead?
First of all: why don't you just fill in the template, then Save As it somewhere once you've written the data? Or, copy the template into a new file, then write the data. Your method seems a bit quirky...
Now for your problem:
Datarows (which your %CurrentItem% is) are always written horizontally into the sheet, and this behavior cannot be changed. There is also no "rotate variable" to quickly change the datarow into a list (which are written vertically)
So you have two options:
Hello Henrik, thanks for your reply.
The forms are originally filled in an app and the results are all collected into an excel spreadsheet, with one row per form. This is fine as a master document, but clunky to read through when going through specific forms. This is why I want to extract each row of data into a template so that I get one form per file.
I am still very new to PAD, how would I go about doing these solutions?
I'm still trying to understand your challenge then. Are you struggling with your %ExcelData% containing an empty column?
Keep up to date with current events and community announcements in the Power Automate community.
A great place where you can stay up to date with community calls and interact with the speakers.
Check out the latest Community Blog from the community!