[作って]CSVを読み込むマクロ

  1. Home
  2. Excel
  3. ExcelのAI活用

【2025年9月】

先日のセミナーで「CSVを読み込むマクロ」の話題になりました。まぁ、確かにちょっと難しいですよね。なので、AI(Copilot)に作ってもらいましょう。

今回使用するCSVデータは、上図のような内容です。ファイル名は「C:\data\data07.csv」です。まずは、そっくり読み込むマクロから。

CSVファイル「C:\data\data07.csv」を、アクティブシートのセルA1から読み込むマクロを作って。CSVデータの先頭行はヘッダです。項目は「日付・名前・記号・地域・数値」です。 
Sub ImportCSVToActiveSheetA1()
    Dim ws As Worksheet
    Dim csvPath As String
    Dim qt As QueryTable

    csvPath = "C:\data\data07.csv"
    Set ws = ActiveSheet

    ' A1から既存のQueryTableを削除
    On Error Resume Next
    For Each qt In ws.QueryTables
        qt.Delete
    Next
    On Error GoTo 0

    ' CSVをA1から読み込み
    With ws.QueryTables.Add(Connection:="TEXT;" & csvPath, Destination:=ws.Range("A1"))
        .TextFileParseType = xlDelimited
        .TextFileCommaDelimiter = True
        .TextFilePlatform = xlWindows
        .TextFileStartRow = 1
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileOtherDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1) ' 全て一般
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

おおっとぉ！いきなり荒技が登場しましたね。まさかのQueryTableとは！？マクロを実行すると、下図のようになります。

まぁ確かに、プロンプトに「CSVのデータを読み込んで」とは書きませんでした。ご存じない方も多いでしょうから、ちょっと解説します。そもそもExcelには、CSVファイルなどのテキスト形式ファイルを、セルにリンクさせるという機能がありました。Power Queryじゃありませんよ。もっと大昔からある、古典的な機能です。

上図のコマンドは、通常では表示されません。コマンドを表示させるには、Excelのオプションを開き、[データ]セクションで該当する項目にチェックを入れます。

(レガシ)というのは「legacy」で"遺産"とか"古くて時代遅れの"や"旧型"などの意味です。ExcelにPower Queryが実装されてから、Microsoftは「これからはPower Queryを使ってね」と公式アナウンスし、従来のコマンドは、Excelの設定を変えなければ表示されないようにしました。要するに「もう、こいつらは、使わないでね」ってことです。とはいえ、機能としては実装され続けています。なので、使おうと思えば使えるわけです。

ただし、重要な注意があります。このマクロは、CSVファイルの読み込みではなく、CSVファイルに対する外部リンクです。リンクされているですから、CSVのファイル名や保存されているフォルダ名などを変更すると、更新したときエラーになります。

どっちがいい、じゃありません。そのマクロで、何の処理を望んでいるのかです。もし「リンクさせたい」のでしたら、上記のマクロでがんばってください。今回は、リンクではなく、データの読み込みにしたいです。

CSVファイル「C:\data\data07.csv」のデータを、外部リンクではなく、アクティブシートのセルA1から読み込むマクロを作って。CSVデータの先頭行はヘッダです。項目は「日付・名前・記号・地域・数値」です。 
ExcelのVBAマクロで、外部リンクを使わずに「C:\data\data07.csv」をアクティブシートの
A1から読み込むには、以下のようなコードが使えます。

Sub CSV読み込み()
    Dim ws As Worksheet
    Dim csvPath As String
    Dim csvLine As String
    Dim csvFile As Integer
    Dim rowNum As Long
    Dim values As Variant
    
    Set ws = ActiveSheet
    csvPath = "C:\data\data07.csv"
    csvFile = FreeFile
    Open csvPath For Input As #csvFile
    rowNum = 1
    Do While Not EOF(csvFile)
        Line Input #csvFile, csvLine
        values = Split(csvLine, ",")
        ws.Range(ws.Cells(rowNum, 1), ws.Cells(rowNum, UBound(values) + 1)).Value = values
        rowNum = rowNum + 1
    Loop
    Close #csvFile
End Sub

実行結果は、下図のとおりです。

私なりのツッコミを入れます。まず、RangeやCellsに「○○シートの～」という階層構造を指定しないと、自動的に「アクティブシートの～」になります。なので「Set ws = ActiveSheet」は不要です。このオブジェクト変数がなくなると、宣言も必要なく、すべての"ws."も削除できます。

Sub CSV読み込み()
    Dim csvPath As String
    Dim csvLine As String
    Dim csvFile As Integer
    Dim rowNum As Long
    Dim values As Variant
    
    csvPath = "C:\data\data07.csv"
    csvFile = FreeFile
    Open csvPath For Input As #csvFile
    rowNum = 1
    Do While Not EOF(csvFile)
        Line Input #csvFile, csvLine
        values = Split(csvLine, ",")
        Range(Cells(rowNum, 1), Cells(rowNum, UBound(values) + 1)).Value = values
        rowNum = rowNum + 1
    Loop
    Close #csvFile
