Excel vba从excel中导出表格数据和图表到新word文档

office应用程序是可以相互访问的,比如可以把excel的数据导出到word中

step1:

首先要引用word工程对象,在excelvba中依次点击 工具——》引用——》microsoft word 11.0 library

如果是2007版本,应该选择12.0;2010版本,选择14.0

excel <wbr>vba从excel中导出表格数据和图表到新word文档

step2:

准备excel数据

excel <wbr>vba从excel中导出表格数据和图表到新word文档

step3:

添加模块,写入代码

Sub xxx()
   Dim objWordApp As Word.Application    '定义WORD应用程序变量
    Dim objWord As Word.Document    '定义word文档变量
    Dim objSel As Word.Selection
    Dim strTitle As String
    On Error GoTo errHandle        '错误处理
    Sheet1.Select
    strTitle = Sheet1.Cells(1, 1)   '标题
    '选择表格区域
    Sheet1.Range(Cells(2, 1), Cells(28, 7)).Select
    Selection.Copy   '复制
    Set objWordApp = New Word.Application
    Set objWord = objWordApp.Documents.Add      '新建一个文档
    objWord.Application.Visible = True                      '设置为不可见
    Set objSel = objWord.Application.Selection
    With objSel
        .InsertAfter Text:=strTitle & vbCrLf
        .ParagraphFormat.Alignment = wdAlignParagraphCenter  '居中设置
        .Font.Size = 16
        .InsertAfter Text:=vbCrLf
        .EndKey Unit:=wdStory
        .PasteExcelTable False, False, False
        Application.CutCopyMode = False
        ThisWorkbook.Activate
        Sheet1.Select
        '复制图表并粘贴
        Sheet1.ChartObjects(1).CopyPicture
        .Paste
    End With
    '设置word保存路径
    objWord.SaveAs Filename:="C:\test\wordtest.docx", _
        FileFormat:=wdFormatDocument
    objWord.Close
    objWordApp.Quit
errExit:
    Set objSel = Nothing
    Set objWord = Nothing
    Set objWordApp = Nothing
    Exit Sub
errHandle:
    MsgBox Err.Description
    Resume errExit
End Sub


运行后见图:

excel <wbr>vba从excel中导出表格数据和图表到新word文档

阅读更多
换一批

没有更多推荐了,返回首页