ダイアログボックスで選択したフォルダ内全csvをエクセルブックとして取込み 別ファイルにまとめる（「,」「”」対応）

【本日のミッション】

Excel VBA ダイアログボックスで選択したフォルダ内全てのcsvを、エクセルブックとして取込み、別ファイルにまとめて保存せよ。

■初心者向け
■Workbooks.Open メソッド使用
■カンマ「,」ダブルクォーテーション「”」対応
■「ゼロ落ち」（0から始まる文字列の0が消える）非対応
■「日付変換」（文字列が日付に変換される）非対応
■「桁落ち」（桁数の多い数値の16桁以降0になる）非対応
■処理速度が遅い

ミッションの概要

Excel VBA ダイアログボックスで選択したフォルダ内全てのcsvを、エクセルブックとして取込み、別ファイルにまとめて保存せよ、というのが今回のミッションです。

■初心者向け
■Workbooks.Open メソッド使用
■カンマ「,」ダブルクォーテーション「”」対応
■「ゼロ落ち」（0から始まる文字列の0が消える）非対応
■「日付変換」（文字列が日付に変換される）非対応
■「桁落ち」（桁数の多い数値の16桁以降0になる）非対応
■処理速度が遅い

非対応の文字がありますが、保存後のデータを確認し、おかしいところがないか確認し、問題なければOKです♪

今回は気象庁 過去の気象データを使用させて頂きました。

気象庁 過去の気象データ・ダウンロード

ダイアログボックスで選択したフォルダ内のcsvを順に、ExcelのWorkbooks.Open メソッドで開き、別ファイルにまとめて保存します。

貼り付け用sheetには、あらかじめ項目部分を作成しておきます。その下にcsvのデータ部分をつなげていきます。

【処理の流れ】

1. ダイアログから、csvの入っているフォルダを選択。
    
2. 選択したフォルダ内の、csvファイルを順にOPEN。
   
3. csvファイルのデータを、配列ary_dに取込。
4. 配列ary_dのデータを、貼付用シート「Sheet1」に貼付。
5. csvファイルをCLOSE。→次のcsvの処理「2.」へ戻る。
6. 選択したフォルダ内の、全csvの処理が完了したら、貼付用シート「Sheet1」を新規ブックにコピーします。
7. csvの入っていたフォルダの中に、「結合.xlsx」という名前で保存して終了。
    

プロシージャ

