vba 使用 VB 代码将新工作表添加到现有 Excel 工作簿

Add new sheet to existing Excel workbook with VB code

提问by Rameez

This code creates an Excel file with one sheet. This sheet contains the code of an item like (ASR/Floor/Dept./Item_Name/Item_details/1) which I created and works fine, but I want to add a sheet into this Excel file to create another item code, and then save this file.

此代码创建一个包含一张工作表的 Excel 文件。此工作表包含我创建并正常工作的 (ASR/Floor/Dept./Item_Name/Item_details/1) 等项目的代码，但我想将工作表添加到此 Excel 文件中以创建另一个项目代码，然后保存这个文件。

Dim xlApp As Excel.Application
Dim wb As Workbook
Dim ws As Worksheet
Dim var As Variant
Dim code As String
Dim i, nocode As Integer
Dim fname, heading As String

code = "ASR/" & Text1.Text & "/" & Text2.Text & "/" & Text3.Text & "/" & Text4.Text

Set xlApp = New Excel.Application
Set wb = xlApp.Workbooks.Add ' Create a new WorkBook
Set ws = wb.Worksheets("Sheet1") 'Specify your worksheet name

nocode = txtnocode.Text
heading = Text6.Text

For i = 2 To nocode + 1
  ws.Cells(i, 1).Value = code & "/" & i - 1 '"ORG"
Next i

fname = "c:\" & Text5.Text & ".xls"

wb.SaveAs (fname)
wb.Close
xlApp.Quit

Set ws = Nothing
Set wb = Nothing
Set xlApp = Nothing

回答by jonsca

The Worksheets.Addmethod is what you are looking for:

该Worksheets.Add方法是您正在寻找的：

wb.WorkSheets.Add().Name = "SecondSheet"

See MSDN(scroll down and expand Sheets and Worksheets) for the different parameters you can give to .Addincluding being able to add the sheet before or after a specific one.

请参阅MSDN（向下滚动并展开Sheets and Worksheets），了解您可以提供的不同参数，.Add包括能够在特定工作表之前或之后添加工作表。

回答by user2574072

Set ws = wb.Sheets("Sheet1") 
Set ws = wb.Sheets.Add
ws.Activate

回答by glenn garson

This is some standard code I use for this type of problem Note: This code is VBA, to run from within the Excel document itself

这是我用于此类问题的一些标准代码注意：此代码是 VBA，可从 Excel 文档本身中运行

 Option Explicit

Private m_sNameOfOutPutWorkSheet_1 As String


Sub Delete_Recreate_TheWorkSheet()

    On Error GoTo ErrorHandler

    '=========================
    Dim strInFrontOfSheetName As String
    m_sNameOfOutPutWorkSheet_1 = "Dashboard_1"
    strInFrontOfSheetName = "CONTROL"    'create the new worksheet in front of this sheet

    '1] Clean up old data if it is still there
    GetRidOf_WorkSheet_IfItExists (m_sNameOfOutPutWorkSheet_1)

    CreateNewOutputWorkSheet m_sNameOfOutPutWorkSheet_1, strInFrontOfSheetName
    'Color the tab of the new worksheet
    ActiveWorkbook.Sheets(m_sNameOfOutPutWorkSheet_1).Tab.ColorIndex = 5

    'Select the worksheet that I started with
    Worksheets(strInFrontOfSheetName).Select

    '=========================
      Exit Sub

ErrorHandler:
        Select Case Err.Number
            Case Else
                MsgBox "One_Main - Error: " & Err.Number & " " & Err.Description
        End Select
 End Sub

Sub GetRidOf_WorkSheet_IfItExists(sWorkSheetName_ForInitalData As String)
    On Error GoTo ErrorHandler

    '=========================

    If fn_WorkSheetExists(sWorkSheetName_ForInitalData) Then
        'Sheet Exists
        Application.DisplayAlerts = False
        Worksheets(sWorkSheetName_ForInitalData).Delete
        Application.DisplayAlerts = True

    End If

    '=========================
      Exit Sub

ErrorHandler:
        Select Case Err.Number
            Case Else
                MsgBox "GetRidOf_WorkSheet_IfItExists - Error: " & Err.Number & " " & Err.Description
        End Select
    End Sub


Function fn_WorkSheetExists(wsName As String) As Boolean
    On Error Resume Next
    fn_WorkSheetExists = Worksheets(wsName).Name = wsName
End Function


Sub CreateNewOutputWorkSheet(sWorkSheetName_ForOutputData As String, strInFrontOfSheetName As String)
    On Error GoTo ErrorHandler

    '=========================
    If fn_WorkSheetExists(sWorkSheetName_ForOutputData) Then
        'Sheet Exists
        Application.DisplayAlerts = False
        Worksheets(sWorkSheetName_ForOutputData).Delete
        Application.DisplayAlerts = True
    End If

    Dim wsX As Worksheet
    Set wsX = Sheets.Add(Before:=Worksheets(strInFrontOfSheetName))

    wsX.Name = sWorkSheetName_ForOutputData

    '=========================
      Exit Sub

ErrorHandler:
        Select Case Err.Number
            Case Else
                MsgBox "CreateNewOutputWorkSheet - Error: " & Err.Number & " " & Err.Description
        End Select
End Sub