Sheets.Add方法的“After”引數

我嘗試使用以下代碼在活動后添加 5 張紙：

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

我的問題：Sheet2 被添加到 Sheet1 的右側。但是所有后續作業表都被添加到 Sheet2 的左側（見圖）

因此，從 L 到 R 的作業表順序變為：Sheet1、Sheet6、Sheet5、Sheet4、Sheet3、Sheet2。訂單（從 L 到 R）不應該是 Sheet1、Sheet2....Sheet6 嗎？這是正常的還是我錯過了什么？如果這是正確的，那么是否有一種直接的方法來插入作業表，以便從 L 到 R 讀取為 Sheet1、Sheet2...Sheet6？

uj5u.com熱心網友回復：

我想知道這樣的事情是否可行，

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

uj5u.com熱心網友回復：

下面展示了如何使用Microsoft.Office.Interop.Excel將作業表添加到 Excel 作業簿。

添加參考：Microsoft Excel xx.x 物件庫（例如：Microsoft Excel 16.0 物件庫）

添加匯入陳述句：

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

添加表格：

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

資源

• Microsoft.Office.Interop.Excel
• 如何從 Visual Basic .NET 自動化 Excel 以使用陣列填充或獲取區域中的資料
• Worksheets.Add(Object, Object, Object, Object) 方法

標籤：擅长 VB.net com

上一篇：用Javascript打開后在瀏覽器視窗中下載檔案？

下一篇：Powershell電腦資訊查詢匯出到excel