Excel (VBA)

Excel VBAに関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 10 Home : Excel 2016)
エクセルvbaでエクセルのデータをワードに置換
投稿日時: 20/10/27 00:57:39
投稿者: World_nature11

エクセルの表にある住所や名前をVBAでワードのテンプレートに置換する方法を知りたいです。
 
あまり知識がないのに会社でアサインされてしまったので、見よう見まねなコードでお恥ずかしいのですが、こちらが書いてみたものです。
Public Sub PasteTableToWord()
    'Word Appを開く
    Dim objWord As WORD.Application
    Set objWord = New WORD.Application
    objWord.Visible = True
    'テンプレートのワードファイルのPathを指定
    Dim docPath As String
    docPath = "C:\Users\AQ-PC-05\Desktop\sample.docx"
    '指定したPathのワードファイルを開く(編集可能)
    Dim objDoc As WORD.Document
    Set objDoc = objWord.Documents.Open(docPath)
    'カーソルを冒頭に移動
    objWord.Selection.Start = 0
    objWord.Selection.End = 0
    'ワードで文字列を検索し選択
    objWord.Selection.Find.Text = "住所"
    objWord.Selection.Find.Execute
    '選択箇所を取得
    Dim startIndex As Long
    Dim endIndex As Long
    startIndex = objWord.Selection.Start
    endIndex = objWord.Selection.End
    'エクセルファイルを選択
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    'コピペ
    Call ws.Range("B2").Copy
    Call objDoc.Range(startIndex, endIndex).Paste
    'カーソルを5行目に移動
    objWord.Selection.Start = 5
    objWord.Selection.End = 5
    'ワードで文字列を検索し選択
    objWord.Selection.Find.Text = "会員番号"
    objWord.Selection.Find.Execute
    'エクセルファイルを選択
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    'コピペ
    Call ws.Range("B4").Copy
    Call objDoc.Range(startIndex, endIndex).Paste
    'カーソルを6行目に移動
    objWord.Selection.Start = 6
    objWord.Selection.End = 6
    'ワードで文字列を検索し選択
    objWord.Selection.Find.Text = "名前"
    objWord.Selection.Find.Execute
    'エクセルファイルを選択
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    'コピペ
    Call ws.Range("B3").Copy
    Call objDoc.Range(startIndex, endIndex).Paste
End Sub
 
ワードが開いてコピペされるのですが、
名前が1行目、会員番号が2行目、住所が3行目になります。
会員番号を5行目、名前を6行目、住所が1〜4行目(セル内で改行あり)にしたいです。
 
また、D5にある別の会員番号が1から始まる場合、指定の文書をワードの10行目にコピペしたいです。
 
書式やインデントをワードテンプレートのままにしたいです。
 
初めて質問するので、足りない情報があるかもしれません。
宜しくお願いいたします。[/u]

回答
投稿日時: 20/10/27 09:39:52
投稿者: sk

引用:
エクセルの表にある住所や名前をVBAでワードのテンプレートに置換

引用:
書式やインデントをワードテンプレートのままにしたいです。

Word の差込印刷機能を使用された方が簡単で手っ取り早いと思います。
 
引用:
あまり知識がないのに会社でアサインされてしまった

Excel ブックに記録されている各会員情報のレイアウトを一覧表形式
(ワークシートの 1 行目を列見出し行、2 行目以降をデータ行とする)に
置き換えることが出来れば、そのまま Word の差込印刷のデータソースとして
利用することが出来ますが、そういったデータレイアウトの変換を
行なうことは無理なのでしょうか。
 
引用:
また、D5にある別の会員番号が1から始まる場合、
指定の文書をワードの10行目にコピペしたいです。

ここでの「指定の文書」とは具体的に何を指しているのでしょうか。

投稿日時: 20/10/27 10:45:19
投稿者: World_nature11

差し込み印刷も提案したのですが、元のテンプレートの変更はできるだけないようにしたいらしく、VBAでとなってしまいました。
 
指定文書は、会員番号1から始まる方向けだけのメッセージになります。
 
宜しくお願いします。

回答
投稿日時: 20/10/27 11:16:50
投稿者: WinArrow
投稿者のウェブサイトに移動

>置換
とかいてあるが、Wordの文書内のレイアウトが説明されていないので、よくわかりませんが、
 
Excelのデータは複数件あると想定できますが、
Word文書は1件(又は1ページ)の場合、複数件に対応してページを追加数必要があると思います。
差込印刷機能を使う場合は、ページを増やさなくても対応できますが・・・
 
