【VBA】テキストファイルの読み込みを高速化する

こんにちは、Ryoです。
VBAでcsvファイルの処理を行うことは比較的多いかと思います。その際の処理でExcelのセルへのアクセス回数と処理速度は因果関係があり、回数が少ないほど速度的には有利になりますので、今回はcsvファイル内のデータを配列に格納し1回の処理でエクセルのワークシートに転記する方法などについて書いていきます。

1.サンプル概要
2.サンプルコード
- ◆参考：アクティブシートの初期化
3.まとめ

1.サンプル概要

サンプルとして準備したcsvファイルをテキストエディタ（メモ帳）で開いたものですが、これを読み込んでExcelのSheetに転記していきます。

ちなみにテキストの文字コードが「UFT-8」形式であれば問題ありませんが「UFT-8(BOM付き）」では文字化けが発生するのでご注意ください。

このテキストデータを読み込んで配列に格納し、Excelへの転記した結果が以下になります。

転記自体は完了しているので処理上は問題ありませんが、既定の列幅では表示できず####・・・となってしまうこともあります。その場合、ShirinkToFitプロパティやAutoFitメソッドを利用することで対処できます。

≪ShirinkToFitプロパティ≫

列幅を変えずにセル内の文字を縮小して表示させます。

≪AutoFitメソッド≫

列幅や行高さを自動調節して表示させます。

ShirinkToFitプロパティやAutoFitメソッドを利用するとそれぞれ自動調節されるので便利ですが、Sheetを初期化したい際に手動で戻したりするのは面倒なので本題とは反れますがアクティブシートの初期化についても書いておきます。

実行すればこの通り元のシートに戻ります。

以上がサンプル概要になります。

2.サンプルコード

Sub Sample1()

 Dim num As Integer
 Dim buf() As Byte
 Dim D_list, samp, Data() As Variant
 Dim Row_Number As Long
 Dim i, j As Long
 
 'ファイル番号を取得
 'テキストファイルやバイナリファイルを扱う際に必要になる
 num = FreeFile
 'Sample1.csvファイルをバイナリモードで開く
 Open ThisWorkbook.Path & "\Sample1.csv" For Binary As #num
 'ファイルの長さを取得、変数bufのサイズ（大きさ）を確保
 ReDim buf(1 To LOF(num))
 'ファイルを変数bufに読み込む
 Get #num, , buf
 'ファイルを閉じる
 Close #num
 
 '読み込みデータを改行コードで区切り､配列に代入することで
 '配列＝行毎のデータとなる
 D_list = Split(StrConv(buf, vbUnicode), vbCrLf)
 'データ行数の取得
 Row_Number = UBound(D_list)
 
 'データ行数分繰り返し処理を行う
 For i = 1 To Row_Number
   '1行分のデータをカンマで区切り配列に代入
   samp = Split(D_list(i - 1), ",")
   '配列変数Dataの要素数を変更する
   ReDim Preserve Data(1 To Row_Number, 1 To UBound(samp) + 1)
      '各1行でのデータ処理を行い、配列に代入する
      For j = 1 To UBound(samp) + 1
         Data(i, j) = samp(j - 1)
      Next
 Next
 
 'セルへデータの書込み処理を行う
 With Cells(1, 1).Resize(UBound(Data), UBound(Data, 2))
         .Value = Data
         
         '↓↓セル内の文字を縮小して表示させたい場合
         '(列幅を変更したくない場合に有効）
         '.ShrinkToFit = True
         
         '↓↓列幅を自動調節させて表示させる場合
         '.EntireColumn.AutoFit
         
  End With
   
End Sub

このサンプルコードは実行元ファイルと同パスにある「Sample1.csv」ファイルデータを読み込んでExcelのアクティブシートに転記するものです。

先ずファイルの読み込みにはGetステートメントを使用します。

構文：Get[#]filenumber,[recnumber],varname

引数のfilenumberは任意のファイル番号を指定しますが、そのファイルはRandomモードかBinaryモードのどちらかで開かれている必要がありますので、サンプルではBinaryで実行しています。

引数recnumberは読込開始レコード番号（Randomモード）やバイト位置（Binaryモード）を指定するものですが省略も可能です。引数varnameは読み込んだデータを格納する変数を指定します。

このGetステートメントを使用して該当csvファイルのデータをまとめて変数bufに読み込む形になりますので、予めLOF関数（Openステートメントで開いているファイルサイズを返す）を使用して変数bufにファイルデータ相当の容量を確保します。

ここでGetステートメントにサイズを指定するのは「指定した変数のサイズ分のみデータが読み込める」ので、この処理が必要となります。

次にSplit関数を使用して読み込んだデータを行ごとの配列データに分割しています。

構文：Split(expression[, delimiter[, limit[, compare]]])

引数expressionに指定した文字列を、引数delimiterに指定した文字で区切り、配列を返しますのでサンプルでは改行コードで区切ることで行ごとのデータとしています。

後は取得した各行のデータに対し、カンマを基準として更にデータを分割することで、1行のデータをセル毎のデータへ分割することができます。

分割されたデータを配列変数Dataに格納し、ExcelのセルA1以降(Cells(1,1))に貼り付けますが、この時にResizeプロパティを使用することで貼り付け先のセル範囲を配列変数Dataに合わせています。

ここまででデータ転記は完了なので、サンプルコード内コメント文にしてあるShirinkToFitプロパティやAutoFitメソッドは使用しなくても問題ありませんが、表示などの便宜上で必要があれば有効にして動作確認してみるのも良いかと思います。

◆参考：アクティブシートの初期化

これは本記事のサンプル動作確認を繰り返し実行した際に、挙動を確認する上で新規Sheet上で実行したかったこともあり、列幅／行高さ／書式などのクリア＝初期化を実行するものです。

Sub Sample2()

'***アクティブシートの初期化***

 'アクティブシートの全セル対象
 With Cells
   '全て消去
   .Clear
   '書式クリア（罫線／設定書式など）
   .ClearFormats
   'Sheet全体の列幅を標準サイズ（8.43）設定
   .ColumnWidth = ActiveSheet.StandardWidth
   'Sheet全体の行高さを標準サイズ(12.00）設定
   .RowHeight = ActiveSheet.StandardHeight
 End With
 
End Sub

cells.●●と指定するこでアクティブシート全体を選択しますので.clear/.clearFormatsを使用して消去しています。後は列幅／行高さをそれぞれ.StandardWidth/.StandarsHeightを指定することで標準サイズ設定としています。

もしデフォルトサイズに戻したいことなどがあればご参考までに。