【Excel VBA】CSVデータを読み込んでセルに記載する方法

はじめに

こんにちは。IMMRです。

CSVデータを読み込んでセルに記載して、処理を行いたいということはあると思います。

CSVを読み込んで、そのあとの処理は各々業務等によって変わってくると思います。

ここではCSVデータを読み込んでセルに記載するまでの方法を説明します。

1.CSVを読み込む

CSVデータはテキストファイルとして読み込みを行います。

そのため、ブックを読み込みを行う「workbook.open」ではなく、

テキストを読み込む「Open "C:\Users\vba.txt" For Input」の形式で読み込みを行います。

CSVデータを読み込む

Open "パス\CSVファイル名" For Input As #1
	Do Until EOF(1)
		Line Input #1, buf
	Loop
Close #1

Open "パス\csvファイル名" For Input As #1では、CSVファイル(パス\csvファイル名)をOPENします。

Do Until EOF(1)ではOPENしたファイルを1レコードずつ読み込みます。

Line Input #1, bufでは読み込んだ1レコードをbufにセットします。

CSVファイルが

1,aa,1x1

2,bb,2y2

3,cc,3z3

だとbufにセットされるのは「1,aa,1x1」「2,bb,2y2」「3,cc,3z3」となるので

bufの値をそのままセルに記載すると、1つのセルに「1,aa,1x1」となってしまいます。

「1,aa,1x1」ではなく、「1」「aa」「1x1」のように","(カンマ)区切りで代入したい場合はもう、ひと手間が必要です。

カンマで区切る

先ほどのbufを","(カンマ)で区切るにはSplit関数を使います。

これによりカンマで区切った値より配列を作成します。

tmp = Split(buf, ",")

※tmpはVariant型で宣言しています。

※(buf, ",")の，(カンマ)は、カンマで区切るという意味です。

これにより、bufの中に「1,aa,1x1」がセットされていても「1」「aa」「1x1」と区切ることが出来ます。

区切ったものは、tmp(0)、tmp(1)、tmp(2)といった形式になりました。

ちなみに一番目の「1」はtmp(0)に、セットされています。これは配列は1からではなく、0から始まるためです。

カンマで区切った配列の数

Split関数を使って","(カンマ)を区切ることが出来き、tmp(0)、tmp(1)、tmp(2)に分けること出来ます。

しかし、区切った配列がいくつあるのか、tmp(?)まであるのかがわかりません。

データによって、変わる場合は、少なかったり、多すぎたりとなってしまいます。

そういった場合はカンマで区切った配列が最大でいくつかを確認する必要があります。

そうすれば、データごとに変わっても問題がなくなります。

確認方法は以下の通りです。

UBound(tmp)

配列tmpの再列の最大数を確認出来ます。

tmp(0)、tmp(1)、tmp(2)があるとすると最大数は2です。

※最大値が2だから2個の配列があるという意味ではありません。

　配列0からカウントして、MAXが2という意味なのでtmp(0)、tmp(1)、tmp(2)　の合計3個になります。

2.CSVを読み込むサンプルコード

サンプルコード

Sub TEST33_1()
    Dim i As Long, j As Long
    Dim File_name As String
    Dim tmp As Variant
       
    With Application.FileDialog(msoFileDialogFilePicker)
        .InitialFileName = "C:\logdata"
        If .Show = 0 Then Exit Sub
        File_name = .SelectedItems(1)
    End With
    
    j = 1
    Cells(j, 1) = Dir(File_name)
    j = j + 1
        
    Open File_name For Input As #1
        Do Until EOF(1)
            Line Input #1, buf
            tmp = Split(buf, ",")
            j = j + 1
            For i = 0 To UBound(tmp)
                Cells(j, i + 1) = tmp(i)
            Next
        Loop
    Close #1

End Sub

上記のサンプルコードについて簡単に説明します。

With Application.FileDialog(msoFileDialogFilePicker)

～

End With　では、ダイアログボックスより処理に必要なファイルを選択しています。

詳細は他の記事にて説明しているのでご参照下さい。

【Excel VBA】ダイアログボックスより処理に必要なファイルを選択する方法

j = 1

Cells(j, 1) = Dir(File_name)では、Cells(1, 1)(jには1が代入されているため)に選択したファイル名を記載しています。

Open File_name For Input As #1でCSVファイル(ダイアログで選択した)を開きます。

Do Until EOF(1)では、CSVファイルを1レコードずづ最後まで読み込みます。

