Open an excel file and rename the file for a specific cell value

Help
,
1

Hello,

I have an RPA that downloads files from outlook and will save the files to a specific folder. Once the file is downloaded, I need to rename the file based on three cell values. I need to take the value from cell B1 + B6 and use the values in these fields to rename the file.

Could someone provide me with step by step instruction to resolve my issue?

As a side note, I did a lot of research on this issue but I could not find a solution. I was initially trying to use Excel application scope but it required a specific file name…

Thank you,

1 Like
2

Hi @MF.RPA,

  1. Read the downloaded file. It doesn’t matter if it isn’t properly named, you need to inform current name to read cell values. Use Read Cell/Read Range activity informing current file’s name.

  2. Store in a variable the new file name, according to cell’s values.

  3. Use a Copy File activity to create a new file with proper name. Then, you can delete the old one if you want.

Let me know if you need more details

1 Like
3

When I try to use the read activity, I receive an error that says the activity must occur within the Excel application scope activity. In this activity I am required to provide a file name.

image

1 Like
4

Hi @MF.RPA,

Got it…

You’ll need to inform file’s name.

What you can do is get file’s full path based on most recent file in a folder.

After that, use excel application scope to read cell values.

Check this post:

1 Like
5

How does this look?

image
image456×581 36.6 KB

1 Like
6

When using the variable strFilepath and the VB expression Directory.GetFiles(yourfolder_path,“*.txt”).OrderByDescending(Function(d) New FileInfo(d).CreationTime).Tolist(0)

I receive a compiler error, I tried as pasted above and replacing the “youfolder_path,” section with my actual file path. I have also tried adding quotes and still receive a compiler error.

1 Like
7

“.txt” is an example.

You must replace by the extension of your file, such as .csv, .xlsx, etc

1 Like
8

I also changed the extension and I still receive a compiler error, I forgot to mention that part. The error message says that ‘)’ expected. I have tired to add this in a few places but did not resolve the issue.

1 Like
9

ping me on sg2070156@gmail.com

10

Kindly share your project or share the entire compilation error message you mentioned above.

1 Like
11

[quote=“gustavo.cervelin, post:10, topic:270051”]
are the compilation error you mentioned above.
[/quote]

image
image735×264 27.9 KB

1 Like
12

Hi @MF.RPA,

It seems that the double quotes were missing

Instead of …S2_REPORTS\RPA_Downloads use “…S2_REPORTS\RPA_Downloads”

1 Like
13

Thank you for catching that, I will test the script and report back.

1 Like
14

The script seems to work well but I cannot assign values from the search condition
Error
Multiple Assign: Can not assign ‘strSearchConditions.Substring(strSearchConditions.IndexOf(“Cust.=”)+9, 2)’ to ‘strCustomer’.

1 Like
15

Well done @MF.RPA,

I just want to understand better…

Did you manage to get the full path of the most recent file and read the cells?

Was this error before or after that?

1 Like
16

The error was after read range and in the multiple assign area. Now for some reason I am getting an error in read range saying the most recent file does not exist in the folder although it does

1 Like
17

Got it…

How did you enter file’s name in Excel Application Scope?

If you want, share a screenshot.

1 Like
18

Directory.GetFiles(“FILE_FOLDER”,“*.xlsm”).OrderByDescending(Function(d) New FileInfo(d).CreationTime).Tolist(0)

1 Like
19

Now I am finding the workbook however the system is saying the sheet does not exist. For the sheet name I am using “” since the name of the sheet can change on every report. Is there a better way?

1 Like
20

Ok, got it that the sheet name changes…

Do you know if it’s always just one sheet?

Or is there a scenario of having more than one?

You can use “Get Workbook Sheets” activity, it will return a list of all sheets. If there is only one, get its name and read cells.

If there are two or more, let me know

1 Like