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

1、获取Excel数据图表到VB的PictureBox控件中

'先引用对象库：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 = True
        Set xlSheet = xlBook.Worksheets("Sheet1") '指定Sheet表
        xlSheet.ChartObjects(2).Chart.CopyPicture '读取第2个数据图表到剪贴板
        Picture1.Picture = Clipboard.GetData      '粘贴数据到图片框
        Clipboard.Clear                           '清除剪贴板数据
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

效果图如下：

2、插入图片到Excel表格中

'先引用对象库：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 = True
        Application.DisplayAlerts = False '不提示保存对话框
        Set xlSheet = xlBook.Worksheets("Sheet1") '指定Sheet表
        xlBook.Sheets("Sheet1").Select  '指定Sheet表
        ActiveSheet.Pictures.Insert("F:资料My Pictures20056158712694.jpg").Select  '插入图片
        xlBook.Save
Errhandler:
        Exit Sub
End Sub

Private Sub Form_Load()
    Picture1.Picture = LoadPicture("F:资料My Pictures20056158712694.jpg")
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

效果图如下：

3、获取Excel表格中的Ole对象（获取图像）

'先引用对象库：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 = True
        Application.DisplayAlerts = False '不提示保存对话框
        Set xlSheet = xlBook.Worksheets("Sheet1") '指定Sheet表
        xlBook.Sheets("Sheet1").Select  '指定Sheet表
        xlSheet.Shapes(3).CopyPicture '读取编号为3的图片。需要注意的是，该命令不仅能获取数据图表，也能获取图片。
        '或者xlSheet.OLEObjects(3).CopyPicture
        Picture1.Picture = Clipboard.GetData      '粘贴数据到图片框
        Clipboard.Clear                           '清除剪贴板数据
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

效果图如下：

（编辑：李大同）

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