ArugmentOutofRangeException

[canlilar]

Hi everyone!

New to Uipath and trying to create a bot that collects a specified group of excel workbooks and combines them into one workbook with many sheets. The problem I am having is finding a way to have each sheet name be unique so that each new sheet that is added does not overwrite the previous one. It would be nice if I could iterate through an array or list of custom names but if that is not possible I’d like to at least have them be “sheet1”, “sheet2”, etc. I tried to accomplish this by using the substring function: “Sheet”+item.substring(-6,1) that would grab the name of workbooks like this “opportunitySearchResults_11-03-2018 (2).xls” and end up with sheet names like sheet1, sheet 2, etc. But i’m getting this strange error message:

Attaching my bot too…
Test import multiple excel files V2.xaml (10.3 KB)

[Manjuts90]

@canlilar u want name sheetname like below right

“SheetopportunitySearchResults_11-03-2018 (2).xls"

instead of doing substring for getting workbook name(or any filename) you can use below function.

Path.GetFileName(item)

it gives you filename with extension. where item is variable which contains path of the file along with file name

[canlilar]

Hi @Manjuts90!

Thanks for your prompt reply. I’m actually looking for sheet names that are a bit shorter and come out to “Sheet1”, “Sheet2”. Actually ideally I’d like to loop through a keyword string list like “apple”, “orange”, “peach” for every sheet that there is. Do you know how I could do that? Every sheet name needs to be unique.

[Manjuts90]

@canlilar Are you asking how to set the sheet names or getting sheet names in a workbook?

[ClaytonM]

Hey.

Here’s one idea. So you can create a Workbook application variable as the output of an Excel Scope for the file. You then can get the list of Sheets on that file with the variable (for example wb.GetSheets)

So using this, you can check that a sheet name already exists.

If wb.GetSheets.Contains("Sheet1")

If you have an array of sheet names you would run them through a For each with a Write Range

For each sh in sheetList
    If Not wb.GetSheets.Contains(sh)
        Write Range to sh

So that is a simple way.

Now, however, if you would like to use one word like “Sheet” with a numerical sequence, then you would create your list possibly using some vb

sheetList = Enumerable.Range(1,10).Select(Function(x) word+x.ToString)

EDIT: I have a mistake on the condition above.
Correction: if Not wb.GetSheets.Contains(sh)
// use .Contains

Regards!

[canlilar]

How to set the sheet names. That second part of the attached query. Where it compiles the workbooks into one workbook with multiple worksheets.

[Manjuts90]

@canlilar ClaytonM suggested better way try it.

[canlilar]

Thanks @ClaytonM! I like the idea but I’m having a hard time implementing. I did my best to follow your instructions for the “simple way” but getting a lot of errors. Would you mind taking a look at the attached bot and provide me with any insights you might have on how I can improve please? it would be much appreciated!

Test import multiple excel files V2.xaml (12.6 KB)

You will also need at one of test excel files that I am trying to merge…

opportunitySearchResults_11-19-2018 (6).xls (62 KB)

[ClaytonM]

No problem. You were close.
—Since you are closing the input workbook, I went ahead and stored the existing sheets into a string array variable.
—also, you put “If Not …” in the condition box. In my psuedocode, “If” was just representing the If activity. However, you can use inline If statements too like If(condition, True statement, False statement) … So I removed the If from the condition box
—additionally, the For each used was looking at the items as objects, so you need to use .ToString, like sh.ToString
—SheetNames should be an array, so it can provide the sheet names to use
—keyword was changed to a string set as “test”, then I adjusted the Assign to store to SheetNames with numbers
—I added a Break in the If activity so once it writes the data to the sheet it can exit the loop and go to next data set / file

I did not spend much time on this and did not test it. Hopefully, it gets you going!
Test import multiple excel files V2 (1).xaml (13.4 KB)

Let me know if there are other problems or errors.

Regards.

[ClaytonM]

I realized that you want to check the existing sheet names in the Output.xlsx file.
I will need to reupload with that adjustment. But basically, you just need to an Excel scope on Output.xlsx if it exists, and store the output to a workbook variable similar to wb1. And, move the Assign of wb1Sheets inside that Excel scope instead. That excel scope should be at the very start.

Essentially, you want the wbSheets array to consist of the sheets in the file that you don’t want to be overwriting the data.

[ClaytonM]

Here is quick adjustment I was talking about to read in sheets from Output file.

Test import multiple excel files V2 (1).xaml (15.3 KB)

There might some other improvements also. For one, there is a more efficient way to choose the Sheet Name, but was just trying to keep it simple for now.

Regards.

[canlilar]

Thank you @ClaytonM! However I am receiving the following error:

I coudn’t find a variable named “source” to change it to not null. Any ideas?

THANK YOU so much for your help!

[ClaytonM]

Source is the activity name (thus why I renamed each activity I added in there)

Looking at that activity here:

Enumerable.Range(1,FileNames.Count+existingSheets.Count).Select(Function(x) sheetWord+x.ToString).ToArray

The error indicates that a value in the above code has no value. Make sure that each variable has something in it initially.

Also make a slight adjustment to add 1 if .count is 0 for both arrays:
Enumerable.Range(1,1+FileNames.Count+existingSheets.Count).Select(Function(x) sheetWord+x.ToString).ToArray

Also make sure you use latest uploaded version that is represented in above image as shown.

Let me know if you still receive errors.

Thanks.

[canlilar]

Thank you @ClaytonM! I’m not getting any error messages anymore, however I’m getting strange results. I have 11 test files I am trying to ready in and only two sheets are showing up in the output.xls file. The first sheet is called sheet1 (which is blank) and the second one is called test1 (which has the correct data). I’ve attached the results. Can’t figure out why this happening. Any ideas?

Output.xlsx (3.2 MB)Test import multiple excel files V4.xaml (15.8 KB)

also adding my latest bot.

[ClaytonM]

Hi @canlilar

I think it is because you are only reading the Sheet names at the start so everytime you add a new sheet it doesn’t get added to the original sheet list. There are a few approaches, but I think it might be better to just change the existingSheets to a type List<Of String>

Then, use the Add to collection, when you add a new sheet:

Here is updated workflow: Test import multiple excel files V4.xaml (16.1 KB)

[canlilar]

Thank you for continued guidance @ClaytonM! After running the latest attached workflow I am receiving the following error:

I tried to resolve by adding “” to the existingsheets variable default value but it’s throwing up a error. Any ideas on how to resolve?

Best,

Eden

[ClaytonM]

Sorry, I was wondering if that would be a problem.

Here is how to initialize it to an empty list:

New List(Of String)(New String() {})

[canlilar]

Ah ha! I would never have guessed that

Thank you for your prompt reply @ClaytonM. You are a very talented bot creator. That fixed the issue!