Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

VB6 - Resequenceing the Excel sheets in a specific sequence

Posted on 2014-10-01
4
Medium Priority
?
170 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 46

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 46

Accepted Solution

by:
aikimark earned 2000 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
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 Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

564 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