トップ > VBA・VBS > Excel VBA > CSVを一括で取り込む/書き出す

CSVを一括で取り込む/シートをCSVで一括書き出す

Excel VBA(システムから出るCSVの取り込みと受け渡し用CSVの作成)

業務システムから出てくるCSVを、Excelで開いて、シートを分けて、並べて…。ファイルが増えるほど面倒な、この取り込み作業をマクロに任せます。ついでに逆向き(シートをCSVで書き出す)もセットにしました。現場で一番詰まるのは文字コードの違いによる文字化けなので、Shift_JISとUTF-8は設定で切り替えられるようにしてあります。

✅ これができます:フォルダ内のCSVをシート別に一括取り込みでき、シートをCSVで一括書き出しもできるようになります

完成形(実行前 → 実行後)

実行前
実行前
実行後
実行後

実行前(左)はフォルダ内のCSVファイル群。取り込みを実行すると(右)、ファイルごとにシートが分かれてExcelに取り込まれます

これをコピペすれば動きます

Option Explicit
 
' ===== 設定エリア:変えるのはここだけ =====
' ① CSVが入っているフォルダ(書き出し先も同じフォルダです)
Const CSV_FOLDER As String = "C:\仕事\CSV"
' ② 文字コード("shift_jis" か "utf-8"。日本の業務システムは shift_jis が多め)
Const CHARSET As String = "shift_jis"
' ③ 書き出すシート名
Const EXPORT_SHEET As String = "データ"
' ④ 書き出すファイル名
Const EXPORT_NAME As String = "export.csv"
' ===== ここから下は変えなくてOK =====
 
Sub CSVを一括取り込み()
    Dim fso As Object, folder As Object, file As Object
    Dim st As Object, ws As Worksheet
    Dim text As String, lines As Variant, ln As Variant, cells As Variant
    Dim r As Long, c As Long, cnt As Long
    Dim sheetName As String
 
    Set fso = CreateObject("Scripting.FileSystemObject")
    If Not fso.FolderExists(CSV_FOLDER) Then
        MsgBox "フォルダが見つかりません:" & CSV_FOLDER, vbExclamation, "CSV取り込み"
        Exit Sub
    End If
    Set folder = fso.GetFolder(CSV_FOLDER)
 
    cnt = 0
    For Each file In folder.Files
        If LCase(fso.GetExtensionName(file.Name)) = "csv" And file.Name <> EXPORT_NAME Then
            sheetName = シート名に使える形に(fso.GetBaseName(file.Name))
            Set ws = シートを用意する(sheetName)
            ws.Cells.Clear
            ' 文字列として取り込む(先頭の 0 や、= で始まる値が化けないように)
            ws.Cells.NumberFormat = "@"
            Set st = CreateObject("ADODB.Stream")
            st.Type = 2
            st.Charset = CHARSET
            st.Open
            st.LoadFromFile file.Path
            text = st.ReadText
            st.Close
            Set st = Nothing
            lines = Split(Replace(Replace(text, vbCrLf, vbLf), vbCr, vbLf), vbLf)
            r = 1
            For Each ln In lines
                If Len(Trim(CStr(ln))) > 0 Then
                    cells = Split(CStr(ln), ",")
                    For c = 0 To UBound(cells)
                        ws.Cells(r, c + 1).Value = cells(c)
                    Next c
                    r = r + 1
                End If
            Next ln
            cnt = cnt + 1
        End If
    Next file
    MsgBox cnt & " 件のCSVを取り込みました。", vbInformation, "CSV取り込み"
End Sub
 
Sub シートをCSVで書き出す()
    Dim ws As Worksheet, st As Object
    Dim lastRow As Long, lastCol As Long
    Dim r As Long, c As Long
    Dim line As String, text As String, path As String
 
    Set ws = ThisWorkbook.Worksheets(EXPORT_SHEET)
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
    text = ""
    For r = 1 To lastRow
        line = ""
        For c = 1 To lastCol
            If c > 1 Then line = line & ","
            line = line & CStr(ws.Cells(r, c).Value)
        Next c
        text = text & line & vbCrLf
    Next r
 
    path = CSV_FOLDER & "\" & EXPORT_NAME
    Set st = CreateObject("ADODB.Stream")
    st.Type = 2
    st.Charset = CHARSET
    st.Open
    st.WriteText text
    st.SaveToFile path, 2
    st.Close
    MsgBox "CSVを書き出しました:" & path, vbInformation, "CSV書き出し"
End Sub
 
