エクセルVBAでCSVを高速に取り込むQueryTableオブジェクト使用時の注意点

QueryTabelオブジェクトの注意点アイキャッチ
みなさん、こんにちは!
フジタニ(@libartweb)です。

エクセルVBAでCSVを高速に取り込むQueryTableオブジェクトについて、各メソッド、プロパティの使い方をシリーズでお伝えしてきました。

前回の記事はこちら

エクセルVBAで高速にCSVを取り込むQueryTableオブジェクトの最終処理
エクセルVBAでCSVを高速に取り込むことができるQueryTableオブジェクト。最終処理として非常に重要なQueryTable.Deleteメソッドについて解説します。忘れると不具合を招く非常に重要なメソッドです!

QueryTableオブジェクトの速さ、便利さを知って頂けたと思います。

今回はそんなQueryTableオブジェクトを使用する際に気をつけて頂きたいことを紹介します!

思わぬバグを防ぐことはもちろん、コーディングの時間短縮にもつながります。引き出しを増やすためにも、是非参考にしてみてください。

スポンサーリンク

中身が空のCSVへの対応

QueryTableオブジェクトを使用したCSV取り込みにおける、最大のつまずきポイントかも知れません。

QueryTableオブジェクトでは、空のCSVを取り込むことができないのです。

中身が空のCSVを取り込もうとした場合のエラー

試しに空のCSVで実行してみましょう。

空のCSV取り込みエラー

「メモリが不足しています」

わかりづらいエラー!どう見ても、メモリ関連に問題があるようなエラーですから、知らない人はこれを見て、原因が「CSVが空であること」をすぐに見抜くことは難しいかもしれないですね…

では、空のCSVを取り込むことも想定してコーディングしちゃいましょう。

CSVが空かどうかを取り込み前に確認する方法

CSVの行数カウントには様々な方法がありますが、今回はCSVが空かどうか?を調べるだけですので最も簡単なプログラムを紹介します。サンプルコードは以下の通りです。

Private Sub csvImport()
 
    Dim strPath As String
    Dim qtCsv   As QueryTable
    
    strPath = "C:\Users\hirom\Desktop\test.csv"

    If Dir(strPath) = "" Then
        MsgBox (strPath & "は存在しません")
        Exit Sub
    End If
    
    If isCsvEmpty(strPath) = True Then
        MsgBox (strPath & "は中身が空です。")
        Exit Sub
    End If
    
    Set qtCsv = Sheet1.QueryTables.Add(Connection:="TEXT;" & strPath, _
        Destination:=Sheet1.Range("A1")) '取り込むCSVファイルパスと、取り込み先のシート、セルを指定
    
    With qtCsv
        .TextFileCommaDelimiter = True 'カンマ区切りの指定
        .Refresh 'QueryTableオブジェクトを更新し、シート上に出力
        .Delete 'QueryTableオブジェクトを削除
    End With
End Sub

Private Function isCsvEmpty(ByVal strPath As String)
    Dim myFileNo As Integer
    
    isCsvEmpty = True
    myFileNo = FreeFile
    Open strPath For Input As #myFileNo
        If EOF(myFileNo) = False Then
            isCsvEmpty = False
        End If
    Close #myFileNo
    
End Function

これまでに紹介してきたQueryTableのソースコード内に、CSVが空かどうか?をチェックする「isCsvEmpty」というFunctionを追加しました。

CSVが空かどうかを判定して、空なら処理を中断する処理です。

それでは「isCsvEmpty」で使用しているステートメント、関数を紹介していきます。

ステートメント 詳細
Open CSVやテキストファイルをパソコン内部で開く
Close Openステートメントで開いたファイルを閉じる
関数 詳細 引数 返り値
FreeFile ファイル番号を返す 範囲番号 Integer
EOF ファイルの読み込み位置が末尾かどうかを返す ファイル番号 Boolean

ステートメント、関数の使い方

Open CSVのフルパス For 入力モード As #ファイル番号

通常テキストやCSVなどを取り込むときはWorkbook上に取り込みますが、Openステートメントを使用することでWorkbookを新しく作成せず、パソコン内部で開くことができます。

引数 詳細
CSVのフルパス 空であるか確認したいCSVのフルパス
入力モード CSVの場合はシーケンシャル入力モードを表す「Input」を指定。(先頭から順番に読み込むモード)
#ファイル番号 CSVに割り当てる番号

サンプルでは、CSVのフルパスを表す変数strPathを引数として渡しています。

以下の部分ですね!

If isCsvEmpty(strPath) = True Then

渡した引数は「isCsvEmpty」内のOpenステートメントで使います。

Open strPath For Input As #myFileNo

