ExcelVBAでCSVファイルを2次元配列に格納してから処理する

VBAでCSVファイルをExcelへ読み込むということは結構あると思うのですが、CSVのデータを一旦2次元配列に格納しておいてからの処理という場面に遭遇したのでメモっておきます。

構想

こんな形のCSVファイルがあるとします。

ID,名称,値
83008,H,500
83012,L,150
83003,C,340

今回欲しいのはIDと値だけなんですが、まぁこういう形だったとして。

こいつを、ExcelのこんなシートのA列のIDと照合して一致するF列にCSVの値を入れるとします。

ただ値を入れるだけだったら、CSVファイルを1行ずつ見ていってシートのID位置を探して書き込んでおしまいなのですが、今回はCSV側に値がないものには、Excel側にはゼロを入れる処理も加えたいとします。

そうすると、一度CSVファイルを読み込んで配列に格納しておいてから、Excelシートのデータを1行ずつ見ていって、配列の中身と照合して、CSV側に値があればそれを、なければゼロをExcel側に書き込んでいく、という処理になるのかなと。

コード

Sub Sample()
  Dim file As String, max_n As Long
  Dim buf As String, tmp As Variant, ary() As Variant
  Dim i As Long, n As Long, val As Long
  
  '準備
  file = "C:\test.csv" 'ファイル指定
  max_n = CreateObject("Scripting.FileSystemObject").OpenTextFile(file, 8).Line 'ファイルの行数取得
  ReDim ary(max_n - 1, 2) As Variant '取得した行数で2次元配列の再定義
  
  'CSVファイルを配列へ
  Open file For Input As #1 'CSVファイルを開く
  Do Until EOF(1) '最終行までループ
    Line Input #1, buf '読み込んだデータを1行ずつみていく
    tmp = Split(buf, ",") 'カンマで分割
    For i = 0 To UBound(tmp) '項目数ぶんループ
      ary(n, i) = tmp(i) '分割した内容を配列の項目へ入れる（0→ID, 1→名称, 2→値）
    Next i
    n = n + 1 '配列の次の行へ
  Loop
  Close #1 'CSVファイルを閉じる
  
  'シートの処理
  n = 2 '2行目からスタート
  Do Until Cells(n, 1) = "" 'A列が空白になるまでループ
    val = 0 '初期値セット
    For i = 0 To max_n - 1 '配列の行数ぶんループ
      If ary(i, 0) = CStr(Cells(n, 1)) Then '配列とA列のIDが一致したら
        val = ary(i, 2) '値を上書き
        Exit For 'forを抜ける
      End If
    Next i
    Cells(n, 5) = val '指定セルに入れる
    n = n + 1 'シートの次の行へ
  Loop
  
End Sub

解説

今回、CSVデータを格納するのに2次元配列を使います。配列名(行数, 列[項目]数)と指定するのですが、項目数はID・名称・値の3つと決まっていて、行数は毎回違うものとします。

従って、8行目でまず指定のCSVファイルの行数を取得し、9行目で配列を再定義します。配列の数はゼロからはじまるので、どちらも -1 した数です。

12～21行目で、CSVファイルを開いて配列に格納しています。15行目は、CSVファイルがカンマ区切りの例なので適宜変更してください。

24～35行で、格納した配列データをシートと照合して書き込んでいきます。Excel側のデータを1行ずつ見ていって、IDを配列の0列目と照合し、一致したら2列目の値を、なければゼロを変数valに入れておいて、最終的に33行目で指定のセルに書き込んでいます。

結果

実行すると、このようになります。

参考

• CSVファイルを読み込む：Excel VBA｜即効テクニック｜Excel VBAを学ぶならmoug
• テキストファイルの行数を瞬時に取得 – VBレスキュー(花ちゃん)　Visual Basic,VS6.0,VB6.0,サンプル,Tips

ありがとうございました！