VB6 - Resequenceing the Excel sheets in a specific sequence

Hi,

This is for my VB6 project.

I'm exporting Multiple MSFlefgrid into Excel on multiple sheets inside the same excel file with below code:
 Dim xlObject As EXCEL.Application
    Dim xlWB As EXCEL.Workbook
    Dim r As Long
    Set xlObject = New EXCEL.Application


    'This Adds a new woorkbook, you could open the workbook from file also
    Set xlWB = xlObject.Workbooks.Add

    Clipboard.clear    'Clear the Clipboard
    With MSHFlexGrid_LOAD_RECAP
        'Select Full Contents (You could also select partial content)
        .Col = 0               'From first column
        .Row = 0               'From first Row (header)
        .ColSel = .Cols - 1    'Select all columns
        .RowSel = .Rows - 1    'Select all rows
        Clipboard.SetText .Clip    'Send to Clipboard

    End With

    With xlObject.ActiveWorkbook.ActiveSheet

        xlObject.ActiveWorkbook.ActiveSheet.Columns("A:V").HorizontalAlignment = xlLeft

        xlObject.ActiveWorkbook.ActiveSheet.Columns("A:V").NumberFormat = "@"

        xlObject.ActiveWorkbook.ActiveSheet.Range("a3") = "Load result:"
        xlObject.ActiveWorkbook.ActiveSheet.Range("a3").Font.Bold = True
        xlObject.ActiveWorkbook.ActiveSheet.Range("B3") = Format(Date, "mmm dd, yyyy")
        xlObject.ActiveWorkbook.ActiveSheet.Range("b3").Font.Bold = True
        xlObject.ActiveWorkbook.ActiveSheet.Range("D5").NumberFormat = "0"


        .Range("A6").Select    'Select Cell A1 (will paste from here, to different cells)
        .Paste   'Paste clipboard content

Open in new window


Once done will all sheets created, i would like to sort all sheets in a specific sequence.

How can i do that ?

Can i edit the code to select all the names sequence to be sorted?

Ex;

If i says sheet3, sheet8, sheet13, sheets2... That would be my sequencing.

Thanks again for your help
LVL 11
Wilder1626Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Wilder1626Author Commented:
I think i found it. I dont know if this is the best way to go, but it works

  xlObject.ActiveWorkbook.Sheets("2. Locations details").Move after:=xlObject.ActiveWorkbook.Sheets("1. Load stops detail")
    xlObject.ActiveWorkbook.Sheets("3. Load result").Move after:=xlObject.ActiveWorkbook.Sheets("2. Locations details")
    xlObject.ActiveWorkbook.Sheets("4. Carrier details").Move after:=xlObject.ActiveWorkbook.Sheets("3. Load result")
    xlObject.ActiveWorkbook.Sheets("5. Rates details").Move after:=xlObject.ActiveWorkbook.Sheets("4. Carrier details")
    xlObject.ActiveWorkbook.Sheets("6. Radial Rates details").Move after:=xlObject.ActiveWorkbook.Sheets("5. Rates details")
    xlObject.ActiveWorkbook.Sheets("7. Resources details").Move after:=xlObject.ActiveWorkbook.Sheets("6. Radial Rates details")
    xlObject.ActiveWorkbook.Sheets("8. Orders details").Move after:=xlObject.ActiveWorkbook.Sheets("7. Resources details")
   ' xlObject.ActiveWorkbook.Sheets("8. Orders details").Move after:=xlObject.ActiveWorkbook.Sheets("8. Orders details")
   
   xlObject.ActiveWorkbook.Sheets("1. Load stops detail").Select

Open in new window

0
aikimarkCommented:
* I think it is the only mechanism you have once the sheets have been created.
* disable screen updating before you do this
* you could look at the worksheets' .Index property and only move those that are out of place
* if you instantiated a workbook variable, you would get cleaner and faster code.
Example
    xlObject.ScreenUpdating = False
    wkb = xlObject.ActiveWorkbook
    wkb.Sheets("2. Locations details").Move after:=wkb.Sheets("1. Load stops detail")
    wkb.Sheets("3. Load result").Move after:=wkb.Sheets("2. Locations details")
    wkb.Sheets("4. Carrier details").Move after:=wkb.Sheets("3. Load result")
    wkb.Sheets("5. Rates details").Move after:=wkb.Sheets("4. Carrier details")
    wkb.Sheets("6. Radial Rates details").Move after:=wkb.Sheets("5. Rates details")
    wkb.Sheets("7. Resources details").Move after:=wkb.Sheets("6. Radial Rates details")
    wkb.Sheets("8. Orders details").Move after:=wkb.Sheets("7. Resources details")
   
    wkb.Sheets("1. Load stops detail").Select
    xlObject.ScreenUpdating = False

Open in new window

0
aikimarkCommented:
If you only have eight worksheets, then sheet-moving performance probably isn't a concern.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Wilder1626Author Commented:
Thanks for your help.

I also like your way of doing it. It's good to know.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.