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

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

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

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

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

### この間はすべてタブが1つ
Address<font color="Red">▲</Font>Country<font color="Red">▲</Font>City<font color="Red">▲</Font>1
三角は空白ではなく、タブである。
###直接テキストに貼るとき要注意
このページからコピペで貼る場合はタブにならないので、変える必要がある。
### Wordで読み込むテキストは厳格に
Wordから読み込む場合、データベースとしての形式が守られていないとエラーになる。
1レコードのフィールド数があっているか、
今回は不要であるが、同じデータ型があっているか、
255字以下か。
複数の結果が出ないか。

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

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

```scheme
[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）

```scheme
"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](https://docs.microsoft.com/ja-jp/office/vba/api/word.fields.add)と考える。
フィールドを挿入していく過程をマクロを自動で記録すると、確かにそうなる。
以下は途中でエラーになる例である。

```vb
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)](https://docs.microsoft.com/ja-jp/office/vba/api/word.field.code)はRangeを返すので`Code.Text`としなければならない。

[Word フィールドコードの一覧](https://support.microsoft.com/ja-jp/office/word-%E3%81%AE%E3%83%95%E3%82%A3%E3%83%BC%E3%83%AB%E3%83%89-%E3%82%B3%E3%83%BC%E3%83%89%E4%B8%80%E8%A6%A7-1ad6d91a-55a7-4a8d-b535-cf7888659a51)
　　[Word フィールドコード Databaseフィールド](https://support.microsoft.com/ja-jp/office/%E3%83%95%E3%82%A3%E3%83%BC%E3%83%AB%E3%83%89-%E3%82%B3%E3%83%BC%E3%83%89-database-%E3%83%95%E3%82%A3%E3%83%BC%E3%83%AB%E3%83%89-04398159-a2c9-463f-bb59-558a87badcbc)
##正解はRange InsertDatabase
本当は、
[Range.InsertDatabase メソッド (Word)](https://docs.microsoft.com/ja-jp/office/vba/api/word.range.insertdatabase)
を使う必要がある。

```vb
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
```

##定数設定版

```vb
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つだけにしている。
またフィールド名を排除するこの設定で単純にテキストが入る。
フィールドも残らない。
フィールドが入ると必ず改行がつく。
このため従来は使用にたえなかった。

##参考、フィールドコード

```sql
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 ""`
と空白にしても表として挿入されます。
フィールド名がなくても改行が入る。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/126919/3c5907b5-80a3-a6d6-70c0-117e3cd2970b.png)


```sql
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と入力する。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/126919/aae645e6-3f52-e6e0-eaaa-ce498ffa2c9d.png)

マクロを起動する。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/126919/2eaf99c0-3d01-c345-1061-854ac410c38a.png)

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

##コード1
表の記入には使えません

```vb
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 の相互参照が壊れる理由と対策 (増補) ](https://koukimra.com/archives/416)

## 余談
なお住所だけなら[Word で新しい差し込み印刷リストを設定する](https://support.microsoft.com/ja-jp/office/word-%E3%81%A7%E6%96%B0%E3%81%97%E3%81%84%E5%B7%AE%E3%81%97%E8%BE%BC%E3%81%BF%E5%8D%B0%E5%88%B7%E3%83%AA%E3%82%B9%E3%83%88%E3%82%92%E8%A8%AD%E5%AE%9A%E3%81%99%E3%82%8B-1a752328-b1b5-4865-96a2-e0acd561fe6f?ui=en-us&rs=en-us&ad=us)
という方法もある。差し込み印刷である。
[Word差し込み印刷：256列以降も差し込むには](http://office-qa.com/Word/wd857.htm)
この方法にももっていける。
しかし差し込み印刷はどちらかといえばデータから差し込んでいくので、どうしてもWordの文書からデータベースを呼び出して取得するという方法の逆だった。