"After" parameter of Sheets.Add method-CodePudding

I tried adding 5 sheets after the active using the following code:

Imports System
Imports Microsoft.Office.Interop

Module Program
    Dim oxl As Excel.Application
    Dim owbs As Excel.Workbooks
    Dim owb As Excel.Workbook
    Dim osheets As Excel.Worksheet

    Sub Main(args As String())
        Dim pos As Excel.Worksheet
        oxl = CreateObject("Excel.Application")
        oxl.DisplayAlerts = True
        oxl.Visible = True
        owb = oxl.Workbooks.Add
        pos = owb.Worksheets("Sheet1")
        Console.WriteLine("Enter key to add 5 worksheets")
        Console.ReadLine()
        'Adds 5 sheets after 'Sheet1' - default active sheet
        osheets = owb.Worksheets.Add(, pos, 5,)
        Console.ReadLine()
    End Sub
End Module

My Question: Sheet2 is getting added to the right of Sheet1. But all subsequent sheets are getting added to the left of Sheet2 (see image)

Hence the order of the sheets from L to R becomes: Sheet1, Sheet6, Sheet5, Sheet4, Sheet3, Sheet2. Should the order (from L to R) not have been Sheet1, Sheet2....Sheet6?? Is this normal or am I missing something? Incase this is correct then is there a direct way to insert sheets so that from L to R they read as Sheet1, Sheet2...Sheet6??

CodePudding user response：

I wonder if something like this would work,

Sub Main(args As String())
    Dim pos As Excel.Worksheet
    oxl = CreateObject("Excel.Application")
    oxl.DisplayAlerts = True
    oxl.Visible = True
    owb = oxl.Workbooks.Add
    pos = owb.Worksheets("Sheet1")
    Console.WriteLine("Enter key to add 5 worksheets")
    Console.ReadLine()
    'Adds 5 sheets after 'Sheet1' - default active sheet
    For x As Integer = 1 To 5
        osheets = owb.Worksheets.Add(, pos, 1, )
        Console.ReadLine()
        pos = owb.Worksheets(owb.Worksheets.Count) 'last sheet?
    Next
End Sub

CodePudding user response：

The following shows how to use Microsoft.Office.Interop.Excel to add worksheets to an Excel workbook.

Add reference: Microsoft Excel xx.x Object Library (ex: Microsoft Excel 16.0 Object Library)

Add Imports statements:

Imports Excel = Microsoft.Office.Interop.Excel
Imports System.IO

AddSheets:

Private Sub AddSheets(filename As String)
    'All indices in Excel (rowNumber, columnNumber, etc...) start with 1 

    Dim oMissing As Object = System.Reflection.Missing.Value
    Dim oxl As Excel.Application = Nothing
    Dim owb As Excel.Workbook = Nothing
    Dim osheet As Excel.Worksheet = Nothing
    Dim previouslyActiveSheet As Excel.Worksheet = Nothing

    Try
        'create new instance
        oxl = New Excel.Application()

        'suppress displaying alerts (such as prompting to overwrite existing file)
        oxl.DisplayAlerts = False

        'set Excel visibility
        oxl.Visible = False

        'disable user control while modifying the Excel Workbook
        'to prevent user interference
        'only necessary if Excel application Visibility property = true
        'oxl.UserControl = False

        'if writing/updating a large amount of data
        'disable screen updating by setting value to false
        'for better performance.
        're-enable when done writing/updating data, if desired
        'oxl.ScreenUpdating = False;

        If File.Exists(filename) Then
            'open existing
            owb = oxl.Workbooks.Open(filename)
        Else
            'add Workbook
            owb = oxl.Workbooks.Add()
        End If

        'get active worksheet
        If owb.Sheets.Count > 0 Then
            previouslyActiveSheet = DirectCast(oxl.ActiveSheet, Excel.Worksheet)
        End If

        'get last sheet
        osheet = DirectCast(owb.Sheets(owb.Sheets.Count), Excel.Worksheet)

        'add sheets
        For i As Integer = 1 To 5
            osheet = DirectCast(owb.Worksheets.Add(After:=osheet, Count:=1), Excel.Worksheet)
        Next

        'For Each ws As Excel.Worksheet In owb.Sheets
        'Debug.WriteLine($"ws name: {ws.Name}")
        'Next

        If previouslyActiveSheet IsNot Nothing Then
            'set active sheet to originally active sheet
            previouslyActiveSheet.Activate()
        End If

        'save
        owb.SaveAs(Filename:=filename)

        'if previously disabled, re-enable
        'oxl.UserControl = True

        'if previously disabled, re-enable
        'oxl.ScreenUpdating = True;

    Catch ex As Exception
        'ToDo: add desired code
        Throw 're-throw exception
    Finally
        If owb IsNot Nothing Then
            owb.Close()
        End If

        If oxl IsNot Nothing Then
            oxl.Quit()
        End If
    End Try
End Sub

Resources