とある業務で状況を確認する必要があったため、サーバに格納したデータを別室のブラウザにHTML形式で出力するプログラムを組んでみました。
下記のサンプルデータを用意して、CSV形式にした表を読み取りHTMLに置き換える。
全体図
必要となる物を書き出す
- 一定時間ごとに更新
- データが追加されても全てを配列に格納
- HTMLファイルを作成
- HTMLも時間ごとに更新
- HTMLヘッダーの設定
- HTMLテーブルに追加
プログラム全体
Option Explicit
' 1分ごとにサブルーチンを実行するプロシージャ
Sub Update()
Application.OnTime Now + TimeValue("00:01:00"), "HTML"
Debug.Print Now & ":実行しました。"
End Sub
ーーーーーーーーーーーーーーーーーーーーーーーーーーーーーー
' HTMLコードの書き出しプロシージャ
Sub HTML()
Dim Wbk As Workbook
Dim Wst As Worksheet
Dim htmlFile As Object
Dim htmlPath As String
Dim data As Variant
Dim rowHtml As String
Dim ir As Long, ic As Long
Dim lRow As Long, lCol As Long
' Excelファイルのパス
Set Wbk = ThisWorkbook ' このコードが実行されるExcelファイル
Set Wst = Wbk.Worksheets("Sheet1") ' データがあるシート名を指定
' データを配列に読み込む
lRow = Wst.Cells(Rows.Count, 1).End(xlUp).Row
lCol = Wst.Cells(2, Columns.Count).End(xlToLeft).Column
data = Wst.Range(Cells(2, 1), Cells(lRow, lCol))
' HTMLファイルのパス
htmlPath = "C:\Users\・・・・\test.html"
' HTMLファイルの作成
Set htmlFile = CreateObject("Scripting.FileSystemObject").CreateTextFile(htmlPath, True)
' 60秒更新のHTMLコードの作成
htmlFile.writeline "<!DOCTYPE html>" & vbCrLf & _
"<html>" & vbCrLf & _
"<head>" & vbCrLf & _
"<meta http-equiv=""refresh"" content=""60"">" & vbCrLf & _
"<title>自動テーブル更新</title>" & vbCrLf & _
"</head>" & vbCrLf & _
"<body>"
'HTMLヘッダー
htmlFile.writeline "<table border=""1"">" & vbCrLf & _
"<caption>管理情報</caption>" & vbCrLf & _
"<tr><th>管理番号</th><th>性別</th><th>年齢</th><th>血液型</th><th>都道府県</th></tr>" & vbCrLf
' データをHTMLテーブルに追加
For ir = LBound(data, 1) To UBound(data, 1)
rowHtml = "<tr>"
For ic = LBound(data, 2) To UBound(data, 2)
rowHtml = rowHtml & "<td>" & data(ir, ic) & "</td>"
Next ic
rowHtml = rowHtml & "</tr>"
htmlFile.writeline rowHtml
Next ir
' テーブルの閉じタグを追加
htmlFile.writeline "</table>" & vbCrLf & _
"</body>" & vbCrLf & _
"</html>"
htmlFile.Close
' Updateに戻る
Update
End Sub
一定時間ごとに更新
目的を達成するために一番必要となる一定時間ごとに更新するプロシージャを作成、Now(現在の日時)にTimeValueで1分ごとにHTMLを実行するように設定します。
最後にUpdateを呼び出す事でプロシージャ同志がループするため、一定時間ごとの更新作業が可能となる。
Option Explicit
' 1分ごとにサブルーチンを実行するプロシージャ
Sub Update()
Application.OnTime Now + TimeValue("00:01:00"), "HTML"
Debug.Print Now & ":実行しました。"
End Sub
ーーーーーーーーーーーーーーーーーーーーーーーーーーーーーー
Sub HTML()
・・・
' Updateに戻る
Update
End Sub
可視化しないと動いてるのか判りにくいため、Debug.Printで実行時間を表示してみたところ、1分間隔で実行できていることが確認できます。
出力されるHTMLファイル
meta文のrefreshが更新、contentが秒の設定なので、これで60秒毎に更新となる。
'HTMLファイル
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="refresh" content="60">
<title>自動更新テーブル</title>
</head>
<body>
<table border="1">
<caption>管理情報</caption>
<tr><th>管理番号</th><th>性別</th><th>年齢</th><th>血液型</th><th>都道府県</th></tr>
<tr><td>1</td><td>女</td><td>65</td><td>A型</td><td>福岡県</td></tr>
<tr><td>2</td><td>女</td><td>41</td><td>AB型</td><td>宮城県</td></tr>
<tr><td>3</td><td>男</td><td>80</td><td>B型</td><td>鹿児島県</td></tr>
<tr><td>4</td><td>女</td><td>27</td><td>O型</td><td>東京都</td></tr>
<tr><td>5</td><td>女</td><td>75</td><td>A型</td><td>兵庫県</td></tr>
<tr><td>6</td><td>女</td><td>54</td><td>O型</td><td>愛知県</td></tr>
<tr><td>7</td><td>男</td><td>70</td><td>B型</td><td>東京都</td></tr>
<tr><td>8</td><td>女</td><td>60</td><td>B型</td><td>山口県</td></tr>
<tr><td>9</td><td>男</td><td>41</td><td>O型</td><td>兵庫県</td></tr>
<tr><td>10</td><td>男</td><td>80</td><td>A型</td><td>埼玉県</td></tr>
</table>
</body>
</html>
CSVの管理番号11、12のリストを追加しVBAとHTMLが60秒後にどうなるかを確認する。
無事更新できている様ですね。あとはWebサーバ上にこれを送って別ブラウザで表示することが出来れば今回行いたかった作業は一旦完了です。
追加・改良すべき点が何点かあります。
- VBA上で動いている為、Excelファイルがタスクバー上に常駐している
- HTMLが必要最低限しか書いていないため、言語指定などをしっかりする
- CSVを追加中に60秒経つと更新するため、中途半端なデータが飛ぶ
フォームの追加や勉強中のC#を使用する事で解決できそうですが、少し先の話になりますね。
お読みいただきありがとうございました。