Word VBA テキスト形式のファイルをデータベースとみなして値を挿入するマクロ

テキストファイル

設定

コード UTF-8 65001
区切り文字 TAB
改行 Cr+LF
ヘッダー＝フィールド（項目名）あり
ファイル名 C:\Hoge\Hogehoge\TextDBTab1.txt
取得できる値は必ず一つだとする。
列は4列程度
行（レコード）は最大で50行程度にすること。
選択クエリ（SQL）についての理解が必要となる。

なお、直接は関係ないが、1フィールドの文字数は半角英数字で255字が上限となる。空白は使用できる、タブ、改行は使用できない。タブと改行はデータとレコードの区切り文字になるためである。
また、サンプルが10字程度なのは、これくらいでないと画面、あるは印刷で、横に見通しが利かないためである。

Col1	Col2	Col3	Col4
Name	May	Satsuki	""
Name	John	Smith	""
Address	Country	City	""

このデータベースで、Col2の値がJohnであるレコードのCol3の値は"Smith"になる。

この間はすべてタブが1つ

Address▲Country▲City▲1
三角は空白ではなく、タブである。

直接テキストに貼るとき要注意

このページからコピペで貼る場合はタブにならないので、変える必要がある。

Wordで読み込むテキストは厳格に

Wordから読み込む場合、データベースとしての形式が守られていないとエラーになる。
1レコードのフィールド数があっているか、
今回は不要であるが、同じデータ型があっているか、
255字以下か。
複数の結果が出ないか。

保存するときの文字コード

保存のときの文字コードはUTF-8にすること。

Schema.ini

あまり役立たないかもしれないが、C:\Hoge\Hogehoge\Schema.iniも作っておくべきである。
というかテキストでデータベースを作る場合、必ずSchema.iniまで作ったほうが良い。
それが設計図になるためである。

[TextDBTab1.txt]
ColNameHeader=True 
Format=TabDelimited 
MaxScanRows=25 
Charset=65001
TextDelimeter="
Col1=Col1 Char Widh 255
Col2=Col2 Char Widh 255
Col3=Col3 Char Widh 255
Col4=Col4 Char Widh 255

接続文字列（Connectionstring）

"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Hoge\Hogehoge\;Extended Properties=""text;HDR=Yes;FMT=TabDelimited"";"

ドライブ名は最後に円またはスラッシュ

C:\Hoge\Hogehoge\ C:\Hoge\Hogehogeではない。

コード

Fields.Addではない

ちょっと考えるとFields.Addと考える。
フィールドを挿入していく過程をマクロを自動で記録すると、確かにそうなる。
以下は途中でエラーになる例である。

Sub Macro1()
Dim wFld As Word.Field
' だめなコード（Do Not Use)
` 途中でエラーがおきてしまう
' Macro1 Macro
'
Dim rngTemp As Range
`Application.ScreenUpdating = False
`ThisDocument.Fields.Locked = False
`Application.DisplayAlerts = wdAlertsNone
 With ThisDocument.Fields
 Set wFld = .Add(Range:=Selection.Range, Type:=wdFieldDatabase, PreserveFormatting:=False)
 wFld.Select
 With wFld
 .Code.Text = "DATABASE \d ""C:\\Hoge\\Hogehoge\\TextDBTab1.txt"" \c ""Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Hoge\Hogehoge\;Extended Properties=""""text;HDR=Yes;”” \s ""SELECT Col3 FROM C:\\Hoge\\Hogehoge\\TextDBTab1.txt WHERE ((Col2 = 'John')) ORDER BY Col2"""
.Update
End With
End With
`Application.DisplayAlerts = wdAlertsMessageBox
`Application.ScreenUpdating = True
End Sub

確かに、フィールド自体が挿入後、データベースファイルの設定を要求される。そこでキャンセルするとエラーメッセージが出る。
しかし、なぜかフィールド自体は挿入され値が表示される。
また、フィールドコードは255文字が限界らしく、接続文字列は途中で切れてしまう。
今回とは関係がないが、Field.Code プロパティ (Word)はRangeを返すのでCode.Textとしなければならない。

