Excel VBAダイアロボックスで選択したcsvをQueryTablesで取込（「,」「”」ゼロ落ち・日付変換対応）

【本日のミッション】

Excel VBA ダイアログボックスで選択したcsvファイルをQueryTablesで取り込め。

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

ミッションの

Excel VBA ダイアログボックスで選択したcsvファイルをQueryTablesで取り込め、というのが今回のミッションです。

今回挑戦するのは「QueryTables.Add メソッド」を使ったcsvの取り込みです。以前ご紹介しましたOpen ステートメントを使用したコードよりは、簡単なものになっています。

参照：Excel VBAダイアログボックスで選択したcsvをテキストファイルとして取込（「,」「”」ゼロ落ち・日付変換対応）

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

手動で外部データの取り込み（テキストファイル）を行う処理を、エクセルVBAで行います↓カンマ区切りのcsvを、QueryTables.Add メソッドを使ってデータ取り込みを行います。

【処理の流れ】

1. ダイアログからcsvファイルを選択。
   
2. QueryTables.Add メソッドで、QueryTableを作成。
3. QueryTableにcsvを取込。
4. QueryTableから、指定したセルにcsvのデータを出力。
5. csvファイルとの接続を解除。

プロシージャ

 Sub CSV取込4()

    '【変数】
    Dim fn As Variant       '取込ファイル名
    Dim ws As Worksheet     'データ取込用シート（このブックのsheet1）
    Dim qt As QueryTable    'QueryTableオブジェクト
    
    '■変数セット
    Set ws = ThisWorkbook.Worksheets("Sheet1")  'データ取込用シート（このブックのsheet1）

    '■ファイルの選択
    fn = Application.GetOpenFilename(FileFilter:="CSVファイル(*.csv),*.csv")
    
    '■ファイル選択の確認
    If fn = False Then
        Exit Sub           'ファイル選択されていなかったら終了
    End If
    
    '■QueryTableオブジェクトを追加
    Set qt = ws.QueryTables.Add(Connection:="text;" & fn, Destination:=ws.Range("A1"))

    '■プロパティを指定→csv取込→接続解除
    With qt
        .TextFilePlatform = 932                             '文字コードを指定 Shift_Jis
        .TextFileCommaDelimiter = True                      'カンマ区切り
        .TextFileColumnDataTypes = Array(2, 1, 1, 2, 1, 1)  'データ型
        .Refresh BackgroundQuery:=False                     'CSVデータをワークシートに出力
        .Delete                                             'CSVファイルとの接続を解除
    End With
End Sub

CSVとは

Comma-Separated Values の略称です。

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

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

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の「ファイルを開く」ダイアログボックスのファイルの種類の文字をマネしておきましょう。誰からも突っ込まれないでしょう。

QueryTables.Addメソッド

ワークシートにQueryTableオブジェクトを追加します。

取込先のワークシート.QueryTables.Add(Connection, Destination[, Sql])

今回は引数「Connection」と「Destination」を使用しています。

ws.QueryTables.Add(Connection:="text;" & fn, Destination:=ws.Range("A1"))

引数「Connection」で指定している部分を手動で行うなら、［データ］－［外部データの取り込み］－「テキストファイル」をクリック→「テキストファイルのインポート」ダイアログでファイルを選択、の部分ですね。

引数「Destination」はテキストファイルウィザードの最後に出てくる、下記画面で指定する「データを返す先を選択してください。」の部分ですね。

QueryTable.TextFilePlatform プロパティ

QueryTableに取り込むテキストファイルの、文字コードを指定します。

今回は、Shift-JISを示す「932」を指定しています。

qt.TextFilePlatform = 932

手動で「外部データの取り込み」を行う際の、この部分になります。

QueryTable.TextFileCommaDelimiter プロパティ

「True」を指定 すると、QueryTableにテキストファイルを取込する時の区切り記号が「カンマ」になります。

qt.TextFileCommaDelimiter = True

手動で「外部データの取り込み」を行う際のこの部分になります。

QueryTable.TextFileColumnDataTypes プロパティ

各データ列のデータ形式を指定します。
今回は、「ゼロ落ち」するテキスト列のみ「2」（文字列）を指定し、それ以外は「1」（自動判定）にしています。

qt.TextFileColumnDataTypes = Array(2, 1, 1, 2, 1, 1)

手動で「外部データの取り込み」を行う際のこの部分になります。

QueryTable.Refreshメソッド

QueryTableオブジェクトのcsvのテキストを、ワークシートに出力します。

QueryTableオブジェクト.Refresh(BackgroundQuery)

引数の「BackgroundQuery」には「False」を指定します。全てのデータをシートに取り出した後で制御をプロシージャに返します。「True」にすると変なタイミングでプロシージャに返されることがあるので使用しません。

.Refresh BackgroundQuery:=False  

【参考】

指定したフォルダ内のファイル名全てを取得（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で取込（「,」「”」ゼロ落ち・日付変換対応）