エクセルマクロ、VBAを利用してHTML自動出力（その1）

以前の投稿「エクセルでのHTML生成」で関数を使って同じフォーマットのHTMLの制作効率をアップする説明はしましたが、今回はそれの全自動版です。

利用シーンの確認

どんな状況でも使えるというものではありません。

一番効果があるのは一覧画面と詳細画面があり、詳細画面は同じようなフォーマットである前提で、掲載内容の変更が定期的に発生する場合です。

また、変更内容をできればエクセル、最悪メールやテキストなどのデータでいただけることも大事です。

利用方法

エクセルは追加分と追加前の2つのリストを用意しておきます。

HTMLに埋め込む情報をあらかじめ1アイテム1レコードにしてください。

各アイテムの情報は一致することも条件になります。

私の場合は、追加分シートに追加更新するアイテム情報を入力し、まずはこのシートで詳細画面のHTMLの生成を行いました。

次に追加分のレコードを追加前のレコードに追記し、追加分のシートを空にします。

その後追加前シートより一覧画面のHTMLを生成します。

大まかには以上の2ステップです。

エクセルのレコード計算

今回のVBAでもっとも多く使い、今後の最も多く使うであろう処理が、エクセルに実際に何行記述が入っているかを計算する方法です。

まずは行数を覚えさせるための変数を定義します。今回は2つの変数を使うので以下のようになります。

Dim i As Long
Dim r As Long

なぜ2ついるかは後述しますが、iとrの2つの変数をLong形式で定義しました。

次に繰り返し処理でセルの値を確認し、値がない＝レコード終了とします。

For i = 1 To 1000 '次の行で指定するセル列の1行目～1000行目をiに代入していきます。
Range("A" & i).Select 'A列のi行（はじめは1行目）を選択
If Selection = "" Then '選択しているセルの値を確認し何もなし（""）であれば

r = i - 1 '変数rに変数iから1を引いた値を代入

Exit For 'Forの繰り返しから抜け出し

End If

Next i

繰り返し処理で1行目から1000行目を順番に見ていく処理を行い、空白セルを見つけたら変数rに変数iの1少ない数をセットします。これはiはすでに値のないセルの行数を取ってしまっているために

-1します。

空白セルが見つかるまでは繰り返し処理を行い、変数iは1から順番に数値が代入されていきます。

これで変数rはレコード記述のある最終行を取得できていることになります。

ファイルの読み込み

先ほどのエクセルレコードの計算で今回の開発の50％ほどは達成しました。

残りの50％のファイル操作処理を行います。

まずファイルの読み込みはHTMLタグの記述されたテンプレートを読み込むために使います。

テンプレートの内容はエクセルの特定のセルに一度コピーし、関数により値を取得するので通常のタグではなく関数が記述されたタグで用意してください。

関数が記述されたタグについては「エクセルでのHTML生成」を確認してください。

本題に戻り、まずはファイルの読み込みを行います。

Dim GOF As String
GOF = Application.GetOpenFilename(FileFilter:="textファイル,*.txt", Title:="テンプレートファイルの読み込み")

特定のパスに特定のファイル名でテンプレートを常に置いておき自動的に読み込みするという方法でも問題ないのですが、テンプレートファイルが正確に読み込めないと今回のシステムは全て機能しないので、一応ダイアログを出して確認するようにします。.GetOpenFilenameがそのメソッドになります。

Dim buf As String 'テンプレートの内容を一時的に保存する変数
With CreateObject("Scripting.FileSystemObject")
With .GetFile(GOF).OpenAsTextStream

buf = .ReadAll

.Close

End With

End With

今回はWithステートメントを使っています。オブジェクト型変数という方法でも記述可能なようなので、気になる方は調べてみてください。

WithはEnd Withまでの間、Withの後ろで記述している内容について処理するという意味になります。

順に説明します。

まずCreateObjectメソッドは外部ファイル扱うためのメソッドです。引数のFileSystemObjectでインスタンスを作成することになります。

次は、先にダイアログで指定したファイルをGetFileで開きます。OpenAsTextStreamはテキストを開くよということです。パーミッション設定を引数で入れれますが、今回は使わないので引数はなしです。

次にに変数bufにテキストファイル（今回はテンプレート関数）を入れます。

開いていたファイルを閉じます。という感じです。

オブジェクト、クラス、インスタンスの説明が難しいですね。私もぼんやりしか理解していないので。。

あとは変数bufに代入されたテキストファイルの中身をセルにセットすれば完了です。

Range("X1") = buf

こんな感じです。

ファイルの保存

ファイルの保存は簡単です。

Openステートメントを使います。

Open ファイル名 For やること As ファイル番号

で定義できます。

ファイル名は同名のファイルが存在しない場合は自動的にファイルを作成してくれます。

ForのやることはInput、Output、Appendの3つを覚えておけば大丈夫と思います。以下記述例です。

Dim f_path As String
f_path = ActiveWorkbook.Path & "\" & "list.txt" 
Open f_path For Output As #1

Print #1, Range("Y1").Value

Close #1

これは説明なしでも大丈夫でしょう。

次の記事では実際のソースとその説明をします。