asked on

excel sheet order at run time.

How to reorder excel sheets in vb.net, I've seen some examples on the net but I can't get the 'after' property set. I can add multiple sheets(45) but the order is from last to first, and I need it from first to last.
I've borrowed the code from the net.

Dim objApp As Excel.Application
        Dim objBook As Excel._Workbook
        Dim objBooks As Excel.Workbooks
        Dim objSheets As Excel.Sheets
        Dim objSheet As Excel._Worksheet
        Dim objSheet2 As Excel._Worksheet
        Dim range As Excel.Range
        Dim rangeSheet2 As Excel.Range


        Dim strExcelDataArray As String(,)
        Dim strExcelDataArray2 As String(,)
        Dim misValue As Object = Reflection.Missing.Value

      
        Try
          
            ' Create a new instance of Excel and start a new workbook.
            objApp = New Excel.Application()
            objBooks = objApp.Workbooks
            objBook = objBooks.Add(misValue)
         [b][u] objBook.Sheets.Add(, , strListOfPartNumberPrefixes.Length) [/u][/b]
            objSheets = objBook.Worksheets
            objSheet = objSheets("Sheet1")

Open in new window

when I courser over 'objBook.Sheets.Add(, , strListOfPartNumberPrefixes.Length)' it indicates the the second parameter is the after setting but I'm not sure what to insert.

Thanks in advance.
Dave

Norie

Dave

Don't you have strListOfPartNumberPrefixes.Length as the 3rd argument in the Add method in that code?

The 3rd argument for Sheets.Add is Count, i.e. the no of sheets you want to add.

Are you trying to add multiple sheets in one go?

Subodh Tiwari (Neeraj)

If I have to do this in Excel, considering the variable strListOfPartNumberPrefixes contains the number of sheets to be added, the Sheets in the proper order can be added by using the following For loop.

For i = 1 To strListOfPartNumberPrefixes
    objBook.Sheets.Add , objBook.Sheets(objBook.Sheets.Count)
Next i

Open in new window

See if you can use this approach in .net.

David Peruso

ASKER

yes.
Background, part item database has ~40 different sub groups (Prefix e.g. 100, 110, 200...), this list can change as new part sub categories are added. I need to put out the the individual part groups to separate sheets within the workbook.
When I run the program as is, instead of the tab order being 1,2,3... it is 40,39,38...

this compiles objBook.Sheets.Add(After:=strListOfPartNumberPrefixes.Length)

but gives the error:
System.Runtime.InteropServices.COMException (0x800A03EC): Exception from HRESULT: 0x800A03EC
at Microsoft.Office.Interop.Excel.Sheets.Add(Object Before, Object After, Object Count, Object Type)
at DDS_MRP.ModPartDataEntry.LoadExcelWithItemsFromPartDataEntrySearch2() in C:\DDS MRP WORKING\DDS_MRP_1-30-19_7-4-0\DDS_MRP\DDS MRP\ModPartDataEntry.vb:line 630

David Peruso

ASKER

I've written the code in vb.net

Norie

What is strListOfPartNumberPrefixes.Length?

Also, if you are adding multiple sheets I would expect to see some sort of loop but there's doesn't appear to be anything like that in the posted code

David Peruso

ASKER

strListOfPartNumberPrefixes.Length is the number of prefix values in the array, right now its 45.

with this line of code:
objBook.Sheets.Add(, , strListOfPartNumberPrefixes.Length)
I get all 45 sheets, not in the order I want, but all 45 show up.
output.JPG

Norie

Dave

As I said the 3rd argument of the Add method is the no of sheets you want to add.

If you want to add sheets and name them using the values in the array strListOfPartNumberPrefixes you'll need a loop.

Something like this perhaps.

        Dim objApp As XL.Application
        Dim objBook As XL._Workbook
        Dim strListOfPartNumberPrefixes() As String = {"Part1", "Part2", "Part3"}

        ' Create a new instance of Excel and start a new workbook.
        objApp = New XL.Application
        objBook = objApp.Workbooks.Add()

        For Each part As String In strListOfPartNumberPrefixes
            objBook.Sheets.Add(After:=objBook.Sheets(objBook.Sheets.Count)).name() = part
        Next part

        objApp.Visible = True

Open in new window

David Peruso

ASKER

Norie,
you're on the right track, just tried this before I saw your post:

For x = 1 To strListOfPartNumberPrefixes.Length - 1
objBook.Worksheets.Add(After:=objSheets(x))
Next
yours came up with an error, but I can find it from here, and thanks for the idea of adding the prefix name :)
Thanks
-Dave

David Peruso

ASKER

problem found, null data

This question needs an answer!

Become an EE member today

7 DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform.

View membership options

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.