VB控制Excel工作薄实例精选一

1、获取Excel工作薄所有Sheet表名称、Sheet表的个数、删除指定Sheet表：

'先引用对象库：Microsoft Excel 11.0 Object Library
Option Explicit
Dim xlExcel As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet

Private Sub Command1_Click()
        On Error GoTo Errhandler
        CommonDialog1.Filter = "Excel(*.xls)|*.xls|AllFile(*.*)|*.*"
        CommonDialog1.FilterIndex = 1
        CommonDialog1.ShowOpen
        Set xlExcel = New Excel.Application
        xlExcel.Workbooks.Open CommonDialog1.FileName
        Set xlBook = xlExcel.Workbooks(CommonDialog1.FileTitle)
        Application.Visible = False
        Application.DisplayAlerts = False '不提示保存对话框
        Debug.Print xlBook.Worksheets.Count 'Sheet表的个数
        For Each xlSheet In xlBook.Worksheets
           Set xlSheet = xlBook.Worksheets(xlSheet.Name)
           Debug.Print xlSheet.Name '列出所有Sheet表
           If xlSheet.Name = "Sheet5" Then xlSheet.Delete '删除Sheet5表
        Next
        xlBook.Save
        
Errhandler:
        xlBook.Close
        xlExcel.Quit
        Set xlSheet = Nothing
        Set xlBook = Nothing
        Set xlExcel = Nothing
End Sub

2、获取Excel工作薄某Sheet表有效数据的行数、列数：

'先引用对象库：Microsoft Excel 11.0 Object Library
Option Explicit
Dim xlExcel As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet

Private Sub Command1_Click()
        On Error GoTo Errhandler
        CommonDialog1.Filter = "Excel(*.xls)|*.xls|AllFile(*.*)|*.*"
        CommonDialog1.FilterIndex = 1
        CommonDialog1.ShowOpen
        Set xlExcel = New Excel.Application
        xlExcel.Workbooks.Open CommonDialog1.FileName
        Set xlBook = xlExcel.Workbooks(CommonDialog1.FileTitle)
        Application.Visible = False
        Set xlSheet = xlBook.Worksheets("Sheet1") '或者xlBook.Sheets("Sheet1").Select'指定Sheet表
        Debug.Print xlExcel.ActiveSheet.UsedRange.Rows.Count    '有效数据行数
        Debug.Print xlExcel.ActiveSheet.UsedRange.Columns.Count '有效数据列数
Errhandler:
        xlBook.Close
        xlExcel.Quit
        Set xlSheet = Nothing
        Set xlBook = Nothing
        Set xlExcel = Nothing
End Sub

'先引用对象库:Microsoft Excel 11.0 Object Library
Option Explicit

Dim xlExcel As New Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet

Private Sub Command1_Click()

        Dim Data(1 To 200,1 To 10) As String
        Dim i As Long,j As Long
        
        For i = 1 To 200
            For j = 1 To 10
                Data(i,j) = j
            Next
        Next
        
        On Error GoTo Errhandler
        xlExcel.Application.Visible = True
        Me.MousePointer = vbHourglass
        xlExcel.Workbooks.Add '创建新的工作薄
        xlExcel.Workbooks(1).Activate '激活工作薄
        Set xlSheet = xlExcel.Workbooks(1).Worksheets("Sheet1") '指定Sheet表
        'Set xlSheet = xlBook.Worksheets("Sheet1")
        xlSheet.Activate
        xlSheet.Columns("A:J").NumberFormatLocal = "@" '设置A-J列为文本格式。
        '或者xlSheet.Range("A:J").NumberFormatLocal = "@"
        xlSheet.Range("A1:J200 ") = Data '填充数组到区域A1到J200
        xlSheet.Columns.EntireColumn.AutoFit '列自适应
        Me.MousePointer = vbDefault
        
Errhandler:
        Exit Sub
End Sub

Private Sub Form_Unload(Cancel As Integer)
    On Error Resume Next
    xlBook.Close
    xlExcel.Quit
    Set xlSheet = Nothing
    Set xlBook = Nothing
    Set xlExcel = Nothing
End Sub

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!