また、Wordにも置換機能があるので、貼り付けるというより、置換の方が考えやすいかも・・・

回答
投稿日時: 20/10/27 11:30:26
投稿者: simple

お疲れさまです。
 
拝見していてよくわからないのでお聞きしますが、
>元のテンプレートの変更はできるだけないようにしたいらしく
というのはどういうことなんでしょうか。
元々のテンプレートは残しておいて、そのコピーを使えばよいのでは?
 
そして、その判断はどなたがされているのですか?
あなたの上司さんですか?
その人にとっては、結果さえ出ればいいんでしょう?
結果を出すまでのところはあなたに任されているんじゃないんですか?
 
私も差込印刷がいいんじゃないかなと思っていましたので、
なにか致命的な障害になることがあるのでしたら、教えて頂きたいですね。
今後の参考にさせて頂きたいと思っています。

回答
投稿日時: 20/10/27 12:09:04
投稿者: sk

引用:
差し込み印刷も提案したのですが、元のテンプレートの変更は
できるだけないようにしたいらしく

なるほど。
 
引用:
会員番号を5行目、名前を6行目、住所が1〜4行目(セル内で改行あり)にしたいです。

引用:
また、D5にある別の会員番号が1から始まる場合、指定の文書をワードの10行目にコピペ

引用:
指定文書は、会員番号1から始まる方向けだけのメッセージになります。

それぞれのテキストの出力先となる段落が固定されている場合は、
例えば次のようなコードを記述なさればよろしいでしょう。
 
------------------------------------------------------------
Public Sub PasteTableToWord()
     
    'Word アプリケーションを開く
    Dim objWord As Word.Application
    Set objWord = New Word.Application
    objWord.Visible = True
     
    'テンプレートとなる Word 文書の絶対パスを指定
    Dim docPath As String
    docPath = "C:\Users\AQ-PC-05\Desktop\sample.docx"
     
    '指定したパスの Word 文書をテンプレートとして新規文書を作成
    Dim objDoc As Word.Document
    Set objDoc = objWord.Documents.Add(Template:=docPath)
         
    'このブック内のワークシート[Sheet1]を参照
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Sheet1")
     
    With ws
         
        Dim objRange As Word.Range
        Dim lngParagraph As Long
        lngParagraph = 1
         
        'B4 セルの値(住所)を1〜4段落目に出力
        If .Range("B4").Text <> "" Then
            'LFを区切り記号として1次元配列に変換
            Dim varArray As Variant
            Dim varItem As Variant
            varArray = Split(.Range("B4").Text, vbLf)
            For Each varItem In varArray
                Set objRange = objDoc.Paragraphs(lngParagraph).Range
                objRange.MoveEnd wdCharacter, -1
                objRange.Text = varItem
                Set objRange = Nothing
                lngParagraph = lngParagraph + 1
            Next
        Else
            Set objRange = objDoc.Paragraphs(lngParagraph).Range
            objRange.MoveEnd wdCharacter, -1
            objRange.Text = ""
            Set objRange = Nothing
        End If
         
        'B2 セルの値(会員番号)を5段落目に出力
        Set objRange = objDoc.Paragraphs(5).Range
        objRange.MoveEnd wdCharacter, -1
        objRange.Text = .Range("B2").Text
        Set objRange = Nothing
 
        'B3 セルの値(名前)を6段落目に出力
        Set objRange = objDoc.Paragraphs(6).Range
        objRange.MoveEnd wdCharacter, -1
        objRange.Text = .Range("B3").Text
        Set objRange = Nothing
     
        'D5 セルの値が 1 以上である場合は、10段落目に定型文を出力
        Set objRange = objDoc.Paragraphs(10).Range
        objRange.MoveEnd wdCharacter, -1
        If IsNumeric(.Range("D5").Value) Then
            If CDec(.Range("D5").Value) >= 1 Then
                objRange.Text = "何かの定型文"
            End If
        End If
        Set objRange = Nothing
         
    End With
     
    objDoc.Range(0, 0).Select
     
    Set ws = Nothing
    Set objDoc = Nothing
    Set objWord = Nothing
 
End Sub
------------------------------------------------------------
 
Word 文書の本文の中から任意の文字列を検索して
別の文字列に置換する場合は、また違ったフローとなるでしょう。

投稿日時: 20/10/27 12:45:13
投稿者: World_nature11

WinArrowさん
 
