cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Braven11
Regular Visitor

Renaming files in a loop based on a DataTable

Hello! 🙂

I am working with an excel spreadsheet and a browser for this automation.

In the excel spreadsheet I only have 4 columns:

Name - Year - Hyperlink - concatenate function (company name_year)

What the final result should look like: 

PA loops through each hyperlink in excel

Goes to hyperlink in chrome

Take screenshot of web page and store it as a JPG file
Copy JPG file to a new folder
Rename the copied JPG file according to the concatenate function in excel (Name_year)

I managed to successfully do all steps with the exception of renaming the file - for some reason it throws an unexpected error. 

I should mention that the New File name in the RENAME action is a variable stored as a DataTable (read earlier in the flow). If I make PA read, let's say only cell D3, which contains a concatenated name (e.g. AB-2021) and have the file renamed as according to this variable, it will throw me an error - however if I manually input in the flow "AB-2021" in the "new file name" section instead of the DataTable, then it will successfully rename the file.

I am not sure if I was clear enough with my explanation - any help would be tremendously appreciated, and I am open to further detail or rephrase the above problem.

Many thanks,

1 ACCEPTED SOLUTION

Accepted Solutions

Yes - same problem. 

I managed to solve my problem by doing it "in reverse". My steps right now (working solution):

1 - launch excel
2 -launch chrome
3 - read from excel the column where the desired document name (the concatenate) is stored AS DocumentName
4 - Activate cell in excel worksheet "C1" (hardcoded) - this is the column before the concatenate, which stores the hyperlinks
START LOOP - 5 - For each DocumentName in DocumentName
6 - Activate cell in excel worksheet 1 cell below activated cell (offset) for action #4
7 - Read the value of currently selected cell and store it as HYPERLINK 
8 - trim text of HYPERLINK (this extra step was necessary, for some reason without it it didn't correctly read the hyperlink from excel) - store it as TrimmedHYPERLINK
9 - go to webpage: TrimmedHYPERLINK
10 - take screenshot of webpage and store it into Template.png
11 - copy Template.png into NewFolder, store copy as CopyTemplate
12 - rename CopyTemplate to DocumentName
END LOOP

Basically every time the loop goes through, excel will read the next hyperlink on column C (offset + 1 to bottom of the cell), navigate to webpage, save the webpage print as a JPG file, copy the JPG file into a different folder and rename the copied JPG file there.

Initially I tried running the hyperlinks through a LOOP, while now I am running the DocumentName variable through a LOOP. For some reason I could not rename the file according to a stored DataTable hyperlink (even if I only had 1 hyperlink value in the DataTable).

View solution in original post

4 REPLIES 4
Benny_1857
Helper III
Helper III

Maybe the file path you provide is not correct I guess.

What I did before is use PQ to carry out the path I need.

So when I use file rename it is a little bit trick.

When you get the file path there are several variables carried out in this step.

You need to use 

%Directory%\%FileName%   (I will forget to add back slash which is highlighted between 2 variables)

Benny_1857_0-1642689695067.png

 

MichaelAnnis
Super User
Super User

Have you tried "Get cell contents as text" on the "Read from Excel Worksheet":

MichaelAnnis_0-1642695402230.png

 

 

Hi, this was not the issue. 

Yes - same problem. 

I managed to solve my problem by doing it "in reverse". My steps right now (working solution):

1 - launch excel
2 -launch chrome
3 - read from excel the column where the desired document name (the concatenate) is stored AS DocumentName
4 - Activate cell in excel worksheet "C1" (hardcoded) - this is the column before the concatenate, which stores the hyperlinks
START LOOP - 5 - For each DocumentName in DocumentName
6 - Activate cell in excel worksheet 1 cell below activated cell (offset) for action #4
7 - Read the value of currently selected cell and store it as HYPERLINK 
8 - trim text of HYPERLINK (this extra step was necessary, for some reason without it it didn't correctly read the hyperlink from excel) - store it as TrimmedHYPERLINK
9 - go to webpage: TrimmedHYPERLINK
10 - take screenshot of webpage and store it into Template.png
11 - copy Template.png into NewFolder, store copy as CopyTemplate
12 - rename CopyTemplate to DocumentName
END LOOP

Basically every time the loop goes through, excel will read the next hyperlink on column C (offset + 1 to bottom of the cell), navigate to webpage, save the webpage print as a JPG file, copy the JPG file into a different folder and rename the copied JPG file there.

Initially I tried running the hyperlinks through a LOOP, while now I am running the DocumentName variable through a LOOP. For some reason I could not rename the file according to a stored DataTable hyperlink (even if I only had 1 hyperlink value in the DataTable).

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

Power Automate Designer Feedback_carousel.jpg

Help make Flow Design easier

Are you new to designing flows? What is your biggest struggle with Power Automate Designer? Help us make it more user friendly!

Top Solution Authors
Top Kudoed Authors
Users online (4,287)