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

概述    1、获取Excel工作薄所有Sheet表名称、Sheet表的个数、删除指定Sheet表： '先引用对象库：Microsoft Excel 11.0 Object LibraryOption ExplicitDim xlExcel As Excel.ApplicationDim xlBook As Excel.WorkbookDim xlSheet As Excel.Work

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

'先引用对象库：Microsoft Excel 11.0 Object libraryOption ExplicitDim xlExcel As Excel.ApplicationDim xlBook As Excel.WorkbookDim xlSheet As Excel.WorksheetPrivate 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 = nothingEnd Sub

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

'先引用对象库：Microsoft Excel 11.0 Object libraryOption ExplicitDim xlExcel As Excel.ApplicationDim xlBook As Excel.WorkbookDim xlSheet As Excel.WorksheetPrivate 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 = nothingEnd Sub

3、用数组填充Excel某区域

'先引用对象库:Microsoft Excel 11.0 Object libraryOption ExplicitDim xlExcel As New Excel.ApplicationDim xlBook As Excel.WorkbookDim xlSheet As Excel.WorksheetPrivate 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 SubEnd SubPrivate Sub Form_Unload(Cancel As Integer)    On Error Resume Next    xlBook.Close    xlExcel.Quit    Set xlSheet = nothing    Set xlBook = nothing    Set xlExcel = nothingEnd Sub

总结

以上是内存溢出为你收集整理的VB控制Excel工作薄实例精选一全部内容，希望文章能够帮你解决VB控制Excel工作薄实例精选一所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。