Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

VB6 - Resequenceing the Excel sheets in a specific sequence

Posted on 2014-10-01
4
165 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
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…
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…
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…

861 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