置換で考えた方がいいんですね、ありがとうございます。
ワードのページは1ページのみで、
 
------------------
住所
       名前
       会員番号
 
[みんなに送る定型分]
 
[会員番号1からの人にだけ送る定型分]
 
 
以上
------------------
 
このような感じの1ページを現在はマニュアル手作業でエクセルからコピペしているので、
会員番号1からでない人にも不必要な定型分をいれてしまったり、
コピペミスなどのヒューマンエラーが多くVBAを組むということになりました。

投稿日時: 20/10/27 12:51:45
投稿者: World_nature11

simpleさん
 
ご回答ありがとうございます。
 
VBAだとボタンを1度押してできることと、差込印刷機能をかなりの人数に周知することになるのでVBAをということだと思います。
 

回答
投稿日時: 20/10/27 14:03:28
投稿者: WinArrow
投稿者のウェブサイトに移動

考え方の参考
 
文書の段落を意識したコードは得策ではない。変更した場合、メンテが大変です。
単純な文字列置換と考えた方が対応が楽です。
 
(1)メイン処理
   For 行 = xx To XXXX
        Call サブ1(住所、名前、会員番号)
  Next
 
 
(2)サブ1
   テンプレート開く
   Call サブ2(変換前,変換後) 住所
   Call サブ2(変換前,変換後) 名前
   Call サブ2(変換前,変換後) 会員番号
     印刷
     閉じる
 
(3)サブ2
Private Sub docUPD(ByVal SRC As String, ByVal DST As String)
    With wdApp.Selection.Range
        .Find.Text = SRC
        .Find.Forward = True
        .Find.MatchWholeWord = True
        .Find.Replacement.Text = DST
        .Find.Execute , , , , , , , , , , wdReplaceAll
    End With
 
End Sub

投稿日時: 20/10/27 14:22:45
投稿者: World_nature11

skさん
 
ありがとうございます!
勉強になります。
 
WinArrowさんの言うとおり、ワード文書の中から文字列を探して置換の方がメンテがしやすいのでしょうか。
 

回答
投稿日時: 20/10/27 14:26:55
投稿者: simple

回答ありがとうございました。
これ以上こだわるのも大人げないですが、
> VBAだとボタンを1度押してできることと、
> 差込印刷機能をかなりの人数に周知することになるのでVBAをということだと思います。
差込み印刷そのものは割と一般的ですし、一人でできます。
 
ウイザードがついていてインタラクティブにできますし、
マクロ記録することで、マクロにしてボタン一発とできます。
失礼ながら、差込印刷を使ったことがおありなんでしょうか?
よくご存じなくておっしゃっている印象です。
VBAを作成するよりも、よほど作成が容易で、一般の多くの人にも理解できるものだと思います。
 
特定の方だけに特定文字列を挟み込み、それが長文である、ということなら、
差込印刷上では、頻度が低い文字列(例:$$$1$$$など)を埋め込んでおき、
差込された文書に対して一括して置換すればすむことです。
 
# 特にコメントを求めるものではありません。

回答
投稿日時: 20/10/27 15:18:54
投稿者: sk

引用:
WinArrowさんの言うとおり、ワード文書の中から文字列を探して
置換の方がメンテがしやすいのでしょうか。

場合によります。
 
検索/置換処理を何回も呼び出す場合、元から記述されていた本文の一部や、
先に実行された置換処理によって書き換えられた文字列(を含む文字列)が、
その後に実行される置換処理によって別の文字列になってしまう可能性が
残るからです。
(恐らく今回のケースでは大丈夫でしょうけど)
 
そういった不具合を避けたい場合、例えばテンプレートとなる文書内において
各項目の出力/置換先となる文字列をあらかじめブックマークに設定しておき、
そのブックマークのテキストを書き換える、という方式が挙げられます。
 
