Excel VBA(エクセル マクロ) 外部データの取り込みのマクロ化 テキストデータ(CSVファイル)の取得。

Excelツールで外部データの取り込みがある。
VBAでマクロ化して、Excelにファイル名を変更して読み込むようにする。

Excelで外部からテキストファイルを取得したい。

Excelのツールで外部データの取り込みがあるので、マクロ化して都度テキストファイル名を変更して取り込むことを考えた。(通常テキストファイルをオープンして読むことが主流であまりこの方法が採用されないが。。)


サンプルソースをダウンロードできます。

作成したマクロは以下の通りです。

Sub torikmi1()
  Sheets("Sheet1").Cells.Clear
  With Sheets("Sheet1").QueryTables.Add(Connection:="TEXT;C:test.txt", Destination:=Sheets("sheet1").Range("A1"))
    .Name = "test"
    .FieldNames = True
    .RowNumbers = False
    .Refresh BackgroundQuery:=False
    .RefreshPeriod = 0
    .RefreshOnFileOpen = False
    .PreserveFormatting = True
    .AdjustColumnWidth = True
    .FillAdjacentFormulas = False
    .RefreshStyle = xlInsertEntireRows
    .SavePassword = False
    .SaveData = True
    .TextFilePromptOnRefresh = False
    .TextFileParseType = xlFixedWidth
    .TextFileStartRow = 1
    .TextFilePlatform = 932
    .TextFilePlatform = xlWindows
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimiter = False
    .TextFileTabDelimiter = False
    .TextFileSemicolonDelimiter = False
    .TextFileCommaDelimiter = False
    .TextFileSpaceDelimiter = False
    .TextFileTrailingMinusNumbers = True
    .TextFileColumnDataTypes = Array(5, 9, 1, 9, 2)
    .TextFileFixedColumnWidths = Array(10, 2, 8, 4, 200)
    .Refresh
    .Parent.Names(.Name).Delete
    .Delete
  End With
End Sub

外部データの取り込みをマクロ化するのにこんなに設定しなくてはいけないのと思った(実際はデフォルト設定のため設定必要なし)。

1行づつ調査したことを記述する。

With Sheets("Sheet1").QueryTables.Add(Connection:="TEXT;C:test.txt", Destination:=Sheets("sheet1").Range("A1"))


End With


QueryTables.Addを使用し、Connection:="TEXT;C:test.txt"でテキストファイルC:text.txtに接続する。
Destination:=Sheets("sheet1").Range("A1")で「sheet1」のA1にコピーする。
これだけでは、データは取り込めない。
.RefreshのみをWith構文の中に記入すると文字化けするが取り込める。
(Connection:="URL;http://***"でHTMLファイルが取り込めるようだが次回で取り込んでみる)


まずは、外部データ取り込みの「外部データ範囲のプロパティ」部分を設定する。

各項目についてtrueに設定してマクロを動かすとチェックがはいる。

.Name = "test"
名前を設定する。ただ、何回も取り込むとtest_1とクエリが増えてしまうので後で消去する必要がある。

.FieldNames = True
データの列見出し(1行目)が太字になる。クエリ用の名前になるのか?

.RowNumbers = False
行番号が表示されるとMicrosoft MSDNにかかれてあったが表示されなかった。表示して欲しくないのでFalseとした。

.Refresh BackgroundQuery:=False
定期的に更新する(バックグラウンドで更新する)か設定する。Falseで定期的に更新しない。

.RefreshPeriod = 0
定期的に更新するの時間設定をする。0とすれば定期更新されない。

.RefreshOnFileOpen = False
ファイルを開く時データを更新するかを設定する。Falseで更新しない設定。

.PreserveFormatting = True
セル書式を保持するか設定する。trueでセルの書式を保持。

.AdjustColumnWidth = True
列の幅を調整する。trueにすると、文字の長さによって列の幅が自動調整される。

.FillAdjacentFormulas = False
クエリーテーブルの更新時、数式を自動更新するかを設定する。Falseで自動更新しないように設定。

.RefreshStyle = xlInsertEntireRows
変更されたレコード(行)の処理を設定する。
 xlInsertDeleteCells ←新しいデータのセルを挿入し、使用されていないセルを削除する。
 xlInsertEntireRows ←新規データの行全体を挿入し、使用されていないセルはクリアする。
 xlOverwriteCells ←既存のセルを新規データで上書きし、使用されていないセルはクリアする。

.SavePassword = False
パスワードを保存するか設定する。パスワード自体使ったことがないのでFalseとした。

.SaveData = True
今回設定しているクエリデータテーブルをブック保存時に保存するか設定。trueで保存設定している。
しかし、何回もマクロ実行毎にクエリを作成しては、エクセルが重くなるため、クエリを実行しデータを取り込んだ後、後述する命令文でクエリを削除している。