Sub CSV取込9()

    '【変数】
    Dim pt As Variant       'フォルダパス
    Dim fn As Variant       '取込ファイル名
    Dim ws_h As Worksheet   'データ貼付用シート（このブックのsheet1）
    Dim wb_d As Workbook    'データ取込ブック
    Dim ws_d As Worksheet   'データ取込シート
    Dim ary_d As Variant    'データ取込用配列
    Dim e_row_d As Long     'データ取込シート 最終行
    Dim e_col_d As Long     'データ取込シート 最終列
    Dim s_row_h As Long     'データ貼付用シート 開始行

    '■画面更新STOP
    Application.ScreenUpdating = False

    '■変数セット
    Set ws_h = ThisWorkbook.Worksheets("Sheet1")  'データ貼付用シート（このブックのSheet1）
    s_row_h = 4     'データ貼付シート 開始行  
  
    '■フォルダの選択
    With Application.FileDialog(msoFileDialogFolderPicker)
        .InitialFileName = "C:\"    '初期表示パス
        .AllowMultiSelect = False   '複数選択不可
        .Title = "フォルダの選択"    'ダイアログボックスタイトル
        
        If .Show = 0 Then
            Exit Sub                'フォルダ選択されなかった場合は終了
        End If

        'フォルダを変数ptにセット
        pt = .SelectedItems(1)
    End With

    '■変数fnに1個目のファイル名を格納
    fn = Dir(pt & "\*.csv")
    
    '■フォルダ内のファイルを順に開く
    Do While fn <> ""              'fnが空欄になるまでDo While内の処理を続ける
        Workbooks.Open Filename:=pt & "\" & fn

        Set wb_d = ActiveWorkbook       'データ取込ブック
        Set ws_d = ActiveSheet          'データ取込シート

        '■最終行・最終列の取得
        e_row_d = ws_d.Cells(ws_d.Rows.Count, 1).End(xlUp).Row              'データ取込シート 最終行
        e_col_d = ws_d.Cells(3, ws_d.Columns.Count).End(xlToLeft).Column    'データ取込シート 最終列

        '■データ取込シート→配列ary_d
        ary_d = ws_d.Range(ws_d.Cells(6, 1), ws_d.Cells(e_row_d, e_col_d))
        
        '■配列ary_d→データ貼付用シート（このブックのsheet1）
        ws_h.Range(ws_h.Cells(s_row_h, 1), ws_h.Cells(UBound(ary_d, 1) + s_row_h - 1, e_col_d)) = ary_d

        '■データ貼付用シート開始行変更
        s_row_h = s_row_h + UBound(ary_d, 1)

        '■ファイルを閉じる
        wb_d.Close SaveChanges:=False

        '■変数fnに次のファイル名を格納（ファイルが無い場合は空欄になる）
        fn = Dir()
    Loop
   
    '■Sheet1シートを新規ファイルにコピー
    ws_h.Copy
    
    '■ファイル保存（ダイアログ選択したフォルダ内）
    ActiveWorkbook.SaveAs Filename:=pt & "\結合.xlsx"
 
    '■画面更新STOP解除
    Application.ScreenUpdating = True
End Sub

csvとは

Comma-Separated Values の略称です。

データがカンマ「,」で区切られている、拡張子が「csv」のファイルです。

カンマ「,」で区切られたデータ1行で1レコードとなり、レコードとレコードの間は改行で区切られます。

csvをExcel Workbooks.Open メソッドで開ける時のデメリット

今回のcsv取込方法は、どのcsvでも使えるわけではありません。

例えば、こんなcsvファイルを取り込んでみると・・・・

↓取り込んだ後のデータが、全く違うものになっています。

csvをExcelのWorkbooks.Open メソッドで開く時は、次のことにご注意ください。

「01-01」などの文字列が 日付データに変換される

上記例でいうと、元データ「01-01」が日付「2023/1/1」に変換されます。カレンダーに存在する「月」「日」の組み合わせの数値が、ハイフン「-」でつながっている場合に注意が必要です。

「０」から始まる文字列の「0」が消える

上記例でいうと、元データ「01」が「1」になります。
Excelのセルに「01」と入力して、Enter押したら「1」に自動的に変換されますよね。Excelは数字情報を「数値」だと判断してしまうんですね。

桁数の多い数字の16桁以降「0」になる

桁数の多い数値の16桁目以降が「0」になります。
また「E+」のような表記になります。

処理スピードが遅い

後日ご説明させていただく「Open ステートメント」での読み込みに比べると、格段に処理スピードが遅いです。

csvをExcel Workbooks.Open メソッドで開ける時のメリット

Excelブックとして開くことのメリットもあります。

↓csvデータの難点である、カンマ「,」ダブルクォーテーション「”」を正しく取り込んでいます。

カンマ「,」ダブルクォーテーション「”」対応

上記例でいうと「”x,y,z”」のような、ダブルクォーテーション「”」で囲まれたカンマ「,」を含む文字列が正しく取り込まれます。

数値のカンマは消えていますが、数値の内容としては問題ありません。

初心者に理解しやすいコード

Workbooks.Open メソッドはVBA初心者が、初期段階で学ぶコードです。
「Open fn For Input As #1」ってナニ？「Line Input #1, Rcd」なんて理解できな！という方におすすめです。

FileDialogオブジェクト

