Write in excel if cell contains file name from folder

Help Studio
1

Hello,
I have been struggling to figure out how to find out if in cells in excel is file name from folder (without PDF) and if so then write into same row “Approved”.

So far I have READ RANGE with Datatable dt1 and

image
image823×569 13.3 KB

with Output - index

then
If
row(“My column name”).ToString.Equals(“fileInfo”)

Write cell with Range “R” + (index + 2).ToString

It never writes what I need.
Can someone help, please?

2

Hi @HanaO

If row(“Your column name”).ToString.Equals(“YourfileInfo”)
Then
Assign
row(“YourColumn Name”) = “Approved”

Write range the data outside the two loops.

Regards

3

Hi @HanaO
Welcome to our community

  1. Use Excel application scope and read your excel sheet using read range activity and save output as dt…and use read cell activity to read R.
  2. Use for each row to iterate dt.
  3. If row(“Your_column_name”).ToString.Equals(“YourfileInfo”)
  4. Then use assign activity…
    row(“Your_column_name”)=read cell output
  5. After for each row…use write range activity.
    This should work for both the cases I mentioned.
4

Hello @HanaO

I would recommend you to update the datatable first and at the end just write that data table in excel sheet. Using Write cell for each file will slow down the execution.

strPDF_Folder = "C:\Input\PDF Files"
for each row in dtInput.rows
	If system.io.file.exists(path.combine(strPDF_Folder,fl("File Name").ToString & "*") then
		row("Status") = "Approved"
	end if
next row

Write range > dtInput
5

Hello, thank you for suggestions but it is not what I meant.
I have an excel
COLUMN J contains names of files (without .pdf), COLUMN R say OPEN
And what I need is when row in COLUMN J = file name (this file with same name was found in folder) then the same row COLUMN R should be overwritten with APPROVED.

6

Hi @HanaO

Please refer the xaml below that met with your requirement.

FileValidationFromExcel.xaml (11.3 KB)

Regards

7

Thank you very much.
This helped me a lot.

In the end I found the error…in If
row(“My column name”).ToString.Trim.Contains(FileInfo)
I had “FileInfo” and those " " should not be there :slight_smile:

It is working now and will make my life easier.

8

Hi @HanaO

Please Mark it as solution and close the thread.

Happy Automation!

Regards

9

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.