asked on

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

Wilder1626

ASKER

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

aikimark

* 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

ASKER CERTIFIED SOLUTION

aikimark

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Wilder1626

ASKER

Thanks for your help.

I also like your way of doing it. It's good to know.