Sheet name sort

Help Studio
1

hello :slight_smile:

How to sort the names of the sheets so that they go from the smallest to the largest number?

image

2

is it about reordering the worksheets or about e.g. getting a sorted String list/Array with Sheetnames?

3

Hi @dvojinovic

Input:
image

Macros Code:

Sub SortSheetsByNumber()
    Dim i As Integer
    Dim j As Integer
    Dim temp As String
    
    ' Bubble sort algorithm to sort sheet names
    
    For i = 1 To Sheets.Count - 1
        For j = i + 1 To Sheets.Count
            If Val(Sheets(j).Name) < Val(Sheets(i).Name) Then
                ' Swap sheet names
                temp = Sheets(i).Name
                Sheets(i).Move after:=Sheets(j)
                Sheets(j).Name = temp
            End If
        Next j
    Next i
End Sub

Use the above code in Invoke VBA activity. Paste the code in a Notepad file and save it. Pass the file path in Code File Path and Pass SortSheetsByNumber as Method Name.

Output:

image
image733Ă—375 3.93 KB

Code Text File:
SortSheets.txt (512 Bytes)

Workflow:

image
image711Ă—991 42 KB

Sequence93.xaml (11.6 KB)

Let me know if you have any queries.

Regards

4

Hi,

If you want to re-order sheet, the following code with InvokeCode will help you.

Using wb As New ClosedXML.Excel.XLWorkbook(filename)
Dim sheets As ClosedXML.Excel.IXLWorksheets = wb.Worksheets
Dim arrSheetName  As String() = sheets.Select(Function(s) s.Name).OrderBy(Function(s) CInt(s)).ToArray()
Dim i As int32 =1
For Each  s As String In arrSheetName
	wb.Worksheet(s).Position=i
	i=i+1
Next
wb.Save()
End Using

image
image1021Ă—572 31.3 KB

note: this assumes all the sheet names consist of numeric characters.

Sample
Sequence.xaml (6.0 KB)

Regards,

5

@dvojinovic

you can follow these steps:

  1. Use the “Excel Application Scope” activity - to open the Excel file.
  2. Use the “Get Sheets” activity - to retrieve the names of all sheets in the Excel file.
  3. Use LINQ (Language Integrated Query) - to sort the sheet names based on numeric values.
  4. Use the “Write Line” activity to display the sorted sheet names for verification.

• Open Excel Application Scope
• Get Sheets, Output - sheets
• Assign sortedSheets = sheets.OrderBy(Function(x) Integer.Parse(Regex.Match(x, “\d+”).Value)).ToList()
- > The regex “\d+” extracts numeric values from sheet names

    Use - For Each (sheet in sortedSheets)
        Excel Rename Sheet (Sheet Name: sheet, New Name: sortedSheets.IndexOf(sheet) + 1)
    End For Each
6

Thank you, it worked :slight_smile:

7

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.