ファイルを開いたり保存する標準的な [ファイルを開く] および [保存] ダイアログ ボックスに類似する、ダイアログボックスの機能を提供します。

Application.FileDialog(fileDialogType)

「fileDialogType」には、下記定数のいずれかを指定します。

今回はフォルダを選択するため、「msoFileDialogFolderPicker」を指定しています。

Application.FileDialog(msoFileDialogFolderPicker)

FileDialogオブジェクトには、色々なプロパティやメソッドがあります。その中で、今回使用しているプロパティ、メソッドをご紹介しておきます。

FileDialog.InitialFileNameプロパティ

ダイアログボックスに最初に表示するパスやファイル名を、設定または返します。
文字列型 (String) の値を使用します。

今回は、Cドライブが最初に表示されるように設定しています。

FileDialogオブジェクト.InitialFileName = "C:\"

FileDialog.AllowMultiSelectプロパティ

複数ファイル選択の可・不可を指定します。

今回は、複数選択不可とするために「False」を指定しています。

FileDialogオブジェクト.AllowMultiSelect = False

FileDialog.Titleプロパティ

ダイアログボックスのタイトルを、設定または返します。

FileDialogオブジェクト.Title = "フォルダの選択"

FileDialog.Showメソッド

ダイアログボックスを表示し、[OK][開く][保存]がクリックされた場合は「-1」を、「キャンセル」がクリックされた場合は「0」を返します。

FileDialogオブジェクト.Show

今回は、「キャンセル」(0)がクリックされたら、マクロ処理を終了するようにしています。

If .Show = 0 Then
    Exit Sub 
End If

FileDialog.SelectedItemsプロパティ（インデックス番号）

FileDialogSelectedItemsコレクションを取得します。
このコレクションには、上記 Showメソッドによって表示されたダイアログボックスで、ユーザーが選択したファイル・フォルダパスの一覧が含まれます。 値の取得のみ可能です。

今回は「AllowMultiSelect = False」として、複数ファイル選択を不可としているため、インデックス番号は「1」になります。また、Cドライブの中の「csv」フォルダを選択したため、変数ptには「C:\csv」が入ります。

pt = FileDialogオブジェクト.SelectedItems(1)

Dir関数

Dir関数は、第1引数に指定したファイル・フォルダが存在する場合、パスを除いたファイル名・フォルダ名を返します。存在しない場合は長さ0の文字列 ”” （空欄）を返します。

Dir（ファイル・フォルダのパス,ファイル・フォルダの属性）

第1引数にはワイルドカードを使用することができるので、取得したいファイル名・フォルダ名の幅を広げることができます。

参考：ワイルドカードとは。使い方いろいろ。（Excel）

Dir(“C:\csv\*.*”) の場合

• ファイルが存在する場合（ファイル名を返す）→ 202304.csv
• ファイルが存在しない場合（空欄を返す）      →  ””

第2引数に下記定数（値）を指定することで、特定の属性のオブジェクトのみを取得の対象とすることができます。

フォルダ名を取得したい場合は、第2引数に「vbDirectory」を指定します。

Dir関数でのフォルダ名取得は少し複雑ですので、もう少し詳しいことが知りたい方はコチラをご参考ください。→Dir関数の使い方。ファイル名やフォルダ名の取得方法。（Excel VBA）

'■指定したフォルダパスが存在するか確認
If Dir(pt, vbDirectory) = "" Then
    MsgBox "ご指定のフォルダパスは存在しません。"
    Exit Sub
End If

Dir関数は一度引数を指定すると、次に別の引数を指定するまで、同じ引数に対する処理を行います。

