トキドキドキンドットコム :: Wordの差し込み印刷をVBAで制御する

仕事をしていてよく使う差し込み印刷

Microsoft Officeのアプリケーション間連携で一番最初に使うであろう、連携らしい連携です。

とても強力なツールなので、使いこなすとパワフルな道具になりますが、それだけにかゆいところに手が届くよう、カスタマイズもしたくなります。

特に差し込み印刷の元になるデータが多くなると、差し込むだけでとても時間がかかるし、抽出や並べ替えをきめ細かくしたくなります。

ここで、VBAの出番。好き勝手に使い込んでみましょう

※プリンタへの差し込み方法は、VBAでWordの差し込み印刷のプリントアウト処理をするをご覧ください。

差し込み印刷に使用するデータアクセスの方法

基本は次の３つ。

OLEDB(ODSO:Office DataSource Object)
ODBC
DDE(Dynamic Data Exchange)

Visual Basic で Word の差し込み印刷を自動化する方法(http://support.microsoft.com/kb/285332/ja)にありますが、特に問題がなければ、OLEDB(ODSO)を使うべきでしょう。差し込むレコード数が多くなったとき、OLEDB(ODSO)接続が、断然高速に作動するように感じます。

Word2000以前はDDEが標準だったようです。現在では主流ではありませんが、差し込み印刷にExcelのWorksheetを使う際には、セルに設定された書式設定が、差し込まれたWord側に反映するようです。
本来はフィールドコードで操作するべきところでしょうが、まだ一定のメリットがあるかもしれません。

動作確認環境

WindowsXP

Microsoft Word 2002 SP3
Microsoft Excel 2002 SP3

差し込み印刷の設定がされたWordファイル名「D:\連絡文.doc」
差し込み印刷の元データが入力されたExcelファイル名「D:\MailMergeTest.xls」
差し込み印刷の元データが入力されたExcelのシート名「住所録」

エクセルのワークシートに入力された項目はこんな感じです。

今回は、Wordのメイン文書に、既に差し込み印刷の設定がなされているところからはじめます。

差し込み印刷の設定がされているファイルで、差し込まれるデータの抽出・並べ替えをVBAで行います。

手始めに、性別が「男」のデータだけ差し込むことにしましょう。

サンプルスクリプト

OLEDB(ODSO)による接続

Sub MailMerge_ODSO()
Dim objDoc As Word.Document
Dim strSQL, strDocFileName, strXlsFileName, strXlsWorksheetName As String

    strDocFileName = "連絡文.doc"       'Wordファイル名を設定
    strXlsFileName = "D:\MailMergeTest.xls"       'Excelファイル名を設定
    strXlsWorksheetName = "住所録"

    strSQL = "SELECT * FROM [" & strXlsWorksheetName & "$]"
    strSQL = strSQL & "WHERE [性別] LIKE '男'"

    Set objDoc = Documents(strDocFileName)
    
    With objDoc.MailMerge
        .OpenDataSource Name:=strXlsFileName, _
            SQLSTATEMENT:=strSQL
        
        .ViewMailMergeFieldCodes = False    'この文書がメイン文書でないときはエラーになる。
    End With

    Set objDoc = Nothing

End Sub

上記のスクリプトの核になるのは、MailMergeObjectのOpenDataSourceメソッドです。

OpenDataSourceメソッドには、たくさんの引数がありますが、OLEDB(ODSO)を使った接続の場合、Name引数にデータベースのフルパスを、そしてSQLSTATEMENT引数に適切なSQLを設定するだけです。

ODBCによる接続

Sub MailMerge_ODBC()
Dim objDoc As Word.Document
Dim strSQL, strDocFileName, strXlsFileName, strXlsWorksheetName As String

    strDocFileName = "連絡文.doc"       'Wordファイル名を設定
    strXlsFileName = "D:\MailMergeTest.xls"       'Excelファイル名を設定
    strXlsWorksheetName = "住所録"

    strSQL = "SELECT * FROM [" & strXlsWorksheetName & "$]"
    strSQL = strSQL & "WHERE [性別] LIKE '男'"

    Set objDoc = Documents(strDocFileName)
    
    With objDoc.MailMerge
        .OpenDataSource Name:="", _
                Connection:="DSN=Excel Files;DBQ=" & strXlsFileName & ";", _
                SQLSTATEMENT:=strSQL, _
                Subtype:=wdMergeSubTypeWord2000
        
        .ViewMailMergeFieldCodes = False    'この文書がメイン文書でないときはエラーになる。
    End With
    
    Set objDoc = Nothing

End Sub

ODBC接続する場合、OpenDataSourceメソッドのName引数にはブランク、Connection引数には接続文字列、そして、Subtype引数にwdMergeSubTypeWord2000を設定します。

DDEによる接続

Sub MailMerge_DDE()
Dim objDoc As Word.Document
Dim strSQL, strXlsFileName As String

    strXlsFileName = "D:\MailMerge\MailMergeTest.xls"       'Excelファイル名を設定

    strSQL = "SELECT * FROM [" & strXlsWorksheetName & "$]"
    strSQL = strSQL & "WHERE [性別] LIKE '男'"

    Set objDoc = Documents("連絡文.doc")
    
    objDoc.MailMerge.DataSource.Close
    
    objDoc.MailMerge.OpenDataSource Name:=strXlsFileName, _
                SQLSTATEMENT:=strSQL, _
                Subtype:=wdMergeSubTypeWord2000

    Set objDoc = Nothing

End Sub

DDE接続をする場合は、OLEDB(ODSO)接続する際のOpenDataSourceメソッドに、Subtype:=wdMergeSubTypeWord2000の引数を追加するだけです。

差し込んだ結果は、次のようになります。

OLEDB(ODSO)接続による差し込み印刷の結果

ODBC接続による差し込み印刷の結果

DDE接続による差し込み印刷の結果

日付時刻型の表記の仕方は、全て異なります。

OLEDB(ODSO)では”m/d/yyyy”、ODBCでは”yyyy/mm/dd h:mm:ss”と時分秒まで表示され、DDEではワークシートの書式設定が反映されます。

IDの項目に入力した数字は、ODBC接続した場合だけ、小数位第1位まで表示されています。

DDE接続すると、Excelのワークシート上で設定したセルの書式が、Wordの差し込みフィールド上でも反映されるようですね。

OpenDataSourceのSubType引数

ドキュメントに記載されていない OpenDataSource メソッド式 [SubType] Word での説明(http://support.microsoft.com/kb/324378/ja)
に記載されているように、SubType引数は文書化されていないオプションで、これについての説明はあまり多くありません。

見かけたのは、
Visual Basic で Word の差し込み印刷を自動化する方法(http://support.microsoft.com/kb/285332/ja)
の中で、ODBCもしくはDDEで接続するときに、Subtype:=wdMergeSubTypeWord2000　とするぐらいです。

SQLの文字数が256から510までの間ならば、SqlStatement1パラメーターを使う手もある

SQLが複雑になると、SQLの文字数も多くなります。

しかし、OpenDataSourceメソッドのSqlStatementパラメーターに設定できる文字数の上限は255。

256文字以上の文字列を設定すると、「実行時エラー’9105′ 文字数が255文字を超えています。」エラーになります。

SqlStatement1パラメーターを使ってみる

OpenDataSourceメソッドのヘルプには、次のように記載されています。

SQLStatement 省略可能です。バリアント型 (Variant) の値を指定します。データ検索のクエリオプションを定義します。

SQLStatement1 省略可能です。バリアント型 (Variant) の値を指定します。クエリ文字列が 256 文字以上である場合、引数 SQLStatement は文字列の最初の区分を、引数 SQLStatement1 は 2 番目の区分を指定します。

では、試してみます。

Accessをデータベースにして、256以上510文字以下のSQL文を作ります（実際に使ったのはSQLの文字数は506。）。文字数はLen関数で確認しました。

まず、OLEDB(ODSO)接続で試します

strMdbFileNameには、Accessファイルのフルパスを設定してあります。

    With objDoc.MailMerge
        .OpenDataSource Name:=strMdbFileName, _
                SqlStatement:=Left(strSQL, 255), _
                SqlStatement1:=Right(strSQL, Len(strSQL) - 255)

        .ViewMailMergeFieldCodes = False
    End With

このスクリプトを走らせると、「テーブルを選択」ダイアログが表示されます。

今回使ったSQLでは255文字より後の部分にFROM句とWHERE句が記載されていたので、「テーブルを選択」ダイアログが表示されたのでしょう。

ちなみに、255文字以下のSQLをSqlStatementとSqlStatement1に分割してもだめでした。

いろいろ試行錯誤しましたが、SqlStatement1パラメーターに設定した値は、渡っていないようです。

ODBC接続で試してみます

    With objDoc.MailMerge
        .OpenDataSource Name:="", _
                Connection:="DSN=MS Access Database;DBQ=" & strMdbFileName & ";", _
                SqlStatement:=Left(strSQL, 255), _
                SqlStatement1:=Right(strSQL, Len(strSQL) - 255), _
                Subtype:=wdMergeSubTypeWord2000
        
        .ViewMailMergeFieldCodes = False
    End With

このスクリプトは正常に動作します。ちなみに、255文字以下のSQLを分割してSqlStatementとSqlStatement1に渡しても大丈夫。

ちなみに、上記のスクリプトで、SQLの文字数が510を超えて、SqlStatement1に渡した文字列の文字数が255を超えると、やはり「実行時エラー’9105′ 文字数が255文字を超えています。」が表示されます。

２つのパラメーターを使っても、OpenDataSourceメソッドで使えるSQLの文字数は510が上限のようです。

上記の結果から、「ODBCで接続すると」、OpenDataSourceメソッドのSqlStatement1引数が使えるようです。

文字数が511以上のSQLを使いたいときは？

SQLの文字数が510を超えてしまった場合は、他の手法を検討する必要があります。

Accessが使えるようなら、Accessのクエリを使う（クエリでデータを整えておいて、SqlStatementに渡すSQLのFROM句にクエリを指定する。）のが常套手段でしょうか。

ホントはレコードセットをWordに渡せればいいのですが、どうもそういった手法は見当たりません。

差し込み印刷でSQLを使うときに気を付けるいくつかのこと

いろいろありますが、まず、データベースにExcelのワークシートを使うとき、SQLに記述する際には[Sheet1$]の様に、ドルマークを付記してた上でかぎ括弧でくくります。
記述例としては以下のものが参考になるかな。

[HOWTO] Visual Basic または VBA から ADO を Excel データで使用する(http://support.microsoft.com/kb/257819/ja)

ワークシートの指定

ワークシートをレコードソースとして指定するには、ワークシート名の後にドル記号を付け、角かっこで囲みます。次に例を示します。
        strQuery = "SELECT * FROM [Sheet1$]"
キーボードのチルダ (~) の下にある (日本語キーボードの場合は P キーの右横にある) 斜めの単一引用符を使用してワークシート名を区切ることもできます。次に例を示します。
        strQuery = "SELECT * FROM `Sheet1$`"
マイクロソフトでは、角かっこの使用をお勧めします。角かっこは不確定なデータベースオブジェクト名に対する現在有効な表記方法です。

ドル記号と角かっこ、またはドル記号のみを省略すると、次のエラーメッセージが表示されます。
        オブジェクト 'Sheet1' が見つかりませんでした。
ドル記号を使用して角かっこを省略すると、次のエラーメッセージが表示されます。
        FROM 句の構文エラーです。
通常の単一引用符を使用すると、次のエラーメッセージが表示されます。
        クエリの構文エラーです。クエリの句が不完全です。
名前付き範囲の指定

名前付きセル範囲をレコードソースとして指定するには、単に定義済みの名前を使用します。次に例を示します。
        strQuery = "SELECT * FROM MyRange"
名前なし範囲の指定

名前なしセル範囲をレコードソースとして指定するには、Excel の行および列の標準的な表記を角かっこ内のシート名の後ろに追加します。次に例を示します。
        strQuery = "SELECT * FROM [Sheet1$A1:B10]"
ワークシートの指定についての注意 : データのテーブルは、指定したワークシートの左上に最も近い、空白でないセルから始まると想定されます。つまり、行 3、列 C からもデータのテーブルは問題なく開始できます。ただし、ワークシートのタイトルなどをセル A1 のデータの上や左側に入力することはできません。

範囲の指定についての注意 : ワークシートをレコードソースとして指定すると、プロバイダによってワークシートの既存のレコードの下に、スペースが許す限り新しいレコードが追加されます。範囲 (名前付き、または名前なし) を指定する場合も、Jet によって範囲の既存のレコードの下に、スペースが許す限り新しいレコードが追加されます。ただし、元の範囲で再クエリを実行すると、結果のレコードセットには新しく追加された範囲外のレコードは含まれません。

あとは、SQLの書き方次第

ここまでできれば、あとはSQLの書き方次第で、かゆいところに手が届くスクリプトになります。

例えば、氏に「田」が含まれるレコードを抽出するなら、

    strSQL = "SELECT * FROM [" & strXlsWorksheetName & "$]"
    strSQL = strSQL & "WHERE [氏] like '%田%'"

ですし、

男性で、ふりがな_氏が「あ～こ」で始まるものを抽出し、生年月日を降順にするなら、

    strSQL = "SELECT * FROM [" & strXlsWorksheetName & "$]"
    strSQL = strSQL & "WHERE [性別] = '男'"
    strSQL = strSQL & " AND [ふりがな_氏] like '[あ-こ]%'"     'ふりがなが、あ～こで始まる
    strSQL = strSQL & " ORDER BY [生年月日] DESC"

となります。

工夫すれば、ワードファイルを開くたびに現在の日付を基準にした特定の期間の対象だけを抽出したり、ツールバーに配置したユーザー設定のボタンにスクリプトを割り当てて、表示させたインプットボックスに入力したパラメーターで毎回抽出条件を変化させることもできます。

これで、元になるデータが何千、何万件あっても、抽出結果のデータ数が少なければ、一瞬で差し込み印刷の結果が表示されます。

Like演算子を使った検索パターンはMicrosoftのヘルプ　
Like 演算子(http://office.microsoft.com/ja-jp/access-help/HP001032253.aspx)
が詳しいです。

差し込み印刷関係でつまづいた事をいくつか

いつまでも古いバージョンのWordを使っているから

Word2003とWord2002で、[差し込み印刷の宛先]ダイアログボックスからフィルタをかけると、再設定できなくなるバグがあります。

普段使わないので気にしませんでしたが、たしかに・・・。

RecordCountプロパティは必ずしも使えるとは限らない

MailMergeDataSourceオブジェクトのRecordCountプロパティはデータファイル内のレコード数を返すのですが、場合によっては「-1」を返して、レコード数を確認できないことがあります。

これまでも、Microsoft Accessを使っていて、テーブルのデータを差し込むときはレコード数を得られたのに、クエリを使うとレコード数を得られないことがありました。

これまでの経験では、Accessのクエリを使った時や、ExcelでもOLEDB(ODSO)接続でなくODBCやDDEで接続したときにレコード数を得られないことがあります。

Wordの差し込み印刷をVBAで制御する

仕事をしていてよく使う差し込み印刷

差し込み印刷に使用するデータアクセスの方法

動作確認環境

サンプルスクリプト

OLEDB(ODSO)による接続

ODBCによる接続

DDEによる接続

OpenDataSourceのSubType引数

SQLの文字数が256から510までの間ならば、SqlStatement1パラメーターを使う手もある

SqlStatement1パラメーターを使ってみる

まず、OLEDB(ODSO)接続で試します

ODBC接続で試してみます

文字数が511以上のSQLを使いたいときは？

差し込み印刷でSQLを使うときに気を付けるいくつかのこと

あとは、SQLの書き方次第

差し込み印刷関係でつまづいた事をいくつか

いつまでも古いバージョンのWordを使っているから

RecordCountプロパティは必ずしも使えるとは限らない

参照

Twitter from tokidokidokin

最近の投稿

カテゴリー

アーカイブ

メタ情報

カテゴリー

最近の投稿

最近のコメント

Portfolio Categories

Wordの差し込み印刷をVBAで制御する

仕事をしていてよく使う差し込み印刷

差し込み印刷に使用するデータアクセスの方法

動作確認環境

サンプルスクリプト

OLEDB(ODSO)による接続

ODBCによる接続

DDEによる接続

OpenDataSourceのSubType引数

SQLの文字数が256から510までの間ならば、SqlStatement1パラメーターを使う手もある

SqlStatement1パラメーターを使ってみる

まず、OLEDB(ODSO)接続で試します

ODBC接続で試してみます

文字数が511以上のSQLを使いたいときは？

差し込み印刷でSQLを使うときに気を付けるいくつかのこと

あとは、SQLの書き方次第

差し込み印刷関係でつまづいた事をいくつか

いつまでも古いバージョンのWordを使っているから

RecordCountプロパティは必ずしも使えるとは限らない

参照

Twitter from tokidokidokin

最近の投稿

カテゴリー

アーカイブ

タグ

メタ情報

カテゴリー

タグ

最近の投稿

最近のコメント

Portfolio Categories

Portfolio Tags