(ブックマークを使用した例)
----------------------------------------------------------------
Public Sub EditBookmarkText()
     
    'Word アプリケーションを開く
    Dim objWord As Word.Application
    Set objWord = New Word.Application
    objWord.Visible = True
     
    'テンプレートとなる Word 文書の絶対パスを指定
    Dim docPath As String
    docPath = "C:\Users\AQ-PC-05\Desktop\sample.docx"
     
    '指定したパスの Word 文書をテンプレートとして新規文書を作成
    Dim objDoc As Word.Document
    Set objDoc = objWord.Documents.Add(Template:=docPath)
         
    'このブック内のワークシート[Sheet1]を参照
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Sheet1")
     
    With ws
        objDoc.Bookmarks("会員番号").Range.Text = .Range("B2").Text
        objDoc.Bookmarks("名前").Range.Text = .Range("B3").Text
        objDoc.Bookmarks("住所").Range.Text = .Range("B4").Text
        If IsNumeric(.Range("D5").Value) Then
            If CDec(.Range("D5").Value) >= 1 Then
                objDoc.Bookmarks("定型文").Range.Text = "何かの定型文"
            End If
        End If
    End With
 
    objDoc.Range(0, 0).Select
 
    Set ws = Nothing
    Set objDoc = Nothing
    Set objWord = Nothing
 
End Sub
----------------------------------------------------------------
 
引用:
会員番号を5行目、名前を6行目、住所が1〜4行目(セル内で改行あり)にしたいです。

また、セル内改行記号が含まれている[住所]セルの値をそのまま
Word 文書内に出力する場合、それぞれの行のテキストは
段落単位で出力されることになるため、それ以降の段落は
後ろの方へと送られます。
 
あくまで[会員番号]や[名前]などの出力先となる段落の位置を
固定させておきたいならば、先に例示したサンプルのように、
セル内の各行ごとに出力先となる段落を制御する必要があるでしょう。
 
引用:
D5にある別の会員番号が1から始まる場合

あと、さっき気が付いたのですが、これは D5 セルの先頭文字が "1" である場合、
という意味でしょうか。
 
引用:
'D5 セルの値が 1 以上である場合は、10段落目に定型文を出力
Set objRange = objDoc.Paragraphs(10).Range
objRange.MoveEnd wdCharacter, -1
If IsNumeric(.Range("D5").Value) Then
    If CDec(.Range("D5").Value) >= 1 Then
        objRange.Text = "何かの定型文"
    End If
End If
Set objRange = Nothing

'D5 セルの値が "1" から始まる場合は、10段落目に定型文を出力
Set objRange = objDoc.Paragraphs(10).Range
objRange.MoveEnd wdCharacter, -1
If .Range("D5").Text Like "1*" Then
    objRange.Text = "何かの定型文"
End If
Set objRange = Nothing

投稿日時: 20/10/27 17:11:52
投稿者: World_nature11

skさん
 
なるほど、、、そうなんですね。
ありがとうございます。
 
Bookmarkで指定した言葉のところにエクセルのセル内の文字が置換されるようなイメージですね。
確かにメンテはしやすそうです。
 
今試してみたところ、
「実行時エラー '5941':指定されたコレクションのメンバは存在しません。」
というエラーになりまして、原因を調べたのですが、
「VBA で実行したプログラムで参照している文書が開かれていない場合に発生します。」とMSのサイトに書いてありました。
 
ワードファイルは開けているし、会員番号などの文字列もワードテンプレート内にあるのにこのエラーが表示されるのには他に原因があるのでしょうか。

回答
投稿日時: 20/10/27 17:38:37
投稿者: sk

引用:
今試してみたところ、
「実行時エラー '5941':指定されたコレクションのメンバは存在しません。」
というエラーになりまして

テンプレートとなる Word 文書に「会員番号」「名前」「住所」
といった名前のブックマークが全く追加されていない状態で
実行すれば、当然そうなります。
 
ブックマークを新規作成する場合は、Word 側で次のような操作を行なって下さい。
 
------------------------------------------------------------------
 
1. Word 文書の本文に記述されている「会員番号」という文字列を
   (段落記号を含まないように)範囲選択する。
 
2. [挿入]タブ -> [リンク]グループ -> [ブックマーク]をクリックする。
 
3. [ブックマーク名]に「会員番号」と入力し、[追加]をクリックする。
 
4. 「名前」、「住所」、「定型文」についても同様に、
   上記 1 〜 3 の要領でブックマークを追加する。
 
------------------------------------------------------------------
 
文字列とブックマークの関連性を分かりやすくするために
上記のような例を挙げましたが、ブックマークの名前と
対象文字列を全く同じにする必要はありませんし、
範囲選択された文字列ではなく、アクティブなカーソル位置に
ブックマークを追加することも出来ます。

投稿日時: 20/10/27 19:12:12
投稿者: World_nature11

skさん
 
そうですよね、失礼致しました。
ありがとうございます。
 
かなり勉強になりました。
skさんのようにぱっと書けるようにもっと頑張りたいと思います。
 
皆様本当にありがとうございました!