Excel的简单Word报告批量生成 vba笔记 (一)

最新推荐文章于 2025-11-05 21:22:23 发布

原创最新推荐文章于 2025-11-05 21:22:23 发布 · 1.4w 阅读

41 ·

CC 4.0 BY-SA版权

文章标签：

#vba入门

本文介绍如何使用VBA批量生成Excel的简单Word报告，包括检查引用、处理图表位置、文字错误、图片格式转换、不同源数据的处理以及调试技巧，旨在帮助初学者掌握VBA自动化报告生成。

批量生成Excel的简单Word报告/vba/录制宏

Sub piliangshengcheng()
Set dd = CreateObject("excel.application")
dd.workbooks.Open ("D:\ALM数据分析\VBA\第三天\二手车.xlsx")
dd.sheets("sheet5").Select
For i = 1 To dd.worksheetfunction.counta(dd.Range("N:N"))
'range应有范围
    brand = dd.Range("N" & i)
    mubiao = "D:\ALM数据分析\VBA\第三天\二手车_报告\" & brand & ".docx"
    dd.ActiveSheet.PivotTables("数据透视表3").PivotFields("品牌").ClearAllFilters
    dd.ActiveSheet.PivotTables("数据透视表3").PivotFields("品牌").CurrentPage = brand
    FileCopy "D:\ALM数据分析\VBA\第三天\二手车.docx", mubiao
    Set ddd = Documents.Open(mubiao)
    
    With Selection.Find
    .Text = "现代"
    .Replacement.Text = brand
    .Forward = True
    .Execute Replace:=wdReplaceAll
    End With
    
    dd.Range("A3:B" & dd.worksheetfunction.counta(dd.Range("B:B")) + 1).Select
    dd.Selection.Copy
    With Selection.Find
    .Text = "表1"
    .Replacement.Text = ""
    .Forward = True
    .Execute
    End With
    Selection.MoveUp wdLine, 1
    Selection.Paste
    Application.ActiveDocument.Tables(1).PreferredWidth = 400
    Application.ActiveDocument.Tables(1).Select
    Selection.ParagraphFormat.Alignment = wdAlignParagraphCenter
    

    dd.ActiveSheet.ChartObjects("图表 1").Activate
    dd.ActiveSheet.Shapes("图表 1").Width = 400
    dd.ActiveChart.ChartArea.Copy
    With Selection.Find
        .Text = "图1"
        .Replacement.Text = ""
        .Forward = True
        .Wrap = wdFindContinue
        '不能少.wrap
        .Execute
    End With
    Selection.MoveUp wdLine, 1
    Selection.PasteSpecial Link:=False, DataType:=14, Placement:=wdInLine, _
        DisplayAsIcon:=False
    
    With Selection.Find
    .Text = "对比描述"
    .Replacement.Text = dd.Range("F21")
    .Forward = True
    .Execute Replace:=wdReplaceAll
    End With
    
    ddd.Save
    ddd.Close
    Set ddd = Nothing
Next
dd.DisplayAlerts = False
dd.Quit
End Sub

注:

1. 检查每个引用是否声明来源 (父级) //(90%的错误来源)

brand = dd.Range("N" & i)

2. 插入图表位置错误

.Wrap = wdFindContinue

3. 插入文字时产生错误

.Execute Replace:=wdReplaceAll

4. 图片去链接(粘贴为图片格式)

Selection.PasteSpecial Link:=False, DataType:=14, Placement:=wdInLine, _
        DisplayAsIcon:=False

5. 来源报表长度不等时取最大范围

& xx.worksheetfunction.counta(dd.Range("B:B")) + 1

6. 调试时尽量避免单独运行

Set dd = CreateObject("excel.application")
dd.workbooks.Open ("D:\ALM数据分析\VBA\第三天\二手车.xlsx")

避免产生误报

7. 关键字检查大小写检测准确率未必100%

Set ddd = Documents.Open(mubiao)
Set ddd = Document.Open(mubiao)   //error

7. 文件调用开始与结束...代码封闭

Set dd = CreateObject("excel.application")
dd.workbooks.Open ("路径")
    Set ddd = Documents.Open(mubiao)


    ddd.Save
    ddd.Close
    Set ddd = Nothing
dd.DisplayAlerts = False //excel弹出框提示
dd.Quit