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です♪

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

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

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


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

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

【処理の流れ】

  1. ダイアログから、csvファイルを選択。
    Excel VBAダイアログボックスで選択したcsvファイルをエクセルブックとして開いて取込(「,」「"」対応)
  2. csvファイルを、Workbooks.Open メソッドで開く。
  3. csvファイルのデータを、配列ary_dに取込。
  4. 配列ary_dのデータを、貼付用シートに貼付。
    Excel VBAダイアログボックスで選択したcsvファイルをエクセルブックとして開いて取込(「,」「"」対応)
  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レコードとなり、レコードとレコードの間は改行で区切られます。

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

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

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

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

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


↓取り込んだ後のデータが、全く違うものになっています。
Excel VBAダイアログボックスで選択したcsvファイルをエクセルブックとして開いて取込(「,」「"」対応)

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

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

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

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

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

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

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

処理スピードが遅い

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

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

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

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


↓csvデータの難点である、カンマ「,」ダブルクォーテーション「”」を正しく取り込んでいます。
Excel VBAダイアログボックスで選択したcsvファイルをエクセルブックとして開いて取込(「,」「"」対応)

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

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

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

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

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

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

GetOpenFilenameメソッド

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

Application.GetOpenFilename(FileFilter, FilterIndex, Title, ButtonText, MultiSelect)

引数名説明省略
FileFilterファイルの種類・拡張子を指定する文字列(ファイルフィルター文字列)を指定します。
省略すると “すべてのファイル (*.*),*.*”になります。
FilterIndex引数FileFilterで指定したファイルフィルター文字列の中で、1から何番目のFileFilterを既定値とするかを指定します。
省略または、引数FileFilter文字列の数より大きい数値を指定すると、最初のFileFilter文字列が既定値となります。
Titleダイアログボックスのタイトルを指定します。
省略すると “ファイルを開く” になります。
ButtonTextMacintosh でのみ指定できます
MultiSelectTrue:複数のファイルを選択できます。
False:1 つのファイルしか選択できません。(規定値)

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

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

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

FileFilter

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

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

  • ファイルの種類を指定する文字列
  • ファイルの拡張子
Application.GetOpenFilename("ファイルの種類を指定する文字列,ファイルの拡張子")
Application.GetOpenFilename("Microsoft Excelブック,*.xls*")

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

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

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

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

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

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

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

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

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

指定した通りの内容で表示されます。
Excel VBAダイアログボックスで選択したcsvファイルをエクセルブックとして開いて取込(「,」「"」対応)

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

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

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

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 です。
LocalExcel の言語設定 (コントロール パネルの設定を含む) に合わせてファイルを保存するには、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番目の列ということです。

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

表の最終セルの指定

これらの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」 としておくと、配列の添え字の最小値を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」から始まってますね。

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

上記プロシージャには無い処理ですが、データを配列ary_dに格納した後に

MsgBox ary_d(4, 1)

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

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

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

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

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

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

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

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

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