以前、仕事で「HULFTでファイルをサーバーに転送するから、そのファイルが来たらExcelに取り込んでくれ」というトリッキーな依頼をされました。
ぶっちゃけ、Javaかとかで取り込んでpoiか何かでExcelに書き出したほうが良いかと思いました。
他言語がNGでも、監視部分だけでもVBS+タスクスケジューラーにして、VBAマクロをキックするだけにするとかがいい気がします。
5分毎くらいにVBSを起動してチェックすればいいだけですし、それならプロセスも立ち上げっぱなしにする必要がありません。
しかし、どうしてもExcelVBAで実施したいということで、そのトリッキーな仕様を実装しました。
もしかしたら需要があるかもしれないので、備忘として記載してみたいと思います。
ファイルを監視するにはOnTimeが最適
仮に、以下の仕様でVBA作成を依頼されたとします。
「C:\test.txt」にファイルがアップロードされたら、そのファイルをA1セルに取り込んでファイルを削除する。
「ファイルがアップロードされたら」という発火条件をどうするかですが、無限にループさせて確認するよりは、OnTimeで定期的に確認すべきですね。
以下がサンプルコードになります。
Public d As Date
Public Sub startCheck()
d = Now() + TimeSerial(0, 0, 10)
Call Application.OnTime(d, "startCheck")
// ファイルチェック処理
End Sub
Public Sub endCheck()
Call Application.OnTime(d, "startCheck", , False)
End Sub
startCheck関数で定期的にチェックを処理を開始。endCheck関数でチェック処理を停止します。
まず「Now() + TimeSerial(0, 0, 10)」という関数で現在時刻から10秒後に日付を取得します。
onTime関数で、その10秒後の日付にstartCheck関数を再帰呼び出しします。
再帰呼び出しすると、更に10秒後にstartCheck関数を呼ぶという動きになり、10秒間隔の無限ループに入ります。
普通に無限ループさせる場合と比較して
- 一定時間ごとに処理を行うのでハングしにくい(Sleep不要)
- 処理が一旦終わるのでメモリ解放される
- 応答なしの考慮が不要(DoEvents不要)
というメリットが有るかと思います。
ファイルの容量が増えなくなったらダウンロード完了
一瞬でダウンロードが終わるファイルであればいいのですが、仮に大容量のファイルだったとします。
その場合、ダウンロードまで時間がかかりますからファイルが置かれたら即発火することが出来ません。
「ダウンロードが完了した」というのは、ちょっと前のファイル容量を比較して変化なかったら完了とするしか無いかなあと思います。
Public d As Date
Public filesize As Long
Public Sub startCheck()
Dim buf As String
Const FILEPATH = "C:\test.txt"
d = Now() + TimeSerial(0, 0, 10)
If (Dir(FILEPATH) <> "") Then
If (filesize = FileLen(FILEPATH)) Then
call endCheck
'ファイル容量が前回と同じなら取り込みする
Open FILEPATH For Input As #1
Line Input #1, buf
Worksheets("Sheet1").Cells(1, 1).Value = buf
Close #1
Else
'ファイル容量が前回と違えば何もしない
filesize = FileLen(FILEPATH)
End If
End If
Dir (vbNullString)
Call Application.OnTime(d, "startCheck")
End Sub
Public Sub endCheck()
Call Application.OnTime(d, "startCheck", , False)
End Sub
FileLenでファイルサイズを取得して一旦モジュール変数に格納。
更に次にファイルサイズを取得したときに全く同じであれば、取り込み処理を実施するという流れです。
常時実行のためブックは非表示としたい
こんな感じで常時実行させたり、定期実行させたりするVBAを作るのは稀です。(冒頭で書いたとおりVBSでやれば良いので)
もし、その稀なパターンに当たってしまい、ユーザーが発火しないVBAコードを書くことになったらブックは非表示にした方がいいと思います。
そうしないとExcelを終了させられてしまう可能性もあります。
ブックを非表示にするには、結局VBSを使ったほうが一番楽といいうことになりますが…。
Dim Obj
Set Obj = WScript.CreateObject("Excel.Application")
Obj.Visible = false
CreateObject("WScript.Shell").AppActivate Obj.Caption
Obj.Workbooks.Open "C:\作成したVBAのファイル.xlsm"
Obj.Application.Run "startCheck"
ファイル監視ではなく、HULFTの集信後ジョブで実行したほうが確実ではないしょうか?
ファイルの完成はHULFTが担保してくれるので、仮に転送が不安定でサイズが増えない時間があっても誤発火しませんし。
おっしゃるとおりHULFTの集信後ジョブのほうが良さそうですね。
仮にディスクの容量不足でファイル配置が中止になるということもゼロではなさそうですし。
あまり詳しくないですがEXEファイルも叩けた気がするので、何でもできそうですね。
今回のを無理やり使うとしたらFTPとかファイル共有で配置されたときくらいですかねえ。
そうですね。せっかく高くて高機能な製品使っているのでそちらを利用するのが良いかと。
>今回のを無理やり使うとしたらFTPとかファイル共有で配置されたときくらいですかねえ。
FTPやファイル共有だとこのような運用考慮が必須なので使い所はたくさんあると思いますよ。