fn =Dir("C:\csv\*.*"）
fn =Dir()
fn =Dir()

1行目の処理では、変数 fn に「C:\csv\*.*」に一致するファイルが存在すれば、そのファイル名を返します。

2行目以降の「fn=Dir()」では引数を指定していませんが、「C:\csv\*.*」に一致する別のファイル名を返します。

その処理を繰り返し、「C:\csv\*.*」に一致するファイルが無くなった時、長さ0の文字列 ”” 空欄 を返します。

何度も「fn=Dir()」を書くのは面倒だし、どのタイミングでファイルが無くなるかわからないので、Do While ～ Loopを使って繰り返し処理を行います。

'■指定フォルダパス内のファイル名の取得が終わるまでLoop
fn = Dir(pt & "\*.*") '指定フォルダパス内の1個目のファイル名を取得
Do While fn <> ""
    Workbooks.Open Filename:=pt & "\" & fn
    ActiveWorkbook.Close SaveChanges:=False
    fn = Dir()             '指定フォルダパス内の次のファイル名を取得
Loop

Workbooks.Open メソッド

引数がたくさんあるメソッドですが、今回は引数「Filename」で指定しているファイル「fn」を開く、というだけの動作のみです。

Workbooks.Open Filename:=fn

Workbooks.Open(FileName [, UpdateLinks] [, ReadOnly] [, Format] [, Password]
 [, WriteResPassword] [, IgnoreReadOnlyRecommended] [, Origin] [, Delimiter]
 [, Editable] [, Notify] [, Converter] [, AddToMru] [, Local] [, CorruptLoad] )

最終行・最終列の取得

最終行・最終列の取得には、「Endプロパティ」を使用します。

Rangeオブジェクト.End(方向)

e_row_d = ws_d.Cells(ws_d.Rows.Count, 1).End(xlUp).Row

ws_d.Cells(ws_d.Rows.Count, 1)→「セルA1048576」のことです。
「セルA1048576」からCtrl＋↑をすると「セルA725」を選択しますよね。

その「セルA725」の行番号「725」を変数e_row_dに入れています。

e_col_d = ws_d.Cells(3, ws_d.Columns.Count).End(xlToLeft).Column

ws_d.Cells(3, ws_d.Columns.Count)→セル「XFD3」のことです。

「XFD3」からCtrl＋←をすると「セルM3」を選択しますよね。

その「セルM3」の列番号「13」を変数e_col_dに入れています。

列番号「13」はA→B→C→D→E ・・→L→Mの 13番目の列ということです。

表の最終セルの指定

これらのe_row_dと、e_col_dを使って

ws_d.Cells(e_row_d, e_col_d)

とすることで、「セルM725」を指定することができます。

ws_d.Range(ws_d.Cells(6, 1), ws_d.Cells(e_row_d, e_col_d))

あとは表の左上の「セルA6」（ws_d.Cells(6, 1)）から表の右下の「セルM725」（ws_d.Cells(e_row_d, e_col_d)）で表の範囲を指定することができます。

バリアント型(Variant)変数を配列として使用する

配列って難しくないですか？

• 添え字が0から始まる。
• モジュールの先頭に「Option Base 1」 としておくと、配列の添え字の最小値を１にすることができる。
• 静的配列は、配列変数宣言時に要素数を指定する必要がある。

などなど、他にも色々ありますが・・・。学べば学ぶほど奥の深いものです。

そんな学び無しに、とりあえず実業務でパパっと使えるのが

「バリアント型(Variant)変数を配列として使用する」

なのです。

変数「ary_d」を、バリアント型(Variant)で宣言します。
データ型を省略すると、勝手にバリアント型(Variant)になります。

Dim ary_d

バリアント型変数に、セル範囲をそのまま入れちゃいます。

ary_d = ws_d.Range(ws_d.Cells(6, 1), ws_d.Cells(e_row_d, e_col_d))

この処理で対象セル範囲のテキスト・数字が、バリアント型変数 ary_d に格納され、配列になりました。（以下、配列ary_d と呼ばせていただきます。）

通常、配列は添え字が「0」から始まりますが、この方法で格納した場合、添え字は「1」から始まります。

添え字の始まりが「0」というのが苦手な方にはうれしいですよね。

下記例は、セル範囲B2～C12を配列ary_dに格納した時の、ary_dのインデックス番号を示しています。添え字が「1」から始まってますね。

この例のデータを配列ary_dに格納した後に

MsgBox ary_d(5, 1)

とすると、格納した表の5行目1列目の「パイナップル」が表示されます。

配列ary_dに格納した後は、自由に配列内のデータを使用することができます。セル上のデータを処理するよりも格段にスピードアップです(#^.^#)

今回の処理で配列ary_dに入れた、今回はそのまま別のセルに貼り付けています。

ws_h.Range(ws_h.Cells(s_row_h, 1), ws_h.Cells(UBound(ary_d, 1) + s_row_h - 1, e_col_d)) = ary_d

これまた簡単な処理ですね。

UBound関数

配列（ArrayName）で使用できる最大の添え字を返します。

UBound (ArrayName , Dimension)

UBound(ary_d, 1)

配列ary_dの1次元には、ary_d(1)～ary_d(720)までの要素が入っています。2次元には、ary_d(1)～ary_d(13)までの要素が入っています。
「添え字」とは、配列ary_d()の括弧内の数字のことです。
今回は1次元配列なので、第2引数の「添え字の上限を調べたい配列の次元（Dimension）」を「1」としています。

1次元の添え字は1～720なので、最大値は UBound(ary_d, 1) = 720 になります。

複数データを繋げる方法

項目部分の下4行目からcsvデータを貼り付けています。

貼り付け開始行 s_row_h の初期値を「4」としています。データ部分の行数はUBound(ary_d, 1)になりますので、2つ目以降のcsvの開始行は「s_row_h + UBound(ary_d, 1)」で求めることができます。

貼り付け終了行は、開始行 s_row_h とデータ部分行数UBound(ary_d, 1)の和から1を引いたものになります。「UBound(ary_d, 1) + s_row_h – 1」

s_row_h = 4
ws_h.Range(ws_h.Cells(s_row_h, 1), ws_h.Cells(UBound(ary_d, 1) + s_row_h - 1, e_col_d)) = ary_d
s_row_h = s_row_h + UBound(ary_d, 1)

Worksheets.Copy メソッド

シートをブック内の他の場所にコピーします。
引数「Before」と「After」の両方を省略すると、コピーしたシートを含む新しいブックが作成されます。

今回は引数「Before」と「After」の両方を省略しているので、新しいブックが作成されます。

ws_h.Copy

Workbook.SaveAs メソッド

ブックへの変更を別のファイルに保存します。

Workbook.SaveAs( [, Filename] [, FileFormat] [, Password] [, WriteResPassword]
 [, ReadOnlyRecommended] [, CreateBackup] [, AccessMode] [, ConflictResolution]
 [, AddToMru] [, TextCodepage] [, TextVisualLayout] [, Local] [, WorkIdentity]) 

今回はcsvの入っているフォルダパス変数「pt」の中に「結合.xlsx」というファイル名で保存します。ファイル形式を指定する引数「FileFormat」を省略しているため、規定ブック「.xls」または「.xlsx」で保存されます。（環境に依存）

ActiveWorkbook.SaveAs Filename:=pt & "\結合.xlsx"

Application.ScreenUpdating プロパティ

マクロの速度を向上させるため、画面を更新しないようにします。

【参考】

指定したフォルダ内のファイル名全てを取得（Excel VBA）
変数でよく使われる「buf」「tmp」の意味
Dir関数が取得するファイルの順番
指定したフォルダ内のフォルダ名全てをGetAttrを使って「エラー53 ファイルが見つかりません。」を出さずに取得（Excel VBA）
GetAttrとは？「= vbDirectory」ではなく「And vbDirectory」となるビット演算の疑問
フォルダ名だけを取得したい時に出てくる 「.」 と 「..」 とは？
指定したフォルダ内とサブフォルダ内全てのファイル名を取得（Excel VBA）
CreateObject(“Scripting.FileSystemObject”) を使ってサブフォルダを取得
再帰処理とは？フォルダ内とサブフォルダ内全てのファイル名を取得（Excel VBA）
指定したフォルダ内のサブフォルダのフォルダ名を全部取得（Excel VBA）
再帰処理とは？フォルダ内のサブフォルダのフォルダ名を全部取得（Excel VBA）
参照渡し「ByRef」と値渡し「ByVal」の違い（Excel VBA）
ファイルのフルパスからファイル名のみを取得 InStrRev関数（Excel VBA）
指定したフォルダの全ての階層のフォルダ名・サブフォルダ名・ファイル名を取得（Excel VBA）
FileSystemObjectとは？CreateObject 関数 FolderExists・GetFolderの使い方
FileSystemObject CreateObject関数を使う方法・ 参照設定を使う方法 違いを理解してエラー防止
再帰処理とは？指定したフォルダの全ての階層のフォルダ名・サブフォルダ名・ファイル名を取得（Excel VBA）
ファイルのフルパスからファイル名のみを取得 Split関数（Excel VBA）
Callステートメントとは 引数 括弧（）の使い方（Excel VBA）
指定したフォルダ内から「特定の文字を含まないファイル名」を取得（Excel VBA）
ワイルドカードとは。使い方いろいろ。（Excel）
Dir関数の使い方。ファイル名やフォルダ名の取得方法。（Excel VBA）
指定したフォルダのファイル名を取得し、そのファイル名を一括で変換（Excel VBA）
Excel起動時に「コンパイルエラー」。64ビット システムで Declareステートメントに、PtrSafe属性を設定（Excel VBA ）
「ファイルを開く」ダイアログボックス から ファイル名を取得（Excel VBA）
「ファイルを開く」ダイアログボックス から 複数 ファイル名を取得（Excel VBA）
指定フォルダ内のサブフォルダ全てをフォルダ構成のみ（空フォルダ）を別フォルダにコピー（Excel VBA）
再帰処理とは？指定フォルダ内のサブフォルダ全てをフォルダ構成のみ（空フォルダ）を別フォルダにコピー（Excel VBA）
エクセル ファイルのフルパスから拡張子のみを取得 Split関数（Excel VBA）
エクセル ファイルのフルパスから拡張子のみを取得 InStr関数（Excel VBA）
Excel VBAダイアログボックスで選択したcsvファイルをエクセルブックとして開いて取込（「,」「”」非対応）
Excel VBAダイアログボックスで選択したcsvファイルをテキストファイルとして取込（ゼロ落ち・日付変換対応）
Excel VBAダイアログボックスで選択したcsvをテキストファイルとして取込（「,」「”」ゼロ落ち・日付変換対応）
Excel VBAダイアログボックスで選択したcsvをQueryTablesで取込（「,」「”」ゼロ落ち・日付変換対応）
エクセル ダイアログボックスからフォルダ選択してフォルダ内のファイル全てを取得（Excel VBA）
エクセル VBA 実行時エラー ” 取り込み先の範囲は、クエリテーブルが作成された同じワークシートにありません。を解決
ダイアログボックスで選択したフォルダ内全csvをエクセルブックとして取込み1つのデータにまとめる（「,」「”」対応）
ダイアログボックス選択したフォルダ内全csvをテキストファイルで取込み1つのデータにまとめる（ゼロ落ち・日付変換対応）
ダイアログボックス選択したフォルダ内全csvをテキストファイルで取込み1つのデータにまとめる（「,」「”」ゼロ落ち・日付変換対応）
Excel VBA ダイアログボックス選択したフォルダ内全csvをQueryTablesで取込み1つのデータにまとめる（「,」「”」ゼロ落ち・日付変換対応）
ダイアログボックスで選択したフォルダ内全csvをエクセルブックとして取込み別ファイルにまとめる（「,」「”」対応）