Unanswered

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

(0) Share

Report

Posted on by 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?

Categories:

Power Automate Desktop

I have the same question (0)

All responses (3)

Answers (0)

Sort by

Henrik_M 2,021 Super User 2024 Season 1 on at

Like (1)

Report
Copy link

Link copied!
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:

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.

Create a list variable by looping your %ExcelData% then write this list to Excel.

Was this reply helpful? Yes No
Emil_NOR 3 on at

Like (0)

Report
Copy link

Link copied!

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?

Was this reply helpful? Yes No
Henrik_M 2,021 Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No