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