• ホーム
  • Office
  • VBAを使ってExcelにCSVファイルを読み込んでみた

VBAを使ってExcelにCSVファイルを読み込んでみた

定型のCSVファイルから定型のExcelファイルを作ることがあります。いちいちCSVファイルをExcelで開いて書式を直して・・・というのが面倒なので、VBAで読み込んでみました。

試した環境は下記です。

  • Excel 2016

目次

  1. VBAではTextFieldParserが使えない?
  2. ExcelでCSVファイルを読み込む
    1. CSVファイルをブックとして直接読む
    2. CSVファイルをテキストファイルとして読み込む
    3. QueryTableを使う
    4. TextFileParseTypeプロパティ
    5. TextFileCommaDelimiterプロパティ
    6. TextFileColumnDataTypesプロパティ
    7. RefreshStyleプロパティ
    8. Refreshメソッド
    9. Deleteメソッド
  3. 試してみた

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メソッドは、クエリテーブルを作成するメソッドです。このメソッドの第1引数にはデータソース、第2引数にはデータの配置先の左上端のセルを指定します。

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

カンマを含む要素があったり、ダブルクォーテーションが付いていたり・・・面倒なデータですね。

テンプレートファイルはこんな感じです。

image0

拡張子をxltsにして保存します。これを開くと上書き保存できないので、他人に使わせるには都合良いのです。

VBAのコードは前述のものです。VBAのコードを含むファイルはxlsmという拡張子で保存します。

xlsmファイルを開いて、マクロを実行します。

image1

そうすると、下記のようなウィンドウが開きます。

image2

テンプレートファイルに書き込まれてますね。このファイルは、Ctrl+Sで上書き保存使用すると、ファイル名の入力を要求されます。(上書きできない。)

公開日