VBAで作ったマクロの高速化② 内部的に開く、閉じる（失敗⇒元データをテキスト(タブ区切り)で保存しておく）

~~~初めに(2019/10/11追記)~~~
この記事はうまくいかない方法について実験した結果を解説しています。この記事だけでは遅いマクロを早くしたい、という課題は解決しません。エクセルはバイナリファイルという種類のファイルであり、テキストファイルとして開くと文字化けします。

解決策については、
VBAで作ったマクロの高速化②改　内部的に開く、閉じる　⇒　実務作業の具体的手順をご覧下さい。
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~2019/10/11追記ここまで~~~~~~~

マクロの高速化のためにデータを内部的に開いたりする方法については、

毎日基幹システムから重たいをダウンロードして、どこかに保存する業務があるとします。
私はそれを月に一度集計するVBAツールを作ったのですが、作業に30分くらい掛かります。

「○○データ」YYYYMMDD.xlsx　と言うようなファイル名となっており、
フォルダ内でファイル名に「○○」がつくファイルを順に開いて一カ所にまとめて加工する、と言う作業です。

よく見るとファイルを開くところにめちゃくちゃ時間が掛かっていて、中身の計算自体はそれほどではありませんでした。

そういえばVBAとは関係なく、エクセルブックは開くところに時間が掛かります。

実験します。

注：この実験の結論はエクセルブックはOpenステートメントでは開けない、と言うものです。
それでは具体的にどうするかばよいかについては、VBAで作ったマクロの高速化②改　内部的に開く、閉じる　⇒　実務作業の具体的手順（csvファイルとして用意しておき、集約するという事前作業）をご覧下さい。

Sub 普通にエクセルを開いたり閉じたりする()

    Debug.Print Time
    
    Const path As String = "C:\Users\hoge\Documents\Book1.xlsx"
    Dim i As Long
    
    For i = 1 To 10
        Workbooks.Open Filename:=path
        Debug.Print Cells(i, 1)
        ActiveWorkbook.Close SaveChanges:=False
    Next i
    
    Debug.Print Time

End Sub

時間測定のために10回繰り返しています。空のエクセルファイルで5秒ほど掛かりました。空のファイルですら5秒と言う事は何メガもあるファイルを次々に開いていれば時間が掛かるのも納得です。

Textファイルを読み書きする時に使われるOpenステートメントがよく解説されていますが、エクセルブックで使えるのか実験します。

Sub 内部的にエクセルを開いたり閉じたりする()
' このプログラムは失敗する。バイナリファイルはテキストとして開くことはできない
    Debug.Print Time
    
    Const path As String = "C:\Users\hoge\Documents\Book1.xlsx"
    Dim i As Long
    Dim text_str As String
    
    For i = 1 To 10
        Open path For Input As #1
        Line Input #1, text_str
        Debug.Print text_str
        Close #1
    Next i
    
    Debug.Print Time

End Sub

実行結果。

実行結果（失敗）

文字化けした文字が10回出力されてしまいました。

Sub 内部的にエクセルを開いたり閉じたりする2()

    Debug.Print Time
    
    Const path As String = "C:\Users\hoge\Documents\Book1.text"
    Dim i As Long
    Dim text_str As String
    
    For i = 1 To 10
        Open path For Input As #1
        Line Input #1, text_str
        Debug.Print text_str
        Close #1
    Next i
    
    Debug.Print Time

End Sub

仕方が無いのでエクセルを.txt(タブ区切り)で保存して再度実行。すると、無事に終わりました。
ボタンを押した瞬間に終わりました。

結果としてエクセルを内部的に開いて素早く中身を読み取る事はできないことが分かりました。

しかし、基幹システムからダウンロードするファイルをtxtデータで保存することによって、大幅に
時間短縮ができることが確認できました。

＜関連記事＞

• ワンランク上のExcel VBAプログラミング｜電子書籍出版