Solved

VB6 - Resequenceing the Excel sheets in a specific sequence

Posted on 2014-10-01
4
163 Views
Last Modified: 2014-10-01
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
0
Comment
Question by:Wilder1626
  • 2
  • 2
4 Comments
 
LVL 11

Author Comment

by:Wilder1626
ID: 40356062
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
 
LVL 45

Expert Comment

by:aikimark
ID: 40356154
* 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
 
LVL 45

Accepted Solution

by:
aikimark earned 500 total points
ID: 40356158
If you only have eight worksheets, then sheet-moving performance probably isn't a concern.
0
 
LVL 11

Author Closing Comment

by:Wilder1626
ID: 40356206
Thanks for your help.

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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now