Obtaining row and column information when data found in Excel

[Short]

Hi all

I’m so beyond stuck on this, it isn’t even funny. Could someone please advise on what to do?

This is what I need my process to do:

Read through an Excel sheet like below

If any of the numbers are above 0 (like in D9), I need to obtain the data in cells:
B9
D9
D4

I then need to write it back the above information to a Main spreadsheet.

The Excel can have any number of rows (this will be ran on 100 spreadsheets so they vary a little), the Columns may run from B to K or B to AE but they’ll all need to be checked for the digits in them. If D9 has a number above 0, D10 or any other cells in row D could also have a number above 0 in it too.

Any help would be massively helpful

Thanks

[postwick (Paul Ostwick)]

You don’t work with Excel using UI automation (ie open Excel, click and type etc). You use the Excel activities. For example, Read Range into a datatable. Find the values you want in the datatable by looping through the datatable rows and columns. Based on the row index and column index, you can then know the row and column that the value was in in the Excel file.

[Short]

Hi @postwick

I’m trying to use Read Range but don’t know how to find what i need to in the DataTable, that’s what i was hoping to get help with. I know not to use UI Automation

[Steven_McKeering]

Hey @Short

Take a look at this build. Should solve your problem.
Short_ReadRangeProblem.zip (13.5 KB)

Input sample (below)
Note orange cells (B4, F6)

Output results (below)
I am capturing the cell number, the value and the filename.

I have added an extra check. See column “Echo” is invalid. This column will be skipped.

I have made the main part of the process into a flowchart so its easier to follow.

Hopefully this helps,

Cheers

Steve

[Short]

Hey @Steven_McKeering

This is absolutely amazing, thank you so much!

I’m having one slight issue though… all the Cells in Column B are numbers so it’s picking up every one. I made a change to ignore Column B but it’s writing back to the Main Spreadsheet like this:

This is how the assign is set up:

So I want the value in the cell with the number in (in my example, 1 because it’s in D9), and what is in B9.

I know you’ve gone to loads of effort already and I appreciate it so much, I’ve been working on this for the past 4 hours without trying to ask for more help but I’m stuck haha

Thanks

[Steven_McKeering]

Hey

So you need the value from column “Attribute” also when column B matches (or similar). Easy.

(I’m away from my computer so will do my best to explain).

1. You need to add an extra column in dtResults called “Attribute”.
2. In the last screenshot, with the black box you need to add/update the assign like this:

Left assign:
strAttribute
Right Assign:
FilterRow(“Attribute”).ToString

This will capture the value of the column named “Attribute” into string.

4. Then at the Add Data Row activity, update the field Add ArrayRow. You need to update the field to look like this:

Before ArrayRow:
{strCell,strValue,Filename}

After ArrayRow:
{strCell,strValue,Filename,strAttribute}

Side note:
If you only need to check Column B in your files, then update the arr_ExcelColumnLetters to just be {“B”}.

Hopefully this helps,

Cheers

Steve

[Short]

Hey @Steven_McKeering

You are the absolute best and I cannot thank you enough, you life saver

[Steven_McKeering]

Glad I could assist

All the best with your project.