Then at B35 there is a list of invoices from that contract. By default, there are 20 rows available for the invoices list, so if there were ten invoices, there will be also ten blank rows and then a Totals row.
However, if there were 30 invoices for that contract, the person responsible will have added 10 extra rows, so all the 20 standard rows.plus 10 extra rows will have info, and then there will be the Totals row.
What I want is to go through each one of these hundreds of sheets in the workbook, copy the info between B35 and the Totals Row (but avoid copying the blank rows when there are less than 20 invoices) and paste it ALL on a single sheet, in a Database friendly format.
@Rogerio_Penna
You could use a read cell(store result in variable, say cellValue), starting from cell B35, and loop it until it finds totals row. And inside the loop should be a If condition “Not string.IsNullOrWhiteSpace(cellValue)”, which will read the cell value only if it is non-empty.
It makes a little sense, as I am not very used to work with Excel on UiPath.
Ok, everything must be inside a single Excel App Scope, right?
do… ther eis a DO box, where I will have several activities in Linear Fashion.
First is a READ cell. From B35.
Then it should write in another Excel file. But maybe it is easier to write in another sheet of the same file.
So it reads from Sheet 545, B35, stores value in variable “VarInVoiceCol1”. It writes into Sheet TableInVoices, B1
Now it must loop. I suppose to make the loop I must go outside Excel App Scope activity and have a variable that increases one by one. So the variable (VarCount) started as 35. Now it increases 1 to 36.
Back to Excel App Scope.
It reads from sheet 545, B36. Except I am not sure how to increase it to 36. The cell field of the ReadCell Activity is written as “B35”. Should it be "B"VarCount ???
Hmmm… I am getting a “read cell faulted” when trying to read B35.
Maybe because it’s a merged cell? However, unmerge the cells would create as much work as just copyng everything, because there are hundreds of sheets.
What is the error you are getting, screenshot would be helpful. Are u trying to read using a read cell?
For the loop, one of the several ways you could do is:
Let us assume, there are 20 records and word “totals” appears in cell A55(=35+20). Read the cell value using “Read Cell” activity, mention starting cell as B36 and store the value in variable, cellRef.
Initialize a variable named say counter=1.
Use a while loop with condition “Not cellRef.Equals(“totals”)”, increment the counter by 1. This way u get the number of records.
Assign variable totalCount= counter, outside the while loop.
Use read range activity and assign range as B36:“B”+totalCount, store it in datatable(say DT). Now u can paste this DT any place u like.
Fixed the Error above. Output of reading Excel cannot be a string, nor an integer, nothing. It must be a GenericValue variable. This is the kind of thing that UiPath should be more intelligent about, instead of requiring user to manually change to a variable type that isn´t even directly listed in the dropdown menu when creating a variable.
Exactly. GenericValue is just not good programming.
However, looking through the posts I looks to me that you are only interested in the TOTAL amount of a sheet. If that is right, you could modify the Excel-sheets to have a total (=SUM) of all invoice lines (however many) at the top of the sheet, say E1.
If you have the total value in E1 on all 600 sheets you only have to loop sheets(number).Cell(“E1”).Value. This is faster and less error prone.
You need, however, to incorporate the sum-cell in all 600 sheets first
I don’t know if this is helpful or even an option for you.
Then you can input as many rows as you want then have the blanks remove with the filter, like I mentioned. If you still don’t like that solution. Another workaround is injecting a macro that counts the rows, read the cell where the result is and then use read range to read the range and finally do what you want to do with that info. There are many ways to do this. Good luck!
the error I am getting (which I posted above) is happening because of the while loop with condition “Not cellRef.Equals(“totals”)
If I use that condition, whenever there is an empty cell for that variable being tested, I get the SystemNullReferenceException error.
Your condition works in sheets where there are no blank rows between B35 and TOTAL row. If there is a blank row before TOTAL, I get that error.
I replaced your condition with Not string.IsNullOrWhiteSpace(VarNum)
This one ends the sequence whenever it finds a blank row.
I get no errors with this condition, HOWEVER, if there are no blank rows, it will keep copying text beyond the invoices areas.
I tried using my condition that works with blank rows and gets no errors WITH your condition (using both AND and ANDELSE) your condition., but as soon as I put your condition. the error returns in case of blank rows (even if I invert…
Not string.IsNullOrWhiteSpace(VarNum) And Not VarNum.Equals(“TOTAL”)
Not VarNum.Equals(“TOTAL”) And Not string.IsNullOrWhiteSpace(VarNum)
both give me the error when finding a blank cell and trying to find “TOTAL” in it.
