Read Excel with Script recording and playback

Matthew2 · June 4, 2021, 7:11am

Hi team,

I have an excel with headers and subheaders, like the below:
I have to move to the next header only after making entries for the first set of rows (first heading + subheading) in SAP.

Could you please let me know how to differentiate between the set ? I have attached 2 sets for example.

Matthew2 · June 4, 2021, 8:17am

Please help me this one

GreenTea · June 4, 2021, 8:37am

Hi @Matthew2

I have a solution in mind but need at least 3-5 sets for analysis.

Matthew2 · June 4, 2021, 9:11am

Hi,

I cannot upload my sample, can you help?

Matthew2 · June 4, 2021, 9:13am

Please check this one

Thank you!

GreenTea · June 4, 2021, 10:08am

Hi @Matthew2

Thank for sharing the sets for analysis. There is a repeating pattern for reading the sets.

Use Excel Read Range where Input Range is
first set - A1:K7 or “A1:K” + (CellRowNumber - 2).ToString
second set - A9:K19 or “A” + CellRowNumber.ToString + “:K” + (CellRowNumber - 2).ToString
third set - A21:K27 or “A” + CellRowNumber.ToString + “:K” + (CellRowNumber - 2).ToString
fourth set - A29:K35 or “A” + CellRowNumber.ToString + “:K” + (CellRowNumber - 2).ToString

Where CellRowNumber is 9,21,29,etc. omit 1 because default is 1.

Create a variable to hold a List of Integers: ListOfNumbers = {9,21,29,…} and put inside For Each activity

Inside For Each activity read each item where item is the CellRowNumber and passed the first set, second set, etc.

To get the list of numbers from Excel, you have to insert a new column into Excel - for example column L the excel formula:
=IFERROR(MID(IF(CELL(“contents”,A1)=“Number”,CELL(“address”,A1),“”),FIND(“$”,IF(CELL(“contents”,A1)=“Number”,CELL(“address”,A1),“”),2)+1,5),“”)

Which will give you a column of numbers and blanks - Column L

Filter on Column L to remove blanks.

Copy and the entire column L [shift+ctrl+down arrow], paste Values(V) to new sheet
Transpose

Copy to Notepad and Replace tab to commas

Now you can copy the string into Assign activity ListOfNumbers = {9,21,29,37}

Matthew2 · June 4, 2021, 10:44am

Hi,
But there would be different excel sheets every time with different rows of data under each heading. The sets wouldn’t fall under the same row numbers always.

Are we hardcoding the cell range in the excel read range ?

GreenTea · June 4, 2021, 10:53am

Hi @Matthew2

Yes, it is hardcoded but you did not mention about different Excel sheets. The initial analysis becomes invalid.

Matthew2 · June 4, 2021, 11:05am

I am sorry about that, please forgive me.
This is for a project that needs to run different sets of data in an excel. Each time the data from an excel has been moved to SAP, there would be another excel which needs to be read and insert data into SAP.

Could you please let me know if its possible without hardcoding the range?