次は外部データ取り込み実行時のウィザードの設定を行う。

(1)外部ファイルウィザード1/3の設定。

.TextFilePromptOnRefresh = False
すみません。分かりませんでした。教えてください。

.TextFileParseType = xlFixedWidth
元のデータの形式でデータのファイル形式を選択する。
xlDelimited ←カンマやタブなどの区切り文字によってフィールドごとに区切られたデータ
xlFixedWidth ←スペースによって右または左に揃えられた固定長フィールドのデータ
xlFixedWidthを選んで固定長フィールドのデータを選んで、次にスペース部分が自動的に区切られるかと思ったが区切られなかった。調査したがインターネットにも本でも記載されていなかった。固定長フィールドのため自分でフィールドの長さを設定しなければならない。これが分かるのに1日丸つぶれ。。

.TextFileStartRow = 1
取り込み開始行の設定。テキストファイルの1行目から取り込むことを設定。

.TextFilePlatform = 932
元のファイルの文字コードを設定。これが無いと文字化けする。(私は設定し忘れて文字化けした)
932がJapanease Shift-Jis。外部ファイル取り込みウィザードの1/3の元のファイルで文字コードの番号が書かれているので、その他の言語を取り込みたい場合は設定する。

.TextFilePlatform = xlWindows
これも分かりませんでした。名前からして、テキストファイルがWindowsプラットフォームであることを設定していると推測する。

(2)外部ファイルウィザード2/3の設定。

.TextFileParseType = xlFixedWidth or xlDelimitedの元データの形式の違いでウィザードが変わる。

[.TextFileParseType = xlFixedWidth(固定長データ)の場合]

以下の設定はいらない。

.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False

区切り文字をスペースに設定するとか、文字の引用符を設定するとかせずに、列幅の設定のみする。
列幅の設定は、データのプレビューでクリックすると線が入って、テキストを列で分割できる。
ただ、これは該当する命令が分からなかったので以下で設定した。

.TextFileFixedColumnWidths = Array(10, 2, 8, 4, 200)
固定長データのため、左側からのデータの始まりは決まっている。
左から10文字、2文字、8文字、4文字、200文字で区切って5列作成した。

[.TextFileParseType = xlDelimited(区切り文字で区切られたデータ)の場合]

どの区切り文字で列に分割するか設定。

.TextFileConsecutiveDelimiter = False
連続する文字で分割(?)。区切られなかったので分からない。

.TextFileTabDelimiter = False
タブで分割。

.TextFileSemicolonDelimiter = False
セミコロンで分割。

.TextFileCommaDelimiter = False
カンマで分割。

.TextFileSpaceDelimiter = False
スペースで分割。

.TextFileTextQualifier = xlTextQualifierDoubleQuote
文字列の引用符。
.TextFileTextQualifier = xlTextQualifierDoubleQuote ""で囲まれた物を文字列にする。
.TextFileTextQualifier = xlTextQualifierSingleQuote ''で囲まれた物を文字列にする。
.TextFileTextQualifier = xlTextQualifierNone なし。

(3)外部ファイルウィザード3/3の設定。

.TextFileColumnDataTypes = Array(5, 9, 1, 9, 2)
区切って作成した列のデータ型の設定。
1・・・標準(数値)
2・・・文字列
5・・・日付
9・・・削除(表示しない)

.TextFileTrailingMinusNumbers = True
詳細ボタンで出てくる「-」を数値のマイナス記号にするかの設定。-1000ときたら数値の「-1000」とする

以上で設定終わり。

設定した物を実行する。

.Refresh
取り込み実行。これが無いと取り込めない。私はこの命令を忘れて取り込めず苦労した。

.Parent.Names(.Name).Delete
設定したクエリ名を削除する。マクロ実行毎に作成されたクエリが保存されるのを防ぐ。
.Refresh BackgroundQuery:=Falseで定期更新は行わない設定にしておかないとエラーが出る可能性あり。

.Delete
今まで設定してきたクエリを削除する。

以上でテキストデータを外部ファイルウィザードを使用して取り込みを行った。

サンプルソースをダウンロードできます。


PS.

テキストファイルの外部データ取り込みはあまり推奨されていないようです。
本屋に行っても、上記のような命令文の解説はありませんでした。

それよりは、FSO(File System Object)を使って取り込むことを推奨しています。
コンマ区切りとか、固定長とりこみとか原理は一緒なんですけどね。

Webファイルの外部データ取り込みは少しは書かれているので、こちらが本命かなと思いました。

インターネットや本の情報が非常に少なく、苦労しました。

 

忍者Admaxのテキスト広告で収入UP!

実践!ExcelデータベースTOP