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

【本日のミッション】

Excel VBA ダイアログボックスで選択したフォルダ内全てのcsvを、エクセルブックとして取込み、1つのデータにまとめよ。

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

1 ミッションの概要
2 プロシージャ
3 csvとは
4 csvをExcel Workbooks.Open メソッドで開ける時のデメリット
5 csvをExcel Workbooks.Open メソッドで開ける時のメリット
- 5.1 カンマ「,」ダブルクォーテーション「”」対応
- 5.2 初心者に理解しやすいコード
6 FileDialogオブジェクト
7 Dir関数
8 Workbooks.Open メソッド
9 最終行・最終列の取得
10 表の最終セルの指定
11 バリアント型(Variant)変数を配列として使用する
12 UBound関数
13 複数データを繋げる方法
14 Application.ScreenUpdating プロパティ
15 【参考】

ミッションの概要

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

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

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

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

ダイアログボックスで選択したフォルダ内のcsvを順に、ExcelのWorkbooks.Open メソッドで開き、一つのデータにまとめていきます。

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

【処理の流れ】

ダイアログから、csvの入っているフォルダを選択。
選択したフォルダ内の、csvファイルを順にOPEN。
csvファイルのデータを、配列ary_dに取込。
配列ary_dのデータを、貼付用シートに貼付。
csvファイルをCLOSE。→次のcsvの処理「2.」へ戻る。
選択したフォルダ内の、全csvの処理が完了したら終了。

■■■スポンサーリンク■■■

プロシージャ

Sub CSV取込5()

    '【変数】
    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

    '■画面更新STOP解除
    Application.ScreenUpdating = True
End Sub

csvとは

Comma-Separated Values の略称です。

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

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

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

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

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

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

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

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」には、下記定数のいずれかを指定します。

定数	内容
msoFileDialogFilePicker	ファイルを選択
msoFileDialogFolderPicker	フォルダを選択
msoFileDialogOpen	ファイルを開く
msoFileDialogSaveAs	ファイルを保存

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

Application.FileDialog(msoFileDialogFolderPicker)

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

FileDialog.InitialFileNameプロパティ

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

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

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

FileDialog.AllowMultiSelectプロパティ

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

値	内容
True	複数選択可能
False	複数選択不可

今回は、複数選択不可とするために「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引数に下記定数（値）を指定することで、特定の属性のオブジェクトのみを取得の対象とすることができます。

定数	値	属性
vbNormal (既定)	0	標準ファイル
vbReadOnly	1	読み取り専用ファイル
vbHidden	2	隠しファイル
vbSystem	4	システムファイル
vbVolume	8	ボリュームラベル。この値を指定すると、すべての属性は無効になります。
vbDirectory	16	フォルダ
vbAlias	64	エイリアスファイル

フォルダ名を取得したい場合は、第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] )

引数

内容

省略

FileName

開くブックのファイル名（ファイルパス）を指定します。

UpdateLinks

ファイル内のリンク更新方法を指定します。この引数を省略すると、リンク更新方法を確認するメッセージがユーザーに表示されます。

1	リンクの更新方法をユーザが指定
2	リンクを更新しない
3	リンクを更新する

○

ReadOnly

ブックを読み取り専用モードで開く場合、True を指定します。

○

Format

テキストファイルを開く場合、この引数で区切り文字を指定します。

1	タブ
2	カンマ
3	スペース
4	セミコロン
5	なし
6	カスタム文字

○

Password

パスワード保護されたブックを開くのに必要なパスワードを指定します。この引数を省略した場合、パスワードが必要なブックでは、パスワードの入力を促すダイアログボックスがユーザーに表示されます。

○

WriteResPassword

書き込み保護されたブックに書き込みをするために必要なパスワードを指定します。この引数を省略した場合、パスワードが必要なブックでは、パスワードの入力を促すダイアログボックスがユーザーに表示されます。

○

IgnoreReadOnlyRecommended

[読み取り専用を推奨する] チェックボックスをオンにして保存されたブックを開く場合でも、読み取り専用を推奨するメッセージを非表示にするには、True を指定します。

○

Origin

開こうとしているファイルがテキストファイルの場合、それがどのような形式のテキストファイルかを指定します。コードページと改行コード (CR/LF) を正しく変換するために必要です。 XlPlatform クラスの定数 xlMacintosh、xlWindows、xlMSDOS のいずれかを使用します。このファイルを省略すると、現在のオペレーティングシステムの形式が使用されます。

○

Delimiter

開こうとしているファイルがテキストファイルで、引数 Format が 6 の場合は、この引数で区切り文字を使用します。文字列の最初の文字のみが使用されます。

○

Editable

開こうとしているファイルが Excel 4.0 のアドインの場合、この引数に True を指定すると、アドインがウィンドウとして表示されます。この引数に False を指定するか、この引数を省略すると、アドインは非表示の状態で開かれ、ウィンドウとして表示することはできません。

○

Notify

ファイルが読み取り/書き込みモードで開けない場合に、ファイルを通知リストに追加するには、True を指定します。ファイルが読み取り専用モードで開かれて通知リストに追加され、ファイルが編集可能になった時点でユーザーに通知されます。ファイルが開けない場合に、このような通知を行わずにエラーを発生させるには、False を指定するか省略します。

○

Converter

ファイルを開くときに最初に使用するファイルコンバータのインデックス番号を指定します。

○

AddToMru

最近使用したファイルの一覧にブックを追加するには、True を指定します。既定値は False です。

○

Local

Excel の言語設定 (コントロールパネルの設定を含む) に合わせてファイルを保存するには、True を指定します。

○

CorruptLoad

使用できる定数は、xlNormalLoad、xlRepairFile、xlExtractData のいずれかです。この引数を省略した場合の既定の動作は、通常は標準の読み込み処理となります。

○

■■■スポンサーリンク■■■

最終行・最終列の取得

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

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

方向	内容	キーボード操作
xlUp	上方向	Ctrl＋↑
xlDown	下方向	Ctrl＋↓
xlToLeft	左方向	Ctrl＋←
xlToRight	右方法	Ctrl＋→

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)

引数名	説明	省略
ArrayName	配列変数の名前を指定します。	×
Dimension	添え字の上限を調べたい配列の次元を指定します。省略すると、1 次元とみなされます。	○

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)

Application.ScreenUpdating プロパティ

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

【参考】

■■■スポンサーリンク■■■

ミッションの概要

プロシージャ

csvとは

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

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

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

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

処理スピードが遅い

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

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

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

FileDialogオブジェクト

FileDialog.InitialFileNameプロパティ

FileDialog.AllowMultiSelectプロパティ

FileDialog.Titleプロパティ

FileDialog.Showメソッド

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

Dir関数

Workbooks.Open メソッド

最終行・最終列の取得

表の最終セルの指定

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

UBound関数

複数データを繋げる方法

Application.ScreenUpdating プロパティ

【参考】

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