End Sub

「csvPath = "C:\data\data07.csv"」とファイルのフルパスを変数に入れてますが、このフルパスを一度しか使わないのなら直接書けばいいです。さらに「csvFile = FreeFile」というのは、今回のOpenステートメントでファイルを開くとき、ファイルに付ける番号です。背番号みたいなもので、これ以降そのファイルを番号で指定します。もし、大量のファイルを同時に開く場合、当たり前ですが背番号が重複してはいけません。そこで、FreeFile関数を使って「現在使用可能な番号」を取得しています。てか、ひとつしか開かないんですから"1"に決まってますｗ これも不要ですね。

Sub CSV読み込み()
    Dim csvLine As String
    Dim rowNum As Long
    Dim values As Variant
    
    Open "C:\data\data07.csv" For Input As #1
    rowNum = 1
    Do While Not EOF(1)
        Line Input #1, csvLine
        values = Split(csvLine, ",")
        Range(Cells(rowNum, 1), Cells(rowNum, UBound(values) + 1)).Value = values
        rowNum = rowNum + 1
    Loop
    Close #1
End Sub

削れるのは、こんなとこかな。OpenステートメントでCSVファイルを開き、Line Input#で1行ずつ読み込みます。読み込んだ1行のデータ(例："2025/7/1,田中,A,東京,109")をSplit関数で区切り{2025/7/1,田中,A,東京,109}という配列にします。Do While Not EOF(1) は、ファイルの終端(End Of File)まで読み込むというループ処理で、1行読み込むごとに変数rowNumを増加させて、シートの下方向に読み込みます。UBound関数は、配列の最も大きい要素番号を返す関数で、配列の番号は0から始まります。したがって、UBound関数の結果は4です。シート上では、この配列(5個の要素)を1行×5列に代入したいので、1を足しています。

ということなんですが、それよりも結果をご覧になって、気になったことはありませんか？もう一度、お見せします。

分かりやすく、A列とE列の列幅を広げておきました。日付と数値が左寄せになっていますよね。これは、複数のセルに配列を一括代入すると起こる現象です。純粋な文字列として代入されたのではなく、文字列ぽく左寄せで見えているだけです。セルを編集状態にして、何もしないでEnterキーを押せば、通常の状態に戻ります。昔から、ずっとこうです。バグとは言いませんが、"手抜き感"は否めません。たぶん、この方が内部的な処理が速いでしょうね。でも、気になりますよね。最初から、標準の状態で読み込んで欲しいです。実はけっこう簡単なんですが、AIはそこまでやってくれるでしょうか。

CSVファイル「C:\data\data07.csv」のデータを、外部リンクではなく、アクティブシートのセルA1から読み込むマクロを作って。CSVデータの先頭行はヘッダです。項目は「日付・名前・記号・地域・数値」です。読み込んだ後のA列とE列が左寄せで表示されないように。 
Sub ImportCSVAndFormat()
    Dim ws As Worksheet
    Dim csvPath As String
    Dim lastRow As Long

    Set ws = ActiveSheet
    csvPath = "C:\data\data07.csv"

    ' CSV読み込み
    With ws.QueryTables.Add(Connection:="TEXT;" & csvPath, Destination:=ws.Range("A1"))
        .TextFileParseType = xlDelimited
        .TextFileCommaDelimiter = True
        .TextFilePlatform = xlWindows
        .TextFileStartRow = 1
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileOtherDelimiter = False
        .TextFileColumnDataTypes = Array(4, 1, 1, 1, 1) ' 日付・名前・記号・地域・数値
        .Refresh BackgroundQuery:=False
    End With

    ' データ範囲の最終行取得
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    ' A列を日付形式に設定
    ws.Range("A2:A" & lastRow).NumberFormat = "yyyy/mm/dd"

    ' E列を数値形式に設定
    ws.Range("E2:E" & lastRow).NumberFormat = "0.00"

    ' ヘッダ行は中央揃え
    ws.Range("A1:E1").HorizontalAlignment = xlCenter

    ' A列・E列のデータは右揃え
    ws.Range("A2:A" & lastRow).HorizontalAlignment = xlRight
    ws.Range("E2:E" & lastRow).HorizontalAlignment = xlRight
End Sub

また、QueryTableになっちゃいました。プロンプトには、ちゃんと「外部リンクではなく」って書いたのに。書式を日付形式にしたり、数値形式(しかも0.00)にしているあたり、先に書いた「Excelの手抜き」については、認識していないのでは？と思います。もう、これはいいです。次に行きます。

特定の行だけ読み込む

「名前」列が"田中"の行だけ読み込みたいです。

私だったら、こう書きます。

