cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Emil_NOR
Frequent Visitor

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

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? 

3 REPLIES 3
Henrik_M
Super User
Super User

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.

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
Super User
Super User

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

Helpful resources

Announcements
Power Automate News & Announcements

Power Automate News & Announcements

Keep up to date with current events and community announcements in the Power Automate community.

Community Calls Conversations

Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Automate Community Blog

Power Automate Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (5,438)