Line Input #1, bufでは、読み込んだ1レコードをbufに格納します。

tmp = Split(buf, ",")では","(カンマ)区切りで配列に格納します。

For i = 0 To UBound(tmp)のUBound(tmp)は先ほど","(カンマ)区切りで格納した配列の最大値を取得して、繰り返し処理をします。

Cells(j, i + 1) = "'" & tmp(i)は、先ほど","(カンマ)区切りで格納した配列全てを１つ1つセルに記載しています。　　　　　　　　　　　　　　　　

3.CSVを読み込むサンプルコードの実行

では、CSVデータを読み込みます。使用するCSVは下記の中にある「20210401_log」を選択します。

図1　CSVデータの配下

そして、CSVデータの中身はこのようになっています。

図2　CSVデータの中身

では、実行します。

使用するのは、上記であげたサンプルコードです。

図3　マクロの実行

CSVを読み込み、セルの一つ一つに記載することができました。

しかし、B列とC列がおかしくなっています。

CSVデータでは「202104010420」になっていたものが、「2.02E+11」となっております。

これはエクセル内で行われている自動の変換機能により変わってしまいます。

このようにならないためには、はじめからセルの書式を文字列などにしておくか

あるいはセルの入力時に文字列のフォーマットを変更しなくてはいけません。

セルのフォーマットを文字列にするには、

セル.NumberFormatLocal = "@"

とします。これにより、セルのフォーマットが文字列になるので

「202104010420」とセル内に記載されたとしても「2.02E+11」にはなりません。

下記のようにサンプルコード一部(Cells(j, i + 1) = "'" & tmp(i))の上に

Cells(j, i + 1).NumberFormatLocal = "@"を追加します。

        Line Input #1, buf
            tmp = Split(buf, ",")
            j = j + 1
            For i = 0 To UBound(tmp)
              Cells(j, i + 1) = tmp(i)
            Next
        Loop

↓↓↓↓↓

        Line Input #1, buf
            tmp = Split(buf, ",")
            j = j + 1
            For i = 0 To UBound(tmp)
              Cells(j, i + 1).NumberFormatLocal = "@"
              Cells(j, i + 1) = tmp(i)
            Next
        Loop

4.列幅の設定

上記で紹介したようにプログラムを修正して実行すれば、先ほどのように「2.02E+11」にならなくなります。

しかし、「202104010420」と長くなってしまうため下記のように列幅のズレが生じてしまうことがあります。

図4　列幅のズレ

このようにB列は全ての値が表示されていなかったり、C列は値がはみ出してしまったりして、

見た目がよくない結果になってしまいます。

その場合は、

Columns(列).AutoFitを使って、列幅を自動設定することができます。

詳細は、他の記事で紹介していますのでご参照下さい。

【Excel VBA】列の幅を自動設定するAutoFitメソッド

5.修正版のサンプルコード

上記でいくつか説明していきました。その修正版のサンプルコードは以下のとおりです。

Sub TEST33_2()
    Dim i As Long, j As Long
    Dim File_name As String
    Dim tmp As Variant
       
    With Application.FileDialog(msoFileDialogFilePicker)
        .InitialFileName = "C:\logdata"
        If .Show = 0 Then Exit Sub
        File_name = .SelectedItems(1)
    End With
    
    j = 1
    Cells(j, 1) = Dir(File_name)
    j = j + 1
        
    Open File_name For Input As #1
        Do Until EOF(1)
            Line Input #1, buf
            tmp = Split(buf, ",")
            j = j + 1
            For i = 0 To UBound(tmp)
                Cells(j, i + 1).NumberFormatLocal = "@"
                Cells(j, i + 1) = tmp(i)
            Next
        Loop
    Close #1
    Columns("B:C").AutoFit

End Sub

ここでは上記で説明した

セルのフォーマットを文字列にする「NumberFormatLocal = "@"」と

列の幅を自動設定する「AutoFit」を

【2.CSVを読み込むサンプルコード】で紹介したサンプルコードに追加しています。

6.修正版のサンプルコードの実行

では、再度サンプルプログラムを実行します。

図5　ブック(マクロ実行前)

手順は【3.CSVを読み込むサンプルコードの実行】と同じです。

マクロを実行するとファイルを選択するダイアログボックスが表示されます。そして、「20210401_log」を選択します。

選択すると下記のような実行結果が表示されます。

図6　ブック(マクロ実行後)

CSVデータを読み込んでセルに記載することができました。

また、セルの幅も幅ズレしないできちんと自動調整ができています。