Checking for excel sheet names

Learning Hub Academy Feedback
1

I have different random strings (eg. “MI123”), i am supposed to create excel sheets with names like “MI123” but if it exists, i am supposed to create “MI123 (1)” and if it exists, create “MI123 (2)”… and so on.

How am i supposed to find out what is the number inside the () so that i can +1 for the next sheet to be created?

Disclaimer: Not only do i have “MI123”, i have to create “MI123 X1” and “M123 X2”. Same for both X1 and X2, I will have to increment the numbers if (e.g. “M123 (1) X1”) exists, i will have to create “M123 (2) X1”.

Can anybody please help me out? I was thinking of checking number of occurrence so that i can +1 to the number of occurrence. However, I got confused halfway through.

2

Hi,

Here is the VBA code to check whether the sheet exists or not,

Dim Sht As Worksheet
For Each Sht In ThisWorkbook.Worksheets
If Application.Proper(Sht.Name) = Application.Proper(WorksheetName) Then
WorksheetExists = True
Exit Function
End If
Next Sht
WorksheetExists = False
End Function

and to change the sheet name, you can declare a new variable, let say Count.
Change the sheetname as,

If(WorksheetExists = True){
Sht.Name = Sht.Name +“(” + Count.ToString + “)”.
}

Then call the same function, to check whether the name exists or not and repeat the same if exits
Note : Change WorksheetExists to False after the loop exists

Regards,
MR

3

Hi @celeste_ong,

                  if the string format is common then use the string.format("MI123({0})X1",count),then dynamicaly change the value thorugh the loop.
4

Hello! Thank you for replying, i might not fully understand the VBA codes as i am a beginner to UiPath, i do not understand the Application.Proper part, do you mind explaining a little bit more?

Thank you!

5

Hi! Thank you for replying. May i know where do i implement this statement at?

6

You have to import the package Microsoft.Office.Interop.Excel

and Application.Proper is nothing but the sub-classes present in the imported package

You can replace that with
Microsoft.Office.Interop.Excel.Application.Workbooks.Sheets(“SheetName”).

Nothing to worry with vba. It will provide all the available packages while implementing.
Happy Coding :slight_smile:

7

Get all the sheet names first in an array.

Get the first sheet name, match it with each of the other sheet names.

If there is a match , follow below steps:

  1. Use Reg Ex to match the pattern M123 (any Integer 1,2, etc …) and then split it to get the number within brackets.

  2. Add integer one to it.

RegEx something like this : [\w]*[\s]+[(]\d{1}[)]

  1. Replace the matched sheet name with the new name and repeat the steps for all the sheet names.

let me know if this helps

8

@prassin6

Thank you for replying! May i know how am i supposed to get the first sheet name then match it with each of the other sheet names?

Sorry, I am quite new to this…

9

I have imported the package but still there were errors. Sorry, but is it possible for me to visually see the workflow example? I might not catch everything that you have said especially with the Exit function and End function part.

10

Simple way to get the sheet name is use a shortcut key Alt h o r : this will highlight the Sheet Name
Use Ctrl+C to copy the sheet name.

Similarly get each of the other sheet names.

11

But i can just use Variable.GetSheets in an excel application scope too, right? To retrieve the excel sheets.

12

Yes you can Indeed ! :slight_smile: