Excel VBAダイアログボックスで選択したcsvファイルをエクセルブックとして開いて取込（「,」「”」対応）

【本日のミッション】

Excel VBA ダイアログボックスで選択したcsvファイルをエクセルブックとして開いて、そのデータを取り込め。

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

ミッションの概要

Excel VBA ダイアログボックスで選択したcsvファイルをエクセルブックとして開いて、そのデータを取り込め、というのが今回のミッションです。

今回は特別なことはしていません。csvファイルを、普通にエクセルのWorkbooks.Open メソッドで開いているだけです。

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

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

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

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

↓カンマ区切りのcsvを、ExcelのWorkbooks.Open メソッドで開きます♪

【処理の流れ】

1. ダイアログから、csvファイルを選択。
   
2. csvファイルを、Workbooks.Open メソッドで開く。
3. csvファイルのデータを、配列ary_dに取込。
4. 配列ary_dのデータを、貼付用シートに貼付。
   
5. csvファイル閉じます。

※A列の年月日が「#######」なのは、列を広げたら表示されます。

プロシージャ

Sub CSV取込1()

    '【変数】
    Dim fn As Variant       '取込ファイル名
    Dim ws As Worksheet     'データ貼付用シート（このブックのsheet1）
    Dim ws_d As Worksheet   'データ取込シート
    Dim ary_d As Variant    'データ取込用配列
    Dim e_row_d As Long     'データ取込シート 最終行
    Dim e_col_d As Long     'データ取込シート 最終列
    
    '■画面更新STOP
    Application.ScreenUpdating = False
    
    '■変数セット
    Set ws = ThisWorkbook.Worksheets("Sheet1")  'データ貼付用シート（このブックのsheet1）

    '■ファイルの選択
    fn = Application.GetOpenFilename(FileFilter:="CSVファイル(*.csv),*.csv")
    
    '■ファイル選択の確認
    If fn = False Then
        Exit Sub        'ファイル選択されていなかったら終了
    End If
  
    '■選択したファイルを開く
    Workbooks.Open Filename:=fn
    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(1, 1), ws_d.Cells(e_row_d, e_col_d))
    
    '■配列ary_d→データ貼付用シート（このブックのsheet1）
    ws.Range(ws.Cells(1, 1), ws.Cells(e_row_d, e_col_d)) = ary_d
    
    '■ファイルを閉じる
    ActiveWorkbook.Close SaveChanges:=False
    
    '■画面更新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」なんて理解できな！という方におすすめです。

GetOpenFilenameメソッド

GetOpenFilenameメソッドは、「ファイルを開く」ダイアログボックスを表示させ、選択したファイルのフルパスを返します。

今回は引数FileFilterのみを使用し、他の引数は省略しています。

Application.GetOpenFilename(FileFilter:="CSVファイル(*.csv),*.csv")

FileFilter

ファイルの種類・拡張子を指定する文字列です。
省略すると “すべてのファイル (*.*),*.*”になりますが、選択するファイルの種類が決まっているなら、ダイアログに表示されるファイルが限定されるので、ファイルを選択しやすくなります。

ファイルの種類1つにつき、下記2つの情報をセットにして指定します。
指定するときは、2つの情報の間にカンマ「,」を入れます。
参照：ワイルドカードとは。使い方いろいろ。（Excel）

• ファイルの種類を指定する文字列
• ファイルの拡張子

Application.GetOpenFilename("ファイルの種類を指定する文字列,ファイルの拡張子")

Application.GetOpenFilename("Microsoft Excelブック,*.xls*")

1つのファイルの種類に、複数の拡張子を表示する場合は、拡張子の間にセミコロン「；」を入れます。

Application.GetOpenFilename("テキストファイル,*.txt;*.csv")

複数のファイルの種類を表示する場合は、「ファイルの種類,拡張子」のセットをカンマ「,」で区切ります。カンマだらけになってしまいます。

Application.GetOpenFilename("ファイルの種類,拡張子,ファイルの種類,拡張子,ファイルの種類,拡張子")

Application.GetOpenFilename("Microsoft Excelブック,*.xls*,テキストファイル,*.txt,テキストファイル,*.csv")

もしも、こんなおふざけな指定をしてしまっても・・・

Application.GetOpenFilename("えくせる,*.xls*,てきすと,*.txt;*.csv")

指定した通りの内容で表示されます。

でも、かっこよく指定したい！という場合は、Excelの「ファイルを開く」ダイアログボックスのファイルの種類の文字をマネしておきましょう。誰からも突っ込まれないでしょう。

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(1, 1), ws_d.Cells(e_row_d, e_col_d))

あとは表の左上の「セルA1」（ws_d.Cells(1, 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(1, 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(4, 1)

とすると、格納した表の4行目1列目の「年月日時」が表示されます。

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

せっかく配列に入れたデータですが、今回はそのまま別のセルに貼り付けています。

ws.Range(ws.Cells(1, 1), ws.Cells(e_row_d, e_col_d)) = ary_d

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

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ファイルをエクセルブックとして開いて取込（「,」「”」対応）