Word フィールドコードの一覧
　　Word フィールドコード Databaseフィールド

正解はRange InsertDatabase

本当は、
Range.InsertDatabase メソッド (Word)
を使う必要がある。

Sub WordInsertDatabaseFldValue()
    Selection.Range.InsertDatabase Format:=WdTableFormat.wdTableFormatNone, Style:=0, LinkToSource:=False, _
        Connection:="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Hoge\Hogehoge\;Extended Properties""""Text;HDR=Yes;FMT=TabDelimited""""", SQLStatement:= _
        "SELECT Col3 FROM C:\Hoge\Hogehoge\TextDBTab1.txt WHERE ((Col2 = 'John')) ORDER BY Col1" _
         & "", PasswordDocument:="", PasswordTemplate:="", WritePasswordDocument _
        :="", WritePasswordTemplate:="", DataSource:= _
        "C:\Hoge\Hogehoge\TextDBTab1.txt", From:=1, To:=1, IncludeFields:=False
End Sub

定数設定版

Const cnsReadFile = "C:\Hoge\Hogehoge\TextDBTab1.txt" ' UTF-8 Header = True TabDelimited
Const cnsReadFileFolder = "C:\Hoge\Hogehoge\" '上のファイルのフォルダ名だけ、最後に日本語は円英語はスラッシュ
Const cnsConnectionStringPart1 = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="
Const cnsConnectionStringPart2 = ";Extended Properties=""text;HDR=Yes;FMT=TabDelimited"";"

Sub WordInsertDatabaseFldValue2()
    Selection.Range.InsertDatabase Format:=WdTableFormat.wdTableFormatNone, Style:=0, LinkToSource:=False, _
        Connection:=cnsConnectionStringPart1 & cnsReadFileFolder & cnsConnectionStringPart2, SQLStatement:= _
        "SELECT Col3 FROM " & cnsReadFile & " WHERE ((Col2 = 'John')) ORDER BY Col1" _
         & "", PasswordDocument:="", PasswordTemplate:="", WritePasswordDocument _
        :="", WritePasswordTemplate:="", DataSource:= _
        cnsReadFile, From:=1, To:=1, IncludeFields:=False
End Sub

全然スッキリしないが、ポイントが少し見やすいか。

Select Col3 ここが取り出す列のフィールド名（項目名）
(Col2 = 'John') ここが検索条件の対象となる列のフィールド名とその値。テキストだとダブルクォーテーションではなく、シングルクォーテーションで囲むのがポイント。
この辺は選択クエリというかSQLそのものである。

From:=1, To:=1, IncludeFields:=False

ここも重要。強制的に1つだけにしている。
またフィールド名を排除するこの設定で単純にテキストが入る。
フィールドも残らない。
フィールドが入ると必ず改行がつく。
このため従来は使用にたえなかった。

参考、フィールドコード

DATABASE  \d "C:\\Hoge\\hogehoge\\TextDBTab1.txt" \c "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Hoge\Hogehoge\;Extended Properties=""text;" \s "SELECT Col3 FROM C:\\Hoge\\hogehoge\\TextDBTab1.txt WHERE ((Col2 = 'John')) ORDER BY Col2" 

何回層も下のフォルダに入れると、これでは取得が難しくなる。
その場合は \c "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Hoge\Hogehoge\;Extended Properties=""text;"
を
\c ""
と空白にしても表として挿入されます。
フィールド名がなくても改行が入る。

DATABASE  \d "C:\\Hoge\\hogehoge\\TextDBTab1.txt" \c "" \s "SELECT Col3 FROM C:\\Hoge\\hogehoge\\TextDBTab1.txt WHERE ((Col2 = 'John')) ORDER BY Col2" 

このあとのRange.insertDatabseも値が2つあったり、フィールド名がつくと表になる。

問題点、Wordの表（テーブル）には挿入できない

表には挿入できない

