Answered

Renaming files in a loop based on a DataTable

(0) Share

Report

Posted on by Braven11

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,

Categories:

Power Automate Desktop

I have the same question (0)

All responses (4)

Answers (1)

Benny_1857 124 on at

Like (0)

Report
Copy link

Link copied!

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)

Was this reply helpful? Yes No
MichaelAnnis 5,727 Moderator on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
Braven11 14 on at

Like (0)

Report
Copy link

Link copied!

Hi, this was not the issue.

Was this reply helpful? Yes No
Verified answer

Braven11 14 on at

Like (0)

Report
Copy link

Link copied!

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).

Was this reply helpful? Yes No