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

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

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


ご案内

こちらの記事はCSVを2次元配列に格納して処理する方法を紹介していますが、後にクラスモジュールを使ったコレクションへ格納する方法も試してみました。

どちらも同じことができますが、コードの可読性とかも結構違うので比べてみてください(・ω・)ノ

構想

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

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

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

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

160901-1

ただ値を入れるだけだったら、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行目で指定のセルに書き込んでいます。

ご注意

28行目で、配列内のIDary(i, 0)とセル上のIDCells(n, 1)を比べているのですが、今回のような数値のみのIDだと、ary(i, 0)は文字列、Cells(n, 1)は数値という扱いになってしまって、一致しないと判断されてしまうので、CStr(Cells(n, 1))と、文字列変換して比べています。

結果

160901-2

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

参考

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

公開日:2016/09/01
更新日:2016/10/04

コメントを残す

メールアドレスが公開されることはありません。 が付いている欄は必須項目です

このサイトはスパムを低減するために Akismet を使っています。コメントデータの処理方法の詳細はこちらをご覧ください

コメントは承認制ですので、反映までしばらくお待ち下さい。(稀にスパムの誤判定にて届かないこともあるようですので、必要な際はお問い合わせからお願い致します。)

YouTubeでQ&Aコンテンツを企画しています

運営しているYouTubeチャンネルで、ご相談やご質問を募集しています。動画のコメントやお問い合わせページからお気軽にご相談をお寄せください。