Hello,
How to get the last sheet created in excel ?
I used GetSheets.Last, and it work but he found me the last sheet what it is in queued, it’s right, but if my last sheet doesn’t follow the order of the queued for example it is in the middle, the function GetSheets.Last doesn’t work.
Exist some function where the robot can found or understand which sheet has been created for last ?
thank you very much
best regards
I don’t think there is any metadata available for individual sheets to determine if it was created last.
There’s not a way to access that directly through UiPath. There’s a couple ways you could go about it though.
First way: Save the sheet names as an array using Workbook.GetSheets. Then later on when you want to find new sheets, use the Workbook.GetSheets method again and check each item in the array against the one you saved earlier.
Second way: Use VBA/macro within excel to find it. This means the workbook needs to be macro enabled, or saved into the robots personal macro workbook and called from there. Here is the code you’d need to achieve it, assuming you save this code within the workbook that you want to find the last added sheet. It’ll need to be modified a bit if you save to the robot’s personal workbook:
Option Explicit
Sub GetLastSheet()
Dim lastAddedSheet As Worksheet
Dim ws As Worksheet
With ThisWorkbook
Set lastAddedSheet = .Sheets(1)
For Each ws In .Sheets
If Val(Mid(ws.CodeName, 6)) > Val(Mid(lastAddedSheet.CodeName, 6)) Then
Set lastAddedSheet = ws
End If
Next ws
End With
End Sub
This code also assumes that you aren’t manipulating the worksheet name property in the VBE - the worksheet name as it shows in the workbook can still be named anything you’d like.
I was testing this same code (google master), but I’m not sure it works cause when I ran it it showed the last sheet in the order rather than the last sheet I created.
Also, you don’t need to have a macro-enabled file as you can use the Invoke VBA activity and call this function in a .vbs file:
Maybe I did it wrong though.
Regards.
didn’t actually test it, just removed the msgbox - seemed like it should be working though when I looked through it? Also, looking at it again (since you noted it wasn’t working properly), it’d be limited to workbooks with only 9 sheets in it, since the val(mid()) is only looking at the 6th digit - if you have double digits it’d have to be modified a bit more to check if 6 + 7 are both numbers, and if so, check that. If not, only check the 6th
Also, good tip on the .vbs, I didn’t realize that!
Sorry, can you rewrite the answer because i don’t understand if it was a question mark or a type, please
thank you very much for your help
thanky very much for your help, now i try it.
Thanks
I think the Mid() takes everything from the 6th digit and on, so it’s Sheet10+, it would still work.
Also, I outputted the CodeNames for each of the sheets in my file.
My file I had Sheet1, Sheet2, and Sheet3 in that order, so I created Sheet4 between Sheet1 and Sheet2. So the order was then Sheet1, Sheet4, Sheet2, Sheet3. When I outputted each CodeName, it showed it as Sheet1, Sheet2, Sheet3, Sheet4, therefore Sheet3 was lastcreated even though it wasn’t.
I’m still not sure if this works, but I see what it is trying to do.
@ClaytonM I gave it a try and it is working as expected? I’m wondering if your test didn’t work because it is using ThisWorkbook, which means that it references the workbook the code is contained within. That’s Why I mentioned it’d have to be within the macro-enabled workbook when executed or it’d need some modifications to run.
@viorica_marga I made some modifications so it will check for the last added up to 99 worksheets. Here is the VBA code.
Option Explicit
Sub GetLastSheet()
Dim lastAddedSheet As Worksheet
Dim ws As Worksheet
With ThisWorkbook
Set lastAddedSheet = .Sheets(1)
For Each ws In .Sheets
On Error Resume Next
If Val(Mid(ws.CodeName, 6, 2)) > Val(Mid(lastAddedSheet.CodeName, 6, 2)) Then
Set lastAddedSheet = ws
End If
If Val(Mid(ws.CodeName, 6, 2)) > Val(Mid(lastAddedSheet.CodeName, 6)) Then
Set lastAddedSheet = ws
End If
If Val(Mid(ws.CodeName, 6)) > Val(Mid(lastAddedSheet.CodeName, 6)) Then
Set lastAddedSheet = ws
End If
On Error GoTo 0
Next ws
End With
End Sub
Thank you very much.
I figured out why it wasn’t working. Somehow when I chose “Insert” between two sheets, the CodeName did not set correctly for it. So still not sure how reliable CodeName is, but it did seem to work.
Note: you can see the CodeName by choosing “View Code”
Also,
Mid("Sheet10", 6) will return 10 so there’s no need to take 2 digits like in your edit:
Mid("Sheet100", 6) will also return 100.
Additionally, I still recommend using Invoke VBA with a .vbs file.
The .vbs file will look like this:
Function GetLastCreatedSheet()
Dim lastAddedSheet As Worksheet
Dim oneSheet As Worksheet
Set lastAddedSheet = Worksheets(1)
For Each oneSheet In Worksheets
If Val(Mid(oneSheet.CodeName, 6)) > Val(Mid(lastAddedSheet.CodeName, 6)) Then
Set lastAddedSheet = oneSheet
End If
Next oneSheet
GetLastCreatedSheet = lastAddedSheet.Name
End Function
Then, in your Workflow, it will look like this:
That’s the way I would do it, but either way should work.
Regards. @Viorica_Marga
Now I provide, thank you very much, you are too kind.
Regards
Thanks,
Regards 
unfortunately doesn’t work 
he take me always the sheet what it is to the beginning …in the position 1
Hi.
If you go to “View Code”, you can see the order that it should pick it in.
The sheet it will choose should be the last one listed with the largest number next to Sheet, while the Sheet name is in parenthesis.
It’s possible that the CodeName is not matching up with the Sheet you intended to choose, and thus not reliable in your case.
Regards.
maybe , thus not reliable
thank you for your time
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.
ashokkarale
MD_Farhan1
Ajay_Mishra
postwick
Dheerendra_vishwakarma
Anil_G
chandreshsinh.jadeja
Gautham_Pattabiraman
vrdabberu
aravindbalineni123