入力モードには、シーケンシャル入力モードであるInputを指定し、先頭から読み込むように命令しています。

#ファイル番号は1~511の番号をCSVに対して割り当てることができますが、すでに使用している番号を指定するとエラーになるので、自動的に以下の関数でファイル番号を取得しています。

FreeFile(範囲番号)

まだ使用されていないファイル番号を返します。自動でファイル番号を選択してくれるので、重複によるエラーが回避できます。

範囲番号に0を指定すると1~255の値、1を指定すると256~511の値を返します。

省略すると0を指定したことになります。

サンプルでは、myFileNoという変数にFreeFile関数で取得した値を格納しています。

myFileNo = FreeFile
EOF(ファイル番号)

openステートメントで開いたCSVの読み込み位置が、末尾に達した場合にTrueを返します。

つまり、CSVが空の場合はTrue、1行でもデータが格納されている場合はFalseを返します。

Close #ファイル番号

Closeステートメントで引数「ファイル番号」に指定した番号のファイルを閉じます。この時点で割り当てられた番号は解放されます。

取り込むCSVの選択方法

CSVの選択方法には、

  • 自動で選択する方法
  • ファイル選択ダイアログで手動で選択する方法

以上の2通りがあります。状況に合わせて最適な方法を選択してください。

自動でファイルを選択する

  • 毎回ファイル名が同じ場合
  • 変動するファイル名に規則性がある場合(日付など計算で求められる)

以上の通り、CSVのファイル名に規則性がある場合は手動選択にせず、自動化してしまいましょう。ファイルの選択誤りは高い確率で起こりうる人為的なミスです。

ミスを減らす工夫は以下の記事で詳しく紹介しています。

エクセルVBAによる運用業務で人為的ミスを減らす4つの方法
エクセルVBAを運用業務で活用されている方必見!人為的ミスを大幅に減らす4つの方法を紹介します。エクセルVBAはメイン処理に偏らず人為的ミスを想定したコーディングが業務効率化に繋がります。

ファイルの存在チェックを導入する

自動で選択する以上は、ファイルの存在チェックが必要不可欠です。

ファイル自動選択を導入する場合は、以下の関数で必ずファイルの存在チェックを行ってください。

Dir(ファイルのフルパス)

使い方は以下の記事をご覧ください。

エクセルVBAでDir関数を使って指定のファイルが存在するかどうかを判定する方法
エクセルVBAでファイルを開く作業を自動化する方法についてのシリーズ。今回は開くファイルの指定をエクセルシートに変更し、またファイルが存在するかどうかをDir関数で判定する方法についてお伝えします。

ファイル選択ダイアログで選択する

自動ではなくファイル選択ダイアログを使うことでも実現できます。

CSVの名前に規則性がなく、自動化が難しい場合に使用してください。

ファイル選択ダイアログは以下の記事が参考になります。

エクセルVBAでFileDialogオブジェクトを使ってファイル選択ダイアログを開く
エクセルVBAでファイルを開く作業を自動化する方法についてシリーズでお伝えしています。今回は、エクセルVBAでFileDialogオブジェクトを使ってファイル選択ダイアログを表示させる方法です。

まとめ

以上、QueryTableオブジェクトを使用する際に注意すべき事として以下の2点を紹介しました。

  • 中身が空のCSVへの対応
  • CSVの選択方法

特にCSVが空の時の「メモリ不足です」というエラーはわかりづらいですよね。

コーディングの時間短縮はもちろんですが、思わぬバグや運用ミスを減らす意味で、今回紹介した内容は参考になるはずです。

QueryTableオブジェクトを最大限に実務でお役立てください!

以上、QueryTableオブジェクトの連載でした。

連載目次:エクセルVBAのQueryTableで高速にCSVを取り込む

一般的なループを使用したCSV取り込みよりも、更に高速にCSVを取り込むことのできるQueryTableオブジェクトに関する連載です。サイズの大きなCSVを取り込む際に非常に便利です。
  1. エクセルVBAでQueryTableオブジェクトを使って高速にCSVを取り込む方法
  2. エクセルVBAのQueryTables.Addメソッドでクエリテーブルを作成する方法
  3. エクセルVBAのQueryTable.RefreshメソッドでCSVをシートに出力する方法
  4. エクセルVBAのQueryTableオブジェクトのプロパティの基本とCSVをカンマ区切りする方法
  5. エクセルVBAのQueryTableオブジェクトであらゆる種類のCSVを取り込む方法
  6. エクセルVBAで高速にCSVを取り込むQueryTableオブジェクトの最終処理
  7. エクセルVBAでCSVを高速に取り込むQueryTableオブジェクト使用時の注意点
タイトルとURLをコピーしました