この方法には問題があり、表（テーブル）のセルに値を入れることができない。
表自体がふっとばされて消えてしまう。
仮に、Tabeles(1).Cells(1,2)でSelectしても表自体が消えてしまう。
さらにCellsにはInsertDatabaseがない。
このため、現時点ではWordでは表に入力することができない。

更新ができない

フィールドコードと異なり、値だけになる。

使用例

Wordを起動し、My name isと入力する。

マクロを起動する。

このように改行が入る。なので、消すとOk。しかしカーソルが動いていないことに着目すると、
Selection.Delete Unit:=wdCharacter, Count:=1を加えた次のようなものになる。
ただし絶対に確実とは言い切れない。なのでコード1として掲載する。

コード1

表の記入には使えません

Sub WordInsertDatabaseFldValue1()
' For Word VBA
' You Do Not Try to Insert value Into Tabele Cell with using This Macro.
' Your Table may be deleted.
' 表の中で起動すると、表が削除される。
' 設定はこのコードを掲載しているページの冒頭を見て設定すること。
' テキスト形式のデータベースが必要。
' データを入力したい位置にカーソルを移動し、マクロを起動する。
    Selection.Range.InsertDatabase Format:=WdTableFormat.wdTableFormatNone, Style:=0, LinkToSource:=False, _
        Connection:="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Hoge\Hogehoge\;Extended Properties""""Text;HDR=Yes;FMT=TabDelimited""""", SQLStatement:= _
        "SELECT Col3 FROM C:\Hoge\Hogehoge\TextDBTab1.txt WHERE ((Col2 = 'John')) ORDER BY Col1" _
         & "", PasswordDocument:="", PasswordTemplate:="", WritePasswordDocument _
        :="", WritePasswordTemplate:="", DataSource:= _
        "C:\Hoge\Hogehoge\TextDBTab1.txt", From:=1, To:=1, IncludeFields:=False
     Selection.Delete Unit:=wdCharacter, Count:=1
End Sub

このコードがなんの役に立つのか

項目に対するデータの共有

このコードはWordと次の記事のExcelで共通のデータを取得し、記入するというときに使える。
UTF-8なので、環境依存文字も使える。
さらにタブ区切りのため、コンマつき数字、空白が入ってもOK。
通常はタブは必要なデータにはならないので、この方法で入力がだいたいできることになる。

NotePadで設定

ある人のレポートを書くとして、名前、生年月日、手入力では誤りが生じることが多い。
無論、参照設定を使って、ブックマークから引っ張るという方法がある。
しかし、Word単体でしか使えず、Word Excelを共通にすることはできない。
また、書式やフォントの違いで見間違いが生じる。
しかしNotePadは現在文字の大きさも変えられるようになり、データを入力しやすくなった。
そこで、Notepadで設定データを定義する。そこでチェックも行う。
Notepadはスペルチェックはないが、起動も早く、書き換えも素早い。

入力項目ごとに違うマクロを

なお、値ごとにマクロを用意し、入力するようにしたほうが良い。
さらに、一度入力したら、そこでブックマークを設定し、参照設定で引っ張る。
このようにすると、たとえ値が変わっても、値を書き換えるか、変わった値に同じ名前のブックマークを設定すれば、更新をかけることで、すべて変更される。
さらに、違う人のレポートも同時並行で書く場合、別のファイルを作り、そこを書き換える。
マクロのファイル名を変えると、値が変わるので、それをブックマークしなおす。
しかし、書式が変わると、それも反映される。
このため、書式が変わるとごとにマクロを使うわけである。
BookMarkの参照先が不明になるなどの対策はこちら。
Word の相互参照が壊れる理由と対策 (増補)

余談

なお住所だけならWord で新しい差し込み印刷リストを設定する
という方法もある。差し込み印刷である。
Word差し込み印刷：256列以降も差し込むには
この方法にももっていける。
しかし差し込み印刷はどちらかといえばデータから差し込んでいくので、どうしてもWordの文書からデータベースを呼び出して取得するという方法の逆だった。