[置顶] 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 Pictures\20056158712694.jpg").Select  '插入图片
        xlBook.Save
Errhandler:
        Exit Sub
End Sub

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

效果图如下：