ITのえんぴつ

Google Blockly開発者ツールやIT・プログラミングを研究して発信するブログ

Excel VBAでのCSV取り込みが遅い時に処理速度を改善する方法

前回のVBAコードでは、大規模なCSVファイルのインポートに時間がかかってしまいました。前回のコードに、画面更新と自動計算の停止と再開などの実行処理を修正することでパフォーマンスを向上させることができましたので、共有します。

改善点の説明

  1. 画面更新と自動計算の停止と再開: Application.ScreenUpdatingApplication.Calculation を使って処理中の画面更新と自動計算を停止し、処理後に再開することで、パフォーマンスを大幅に向上させます。
  2. 特定範囲のクリア: シート全体ではなく、必要な範囲だけをクリアするようにしています。
  3. クエリテーブルのプロパティ最適化: .TextFileColumnDataTypes.PreserveFormatting を適切に設定し、データのインポートを最適化します。

コード

以下は、これらの改善を適用したコードです

Sub CSVファイルの内容を取得する()

    ' 画面更新と自動計算を停止
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    ' ワークシートとファイル名を格納する変数を宣言
    Dim wsheet As Worksheet
    Dim file_mrf As String

    ' アクティブなブックの「Sheet1」シートをセット
    Set wsheet = ActiveWorkbook.Sheets("Sheet1")

    ' 「Sheet1」シートを選択
    Sheets("Sheet1").Select

    ' 必要な範囲だけをクリア
    wsheet.Cells.ClearContents

    ' ユーザーにCSVファイルを選択させるダイアログを表示し、選択されたファイルのパスを取得
    file_mrf = Application.GetOpenFilename("Text Files (*.csv), *.csv", , "Provide Text or CSV File:")

    If file_mrf <> "False" Then
        ' クエリテーブルを追加してCSVファイルをインポート
        With wsheet.QueryTables.Add(Connection:="TEXT;" & file_mrf, Destination:=wsheet.Range("A1"))
            ' CSVファイルの区切り方法を設定
            .TextFileParseType = xlDelimited

            ' 区切り文字をカンマに設定
            .TextFileCommaDelimiter = True

            ' その他のプロパティ設定を最適化
            .TextFileColumnDataTypes = Array(1) ' 全ての列を文字列として読み込む
            .PreserveFormatting = True
            .AdjustColumnWidth = True

            ' データをインポート
            .Refresh BackgroundQuery:=False
        End With
    End If

    ' 画面更新と自動計算を再開
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic

End Sub

これらの変更により、特に大量データを扱う場合のパフォーマンスが向上するはずです。

Excel VBAで、CSVとExcelの両方のファイル形式を取り込みたいときの修正案

前回のVBAコードでは、CSVファイルの取り込みに対応していましたが、Excelファイル形式の取り込みには対応していませんでした。そこで今回は、前回のコードをCSVExcelの両方のファイル形式に対応するようにカスタマイズしましたので、共有します。

対応

Excelファイルを取り込むためには、QueryTables の代わりに Workbooks.Open メソッドを使用してExcelファイルを開き、必要なデータをコピーしてシートに貼り付ける方法が有効です。

コード

以下は、CSVExcelの両方のファイル形式に対応するようにカスタマイズしたコードです。

