Excel VBA 实例

创建一个新工作簿

Sub WbAdd()
    Dim Wb As Workbook, sht As Worksheet
    Set Wb = Workbooks.Add
    Set sht = Wb.Worksheets(1)
    With sht
        .Name = "花名册"
        .Range("A1:F1") = Array("序号", "姓名", "性别", "出生年月", "参加工作时间", "备注")
    End With
    Wb.SaveAs Filename:="E:\1_temp\excel VBA\employees.xls"
    ActiveWorkbook.Close
End Sub

向未打开的工作簿录入数据

Sub WbInput()
    Dim wb As String, xrow As Integer, arr  '定义 arr 变量的类型是Variant'
    wb = "E:\1_temp\excel VBA\employees.xls"
    Workbooks.Open (wb)
    With ActiveWorkbook.Worksheets(1)
        xrow = .Range("A1").CurrentRegion.Rows.Count + 1 '.Count 获取行号'
        arr = Array(xrow - 1, "Arye", "Female", #7/8/1987#, "2010")
        .Cells(xrow, 1).Resize(1, 6) = arr
    End With
    ActiveWorkbook.Close savechanges:=True
End Sub

批量创建新工作表

Sub AddSheet()
    Dim wb_class As Workbook, sht As Worksheet
    Dim i As Integer
    i = 2
    
    Set wb_class = Application.Workbooks.Open("E:\1_temp\excel VBA\class.xls")  '打开工作簿'
    Set sht = wb_class.Worksheets(1)  '引用工作表'
    
    Do While sht.Cells(1, "A") <> ""
        Worksheets.Add after:=Worksheets(Worksheets.Count)
        ActiveSheet.Name = sht.Cells(i, "A").Value
        i = i + 1
    Loop
End Sub