PAD. Extracting data from one Excel file into multiple Excel forms.

[Emil_NOR]

Hello,

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%

In Loop:

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? 

[Henrik_M]

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:

1. Using a loop on your %ExcelData% write into Excel, using a row counter variable to shift the write-to row downwards for each loop round.
2. Create a list variable by looping your %ExcelData% then write this list to Excel.

[Emil_NOR]

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? 

[Henrik_M]

I'm still trying to understand your challenge then. Are you struggling with your %ExcelData% containing an empty column?