Sub ファイルの内容を取得する()

    ' 画面更新と自動計算を停止
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    ' ワークシートとファイル名を格納する変数を宣言
    Dim wsheet As Worksheet
    Dim file_mrf As Variant
    Dim wbTemp As Workbook

    ' アクティブなブックの「Sheet1」シートをセット
    Set wsheet = ActiveWorkbook.Sheets("Sheet1")

    ' 必要な範囲だけをクリア
    wsheet.Cells.ClearContents

    ' ユーザーにCSVまたはExcelファイルを選択させるダイアログを表示し、選択されたファイルのパスを取得
    file_mrf = Application.GetOpenFilename("Text or CSV Files (*.csv), *.csv, Excel Files (*.xlsx; *.xls), *.xlsx; *.xls", , "Provide Text, CSV, or Excel File:")

    ' ファイルが選択されたか確認
    If file_mrf <> False Then
        ' ファイルの拡張子を取得
        Dim fileExt As String
        fileExt = Right(file_mrf, Len(file_mrf) - InStrRev(file_mrf, "."))

        ' CSVファイルの場合
        If fileExt = "csv" Then
            ' クエリテーブルを追加してCSVファイルをインポート
            With wsheet.QueryTables.Add(Connection:="TEXT;" & file_mrf, Destination:=wsheet.Range("A1"))
                ' CSVファイルの区切り方法を設定
                .TextFileParseType = xlDelimited

                ' 区切り文字をカンマに設定
                .TextFileCommaDelimiter = True

                ' その他のプロパティ設定を最適化
                .TextFileColumnDataTypes = Array(1) ' 全ての列を文字列として読み込む
                .PreserveFormatting = True
                .AdjustColumnWidth = True

                ' データをインポート
                .Refresh BackgroundQuery:=False
            End With
        ' Excelファイルの場合
        ElseIf fileExt = "xlsx" Or fileExt = "xls" Then
            ' 一時的にExcelファイルを開く
            Set wbTemp = Workbooks.Open(file_mrf)

            ' 一時的なワークブックの最初のシートの内容をコピー
            wbTemp.Sheets(1).UsedRange.Copy

            ' 目的のシートに貼り付け
            wsheet.Range("B1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

            ' 一時的なワークブックを閉じる
            ' wbTemp.Close SaveChanges:=False
        End If
    End If

    ' 画面更新と自動計算を再開
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic

End Sub

改善点の説明

  1. ファイル選択ダイアログ: Application.GetOpenFilename メソッドのフィルターにより、CSVおよびExcelファイル(*.csv, *.xlsx, *.xls)を選択できるようにしました。
  2. ファイル拡張子の判定: 選択されたファイルの拡張子を判定し、それに応じて処理を分岐させます。
  3. CSVファイルの処理: 既存のCSVファイルの処理コードを保持しています。
  4. Excelファイルの処理: Excelファイルの場合は、一時的にファイルを開き、その内容を現在のシートにコピーして貼り付けます。

これにより、ユーザーはCSVおよびExcelファイルのいずれかを選択し、その内容をシートにインポートできるようになります。

Excel VBA: CSV取り込み後のセル幅変更対応

前回作成したExcel VBAスニペットを使って CSVを取り込んだ際に、元ファイルのセル幅が変わってしまいました。その対処法を調べたので、共有します。

原因

列幅が変更されるのは、QueryTables.AddメソッドのRefreshメソッドが呼び出された際に自動的に列幅を調整するためです。

対処法

この動作を防ぐには、クエリテーブルを設定する際に列幅の自動調整を無効にする必要があります。

        ' 列幅の自動調整を無効にする
        .AdjustColumnWidth = False 

元のセル幅を維持したい場合には、この1行を追加してみてください。

コード

修正後の全体コードはこちらです。

■ sample.xlsm


Sub CSVファイルの内容を取得する()

    ' ワークシートとファイル名を格納する変数を宣言
    Dim wsheet As Worksheet
    Dim file_mrf As String

    ' アクティブなブックの「Sheet1」シートをセット
    Set wsheet = ActiveWorkbook.Sheets("Sheet1")

    ' 「Sheet1」シートを選択
    Sheets("Sheet1").Select

    ' シート全体を選択
    Cells.Select

    ' 選択されたセルの内容をクリア
    Selection.ClearContents

    ' ユーザーにCSVファイルを選択させるダイアログを表示し、選択されたファイルのパスを取得
    file_mrf = Application.GetOpenFilename("Text Files (*.csv), *.csv", , "Provide Text or CSV File:")

    ' クエリテーブルを追加してCSVファイルをインポート
    With wsheet.QueryTables.Add(Connection:="TEXT;" & file_mrf, Destination:=wsheet.Range("A1"))
        ' CSVファイルの区切り方法を設定
        .TextFileParseType = xlDelimited
        
        ' 区切り文字をカンマに設定
        .TextFileCommaDelimiter = True
        
        ' 列幅の自動調整を無効にする
        .AdjustColumnWidth = False  

        ' データをインポート
        .Refresh
    End With
 
End Sub

■ data.csv

 "商品コード","商品名"
 "0000000001","洗濯洗剤"
 "0000000002","シャンプー"
 "0000000003","リンス"

まとめ:列幅の自動調整を無効化

マクロでCSVを取り込んだ際に、元ファイルのセル幅が変わってしまう問題は、AdjustColumnWidthプロパティをFalseに設定することで解決でき。これにより、インポート後列幅が変更されません。

Excel VBAスニペット: ファイル名で分岐してCSVファイルの内容を取得する

Excel VBAを使用してファイル名で分岐し、CSVファイルの内容を指定したシートに取得する方法を解説します。以下がそのVBAコードです。

■ sample.xlsm

Sub CSVファイルの内容を取得する()
    Dim file_mrf As String
    Dim sheetName As String
    Dim destCell As Range

    ' CSVファイルを選択させるダイアログを表示
    file_mrf = Application.GetOpenFilename("Text Files (*.csv), *.csv", , "Provide Text or CSV File:")

    ' ファイル名に基づいてシート名と貼り付け位置を設定
    Select Case True
	        Case InStr(file_mrf, "dogs.csv") > 0
            sheetName = "dogs"
            Set destCell = Sheets(sheetName).Range("A1")
        Case InStr(file_mrf, "cats.csv") > 0
            sheetName = "cats"
            Set destCell = Sheets(sheetName).Range("B1")
        Case Else
            MsgBox "dogsとcats以外のファイル名が指定されました。", vbExclamation
            Exit Sub
    End Select

    ' 指定されたシートをクリア
    ClearSheetContents sheetName

    ' CSVファイルをインポート
    ImportCSV file_mrf, destCell

    ' インポートしたデータから引用符を削除
    RemoveQuotes destCell.Parent.UsedRange
End Sub

' シートの内容をクリアする関数
Sub ClearSheetContents(sheetName As String)
    Sheets(sheetName).Cells.ClearContents
End Sub

' CSVファイルをインポートする関数
Sub ImportCSV(filePath As String, destCell As Range)
    With destCell.Parent.QueryTables.Add(Connection:="TEXT;" & filePath, Destination:=destCell)
        .TextFileParseType = xlDelimited
        .TextFileCommaDelimiter = True
        .TextFileTextQualifier = xlTextQualifierNone
        .Refresh
    End With
End Sub

' セル内の引用符を削除する関数
Sub RemoveQuotes(rng As Range)
    Dim cell As Range
    For Each cell In rng
        cell.Value = Replace(cell.Value, """", "")
    Next cell
End Sub

■ dogs.csv

 "コード","種類"
 "0000000001","トイプードル"
 "0000000002","チワワ"
 "0000000003","柴犬"

■ cats.csv

"コード","種類"
"0000000004","ペルシャ"
"0000000005","アメリカンショートヘア"
"0000000006","スコティッシュフォールド"

■ rabbits.csv

"コード","種類"
"0000000007","ネザーランドドワーフ"
"0000000008","ホーランドロップ"
"0000000009","ライオンヘッド"

説明

  1. CSVファイルの内容を取得するサブルーチン:
    • ユーザーにCSVファイルを選択させ、選択されたファイル名に基づいて適切なシート名と貼り付け位置を設定します。
    • シートの内容をクリアし、CSVファイルをインポートし、インポートしたデータから引用符を削除します。
  2. ClearSheetContents関数:
    • 指定されたシートの内容をクリアします。
  3. ImportCSV関数:
    • 指定されたファイルパスと貼り付け位置を使用してCSVファイルをインポートします。
  4. RemoveQuotes関数:
    • 指定された範囲内のセルから引用符を削除します。

ダウンロード

Excel VBAスニペット: 引用符を削除してCSVファイルの内容を取得する

Excel VBAを使用して引用符を削除し、CSVファイルの内容を取得する方法を解説します。以下がそのVBAコードです。

■ sample.xlsm


Sub CSVファイルの内容を取得する()

    ' ワークシートとファイル名を格納する変数を宣言
    Dim wsheet As Worksheet
    Dim file_mrf As String

    ' アクティブなブックの「Sheet1」シートをセット
    Set wsheet = ActiveWorkbook.Sheets("Sheet1")

    ' 「Sheet1」シートを選択
    Sheets("Sheet1").Select

    ' シート全体を選択
    Cells.Select

    ' 選択されたセルの内容をクリア
    Selection.ClearContents

    ' ユーザーにCSVファイルを選択させるダイアログを表示し、選択されたファイルのパスを取得
    file_mrf = Application.GetOpenFilename("Text Files (*.csv), *.csv", , "Provide Text or CSV File:")

    ' クエリテーブルを追加してCSVファイルをインポート
    With wsheet.QueryTables.Add(Connection:="TEXT;" & file_mrf, Destination:=wsheet.Range("A1"))
        ' CSVファイルの区切り方法を設定
        .TextFileParseType = xlDelimited
        
        ' 区切り文字をカンマに設定
        .TextFileCommaDelimiter = True

        ' テキストファイルの引用符を無視
        .TextFileTextQualifier = xlTextQualifierNone

        ' データをインポート
        .Refresh
    End With
    
    ' セル内のデータをループして引用符を削除
    Dim cell As Range
    For Each cell In wsheet.UsedRange
        cell.Value = Replace(cell.Value, """", "")
    Next cell
 
End Sub

■ data.csv

 "商品コード","商品名"
 "0000000001","洗濯洗剤"
 "0000000002","シャンプー"
 "0000000003","リンス"

このコードでは、wsheet.UsedRangeの各セルに対してReplace関数を使用して引用符を削除しています。この方法により、インポートされたデータのすべてのセルから引用符が取り除かれます。

Excel VBAスニペット: シンプルにCSVファイルの内容を取得する

Excel VBAを使用して シンプルにCSVファイルの内容を取得する方法を解説します。以下がそのVBAコードです。このコードは、ユーザーにCSVファイルを選択させ、選択されたCSVファイルの内容をExcelシートにインポートします。

■ sample.xlsm


Sub CSVファイルの内容を取得する()

    ' ワークシートとファイル名を格納する変数を宣言
    Dim wsheet As Worksheet
    Dim file_mrf As String

    ' アクティブなブックの「Sheet1」シートをセット
    Set wsheet = ActiveWorkbook.Sheets("Sheet1")

    ' 「Sheet1」シートを選択
    Sheets("Sheet1").Select

    ' シート全体を選択
    Cells.Select

    ' 選択されたセルの内容をクリア
    Selection.ClearContents

    ' ユーザーにCSVファイルを選択させるダイアログを表示し、選択されたファイルのパスを取得
    file_mrf = Application.GetOpenFilename("Text Files (*.csv), *.csv", , "Provide Text or CSV File:")

    ' クエリテーブルを追加してCSVファイルをインポート
    With wsheet.QueryTables.Add(Connection:="TEXT;" & file_mrf, Destination:=wsheet.Range("A1"))
        ' CSVファイルの区切り方法を設定
        .TextFileParseType = xlDelimited
        
        ' 区切り文字をカンマに設定
        .TextFileCommaDelimiter = True

        ' データをインポート
        .Refresh
    End With
 
End Sub

■ data.csv

 "商品コード","商品名"
 "0000000001","洗濯洗剤"
 "0000000002","シャンプー"
 "0000000003","リンス"

おまけ

もしマクロでCSVを取り込んだ際に元ファイルのセル幅が変わってしまう問題がある場合は、こちらの記事を参考にしてください。

 

eigo-gayomenai-engineer.hatenablog.com

 

参考

Excelのテンプレート9:超シンプルなQA表

今回は、超シンプルなQA表が欲しくなったので、個人で手軽に利用できるようにExcelのテンプレートとして保存しておくことにしました。

🔗(閲覧専用)EXCELテンプレートはこちら ダウンロードできます