Rearrange the sheet order in excel file

[koneruupendra111]

Hi,

I’m trying to arrange excel sheets in customised order like (Queries, Datapipeline Extract, Schema Table Extract, Stored Proc Extract, StepFunction, Glue jobs).
I’m not getting in same order when i ran process each time.

Please help me on this!

Thanks,
Upendra

[Anil_G (Anil Gorthi)]

@koneruupendra111

if you are creating the sheets then create them in same order it should retain

if not after creating we need to use vba again…

cheers

[ashokkarale]

@koneruupendra111 ,

Use this code in invoke code activity and customize as per your requirement.

Dim xlApp As Microsoft.Office.Interop.Excel._Application = Nothing 
Dim xlWorkBooks As Microsoft.Office.Interop.Excel.Workbooks=Nothing 
Dim xlWorkBook As Microsoft.Office.Interop.Excel.Workbook=Nothing 
Dim xlWorkSheet As Microsoft.Office.Interop.Excel.Worksheet=Nothing 
Dim xlWorkSheets As Microsoft.Office.Interop.Excel.Sheets=Nothing 
xlApp=New Microsoft.Office.Interop.Excel.ApplicationClass 
xlApp.DisplayAlerts=False 
xlWorkBooks=xlApp.Workbooks 
xlWorkBook=xlWorkBooks.Open(filepath) 
xlApp.Visible = True 
xlWorkSheets=xlWorkBook.Sheets 
xlWorkSheet= CType(xlWorkSheets(sheetname),Microsoft.Office.Interop.Excel.Worksheet) 
xlWorkSheet.Move(Before:=xlWorkBook.Worksheets(1))

Thanks,
Ashok

[vrdabberu (Varunraj Dabberu)]

Hi @koneruupendra111

Input:

VBA Macros Code:

Sub ArrangeSheets()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim sheetOrder As Variant
    Dim i As Integer
    
    ' Define the custom order of sheet names
    sheetOrder = Array("Queries", "Datapipeline Extract", "Schema Table Extract", _
                       "Stored Proc Extract", "StepFunction", "Glue jobs")
    
    ' Open the active workbook
    Set wb = ActiveWorkbook
    
    ' Loop through the custom order
    For i = LBound(sheetOrder) To UBound(sheetOrder)
        ' Check if the sheet exists
        On Error Resume Next
        Set ws = wb.Sheets(sheetOrder(i))
        On Error GoTo 0
        
        ' If the sheet exists, move it to the desired position
        If Not ws Is Nothing Then
            ws.Move Before:=wb.Sheets(i + 1)
        End If
    Next i
    
    ' Activate the first sheet
    wb.Sheets(1).Activate
End Sub

Output:

Text File Path:
Re Arrange Sheets Macros.txt (892 Bytes)

Workflow:

Sequence8.xaml (9.9 KB)

Regards