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:
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.
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
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!
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 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.
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.
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.
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?
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: