VBAを使ってExcelにCSVファイルを読み込んでみた¶
定型のCSVファイルから定型のExcelファイルを作ることがあります。いちいちCSVファイルをExcelで開いて書式を直して・・・というのが面倒なので、VBAで読み込んでみました。
試した環境は下記です。
Excel 2016
目次
- VBAではTextFieldParserが使えない？¶
- ExcelでCSVファイルを読み込む¶
- CSVファイルをブックとして直接読む¶
- CSVファイルをテキストファイルとして読み込む¶
- QueryTableを使う¶
- TextFileParseTypeプロパティ¶
- TextFileCommaDelimiterプロパティ¶
- TextFileColumnDataTypesプロパティ¶
- RefreshStyleプロパティ¶
- Refreshメソッド¶
- Deleteメソッド¶
- 試してみた¶
VBAではTextFieldParserが使えない？¶
Visual Basic .NETには、CSVのような特定の書式のデータを読み込むのに便利な、TextFieldParserというクラスがあります。ところが、Excel 2016のVBAでは使えないようです。
ExcelでCSVファイルを読み込む¶
TextFieldParserを使わないとすると、どうすれば良いのでしょうか。
CSVファイルをブックとして直接読む¶
おそらく一番簡単な方法です。 CSVファイルを指定してブックを開くだけです。ブックとして開いた後に、あれこれセルを操作すれば良いのです。
この方法で困るのは、列の書式が指定できないことです。例えば、文字列を文字列として読むのか数値として読むのかが、Excel任せになってしまうのです。もっと具体的にいうと、「001」は1になってしまいますし、「1e00」も1になってしまいます。
書式の判別は用途によるので、「りんな」がどんなに成長しても上手くいかないかもしれません。
CSVファイルをテキストファイルとして読み込む¶
CSVファイルはテキストファイルの一つの書き方ですので、テキストファイルとして読み込んで、自力でパースします。
具体的には、一行ずつ読み込んでカンマでsplitするわけです。そして、セルに書き込むときにセルの書式を指定すれば、希望の書式に対応できます。コードが長くなると思いますが、とてもわかりやすい方法です。
問題は、データの中にカンマがある場合はどうするかということです。
QueryTableを使う¶
クエリを使って、CSVファイルをデータソースとして読み込みます。
下記のコードは、テンプレートファイルを開いて、CSVファイルの中身を開いたシートに書き込んでみたものです。
Sub CsvReadTrial()
Dim fn As String
fn = "C:\hogehoge\testdata.csv"
Workbooks.Open "C:\hogehoge\trial.xltx"
Dim ws As Worksheet
Set ws = ActiveSheet
Dim qt As QueryTable
Set qt = ws.QueryTables.Add(Connection:="TEXT;" & fn, Destination:=ws.Range("A2"))
With qt
.TextFileParseType = xlDelimited '文字で区切った形式
.TextFileCommaDelimiter = True '区切り文字はカンマ
.TextFileColumnDataTypes = Array(xlSkipColumn, xlGeneralFormat, xlTextFormat, xlTextFormat, xlGeneralFormat, xlGeneralFormat) '各列の書式
.RefreshStyle = xlOverwriteCells '上書きを指定
.Refresh '上書き
.Delete '切断
End With
End Sub
QueryTables.Addメソッドは、クエリテーブルを作成するメソッドです。このメソッドの第１引数にはデータソース、第２引数にはデータの配置先の左上端のセルを指定します。
QueryTableにはいくつかプロパティがあります。
TextFileParseTypeプロパティ¶
データが固定幅で記載されたテキストファイルなのか、特定の文字でデータを区切ったテキストファイルなのか指定します。
指定値 |
内容 |
|---|---|
xlFixedWidth |
データが固定幅で配置されたテキストファイル |
xlDelimited |
データが特定の文字で区切られたテキストファイル |
TextFileCommaDelimiterプロパティ¶
区切り文字がカンマの場合は、trueにします。タブ区切りの場合は、TextTabDelimiterプロパティをtrueにします。
TextFileColumnDataTypesプロパティ¶
各列の書式を配列で指定します。
指定値 |
内容 |
|---|---|
xlGeneralFormat |
一般 |
xlTextFormat |
テキスト |
xlYMDFormat |
YMD形式の日付 |
xlSkipColumn |
スキップする列 |
RefreshStyleプロパティ¶
シートへの書き込み方法を指定します。
指定値 |
内容 |
|---|---|
xlOverwriteCells |
上書きします |
xlInsertDeleteCells |
|
xlInsertEntireRows |
Refreshメソッド¶
クエリを実行します。
Deleteメソッド¶
クエリオブジェクトを削除します。 データソースとの接続を切断するわけですね。
試してみた¶
下記のCSVファイルの2列目以降を、テンプレートファイルのA2セル以下に書き込んでみます。
用意したCSVファイルはこれです。
A001,1,"A001",1A00,1,1
B002,2,"B002",2B00,1,1
C003,1,"C003",1C00,1,1
D004,2,"D004",2D00,2,1
E005,3,"E005",3E00,1,1
F006,1,"F0,06",1F00,3,1
G007,2,"G007",2G00,1.0123456,1
H009,2,"H009",2H00,1,5
カンマを含む要素があったり、ダブルクォーテーションが付いていたり・・・面倒なデータですね。
テンプレートファイルはこんな感じです。
拡張子をxltsにして保存します。これを開くと上書き保存できないので、他人に使わせるには都合良いのです。
VBAのコードは前述のものです。VBAのコードを含むファイルはxlsmという拡張子で保存します。
xlsmファイルを開いて、マクロを実行します。
そうすると、下記のようなウィンドウが開きます。
テンプレートファイルに書き込まれてますね。このファイルは、Ctrl+Sで上書き保存使用すると、ファイル名の入力を要求されます。（上書きできない。）
公開日