Dim csvLine As String, rowNum As Long, values As Variant
Open "C:\data\data07.csv" For Input As #1
rowNum = 1
''1行目(ヘッダ)の処理
Line Input #1, csvLine
values = Split(csvLine, ",")
Range(Cells(rowNum, 1), Cells(rowNum, UBound(values) + 1)).Value = values
rowNum = rowNum + 1
''2行目以降の処理
Do While Not EOF(1)
    Line Input #1, csvLine
    values = Split(csvLine, ",")
    If values(1) = "田中" Then
        Range(Cells(rowNum, 1), Cells(rowNum, UBound(values) + 1)).Value = values
        rowNum = rowNum + 1
    End If
Loop
Close #1

読み込んだデータの2列目(配列は0から始まるのでvalues(1))が"田中"と等しい、という条件分岐を追加します。ただ、そうすると1行目のヘッダも"田中"ではないので読み込まれないです。1行目のヘッダは強制的に読み込みたいので、Do Loopの繰り返しに入る前に、1行分(ヘッダ)を読み込みます。さて、AIはどうするかな？

CSVファイル「C:\data\data07.csv」のデータのうち「名前」が"田中"と等しいデータだけを、外部リンクではなく、アクティブシートのセルA1から読み込むマクロを作って。CSVデータの先頭行はヘッダです。項目は「日付・名前・記号・地域・数値」です。 
Sub ImportTanakaData()
    Dim ws As Worksheet
    Dim csvPath As String
    Dim csvLine As String
    Dim arr As Variant
    Dim outRow As Long
    Dim fso As Object, ts As Object
    
    Set ws = ActiveSheet
    csvPath = "C:\data\data07.csv"
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set ts = fso.OpenTextFile(csvPath, 1)
    
    outRow = 1
    ' ヘッダ行を読み込む
    If Not ts.AtEndOfStream Then
        csvLine = ts.ReadLine
        arr = Split(csvLine, ",")
        ws.Range("A" & outRow).Resize(1, UBound(arr) + 1).Value = arr
        outRow = outRow + 1
    End If
    ' データ行を読み込む
    Do While Not ts.AtEndOfStream
        csvLine = ts.ReadLine
        arr = Split(csvLine, ",")
        If arr(1) = "田中" Then
            ws.Range("A" & outRow).Resize(1, UBound(arr) + 1).Value = arr
            outRow = outRow + 1
        End If
    Loop
    ts.Close
End Sub

おぉ、今度はFileSystemObjectですか。まずヘッダ行を読み込んでから、2行目以降のデータを条件判定しながら読み込むと。方針は私と一緒です。ちなみにこれ、1行目(ヘッダ)かどうかも条件分岐で判定しようとすると煩雑になります。こういうのは、"アイデア勝負"ですね。さて、FileSystemObjectですが、詳しい解説を書いてありますので、よく分からない方は下記のページをご覧ください。

FileSystemObjectの解説

ここでは「FileSystemObjectとは何か？」についてだけ解説します。最初の方に出てきた「Open csvPath For Input As #csvFile」のOpenステートメントや、「Line Input #csvFile, csvLine」のLine Input#ステートメントは、VBAに備わっている「ファイルやフォルダを操作するため」の標準機能です。対してFileSystemObjectは、VBAの仕組みではなく「Windows Script Host(通称WSH)」という、VBScriptなどテキストベースのスクリプト(コード)を実行する仕組みの機能です。このFileSystemObjectも、読んで字のごとく、ファイルやフォルダなどを操作する機能です。ハッキリ言って、両者で"出来ること"に大きな違いはありません。WSHが実装されたのはWindows 98です。大昔ですね。その後、長い年月の間に紆余曲折あり「セキュリティに問題がある」などの理由で、2027年のWindowsから完全に削除されて利用できなくすると、Microsoftが公式にアナウンスしています。FileSystemObjectは、WSHが持つ複数機能の一部です。WSHが削除されるというのが「スクリプトを実行できなくする」だけで、FileSystemObjectなど別の機能は内部に残してくれるならいいですが、2027年にFileSystemObjectも使えなくなるのかは、まだ分かりません。Microsoftの判断によりますね。いずれにしても、もし、このマクロを実務で使うのなら、正常に実行できるのは、あと2年かもしれないと、その覚悟だけは持っていた方がいいでしょうね。私の個人的な予想では、たぶん使えなくなるのではないかと。

長くなったので、このへんで終わります。本当は、あと「2つのCSVファイルを結合する」や「フォルダ内の全CSVファイルを結合する」や「読み込んだ後でピボットテーブルを作る」なども試したかったのですが、それは別のコンテンツで検証します。いずれにしても、CSVファイルを読み込むマクロを作ってもらうと、次のように、いろいろなコードが提示されます。

  • QueryTable(外部リンク)
  • OpenとLine Input#(VBA標準)
  • FileSystemObject(外部機能)

今回は提示されませんでしたが、単に「CSVファイルを開いて」みたいに聞くと「Workbooks.Open」が提示されるかもしれません。いずれにしても、外部ファイルを扱うマクロは、セルやシートを操作するマクロに比べて、かなり難しいです。AIの結果を鵜呑みにせず、がんばって理解してくださいね。