Private Function シート名に使える形に(ByVal s As String) As String
    Dim bad As Variant, b As Variant
    bad = Array("\", "/", "?", "*", "[", "]", ":")
    For Each b In bad
        s = Replace(s, CStr(b), "_")
    Next b
    If Len(s) > 31 Then s = Left(s, 31)
    If s = "" Then s = "無題"
    シート名に使える形に = s
End Function
 
Private Function シートを用意する(ByVal sheetName As String) As Worksheet
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name = sheetName Then
            Set シートを用意する = ws
            Exit Function
        End If
    Next ws
    Set ws = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))
    ws.Name = sheetName
    Set シートを用意する = ws
End Function

コードの黄色い部分が「設定エリア」です。変えるのは、原則ここだけです。

変えるのはここだけ

変える場所を示した図
変えるのは設定エリアの4か所だけ。番号は下の表と対応しています
番号変える場所意味書き方・例
1CSV_FOLDERCSVが入っているフォルダ(書き出し先も同じ)自分のPCの場所に書き換えます。取り込みでは、この中の .csv が全部対象になります
2CHARSET文字コード"shift_jis" か "utf-8"。文字化けしたら、もう一方に変えて再実行するのが切り分けの近道です
3EXPORT_SHEET書き出すシート名CSVにしたいシートを指定します
4EXPORT_NAME書き出すファイル名同じ名前がある場合は上書きされます。このファイル自体は、取り込みの対象から自動で外れます

組み込み手順

  1. 取り込みたいCSVを、①のフォルダに集めておきます
  2. Alt+F11 でVBAエディタを開き、「挿入」→「標準モジュール」を選びます
  3. このページのコードを貼り付け、設定エリア①②を書き換えます(書き出しも使う場合は③④も)
  4. ファイルを .xlsm で保存し、「CSVを一括取り込み」のマクロを実行します
  5. ファイル名のシートができ、中身が文字化けしていなければ成功です。化けていたら②を変えて再実行してください
  6. 書き出しは「シートをCSVで書き出す」のマクロを実行します

⚠ VBAエディタの開き方:Alt+F11 で開きます。「開発」タブが出ていない場合は、ファイル→オプション→リボンのユーザー設定で「開発」にチェックを入れると出ます。

⚠ 保存形式:マクロは「Excel マクロ有効ブック(.xlsm)」で保存しないと、次に開いたときに消えています。いつもの .xlsx で保存すると、マクロだけ取り除かれます。

⚠ セキュリティ警告バー:開いたときに黄色いバーが出たら「コンテンツの有効化」を押してください。押すまではマクロは動きません。

⚠ ファイルのブロック解除:ネットから入手したファイルは、開く前に「右クリック→プロパティ→『許可する』にチェック」が先です。これをしないと、有効化のボタン自体が出ないことがあります。

⚠ 会社の設定で無効化されている場合:警告バーも出ずに無反応なときは、会社の設定でマクロが止められている可能性が高いです。設定を突破しようとせず、情シスに「業務でマクロを使いたい」と相談するのが正解です。

動かし方

取り込んだシートは、実行のたびに作り直されます(同じ名前のシートがあれば中身を入れ替えます)。書き出しは同名ファイルを上書きします。どちらも「再実行で最新化」できる作りです。

カスタマイズ例

数字を数字として取り込みたい

取り込みは全セル「文字列」として行います(先頭の0や、= で始まる値が化けるのを防ぐため)。金額の列など、計算に使う列が決まっている場合は、取り込み後にその列だけ数値化する処理を足すか、Excel側で「区切り位置」や VALUE 関数で整えてください。

TSV(タブ区切り)にも対応したい

Split の区切り文字 "," をタブに変えると、タブ区切りのファイルにも使えます。拡張子の判定も tsv に広げてください。

取り込んだ後に集計まで一気に

取り込んだシートを「集計ボタン」のデータシートに指定すれば、取り込み→集計の流れができます。

つまずきどころ

関連レシピ

次はこれ:フォルダ内の複数ブックを開いて1つに統合する
CSVの次は、Excelファイルそのものの統合も自動化しましょう

よくある質問

Q. Excelで直接CSVを開くのと何が違う?

A. 直接開くと、文字コードの自動判定が外れたときに化けたり、ファイルごとに操作が必要だったりします。このレシピは、文字コードを固定したうえで、複数ファイルをシート分けまで一気にやる点が違います。

Q. .xls形式のファイルも取り込める?

A. このレシピの対象はCSVだけです。Excelファイルの統合は「フォルダ内の複数ブックを開いて1つに統合する」のレシピを使ってください。

Q. 書き出したCSVを相手システムが読み間違える

A. 文字コードと改行の形式が原因のことが多いです。まずは②の文字コードを相手の指定に合わせてください。

PR(広告)

もっと体系的に学びたい人へ
自動化の幅を広げるなら、講座で体系化するのが近道です。

Udemy:Excelショートカット講座を見る