前回の記事でエクセルVBAマクロを使う為の準備、簡単なプログラムを動かすことで、マクロのイメージをなんとなく持っていただけたかと思います。
今回は、
はてなブログの記事をリンク付きの一覧表にまとめる
→ブログ貼り付け用のHTMLソースをつくる
を説明していきたいと思います。
完成のイメージは本ブログのタイトル下の「全記事一覧」ボタンを押して表示されるページになります。
プログラムの作成、それに伴う作業、実行結果については自己責任でお願いします。
おさらい
プログラムを作成するにあたり、エディター（VisualBasicEditor）と空のマクロ（例：Macro1）を準備するところから始まります。
分からない、忘れてしまった方は、前回の記事の「マクロを作る準備」を参照してください。以下のリンクから、別ウィンドゥで表示できます。
どうやるの？
どうやって、なてなブログの記事をリンク付きでリスト化するのか？
記事をリスト化するにはタイトル、日付もあった方がいい、リンク付きならリンク先のアドレスが必要、、それのもとになる情報源がどこにあるか？
はてなブログなら、ダッシュボードに必要な情報がありそうです。記事の管理にいくと今までの全記事が表示されています。これをコピーして使えばOKですが、画像とかもあって重そうです。
もう少し軽いテキストファイルはないか？
探してみるとありました！
同じダッシュボード内の「設定－詳細設定ーエクスポート」を利用します。
記事情報を取得
実際に記事情報を取得してみます。
ダッシュボードから、設定→詳細設定を選択します。
下の方へスクロールしていくと、「エクスポート」があるので、「記事のバックアップと製本サービス」をクリックします。
画面の上の「エクスポートしなおす」ボタンを押すと、作成日時が更新されるので、ダウンロードボタンを押し、記事情報のファイルを取得します。
←ブログの記事情報のファイルが取得できます。
ファイルの中身を確認
ダウンロードしたファイルをメモ帳などのテキストエディタで開くと、ブログの記事がずらーっと並んで書かれているかと思います。
見てみると左端に項目名が規則的に並んでいます。その中で必要な下記の項目を抽出していけばよいことが分かります。
TITLE：記事のタイトル名称
BASENAME：記事のアドレス
STATUS：公開（Publish）か非公開（Draft）
DATE：記事の投稿日
CATEGORY：カテゴリ（複数ある場合は2行以上）
人間ならこれを見て、上から順番にSTATUSが公開（Publish）の所の、TITLEなどを抜き出そう、1記事の区切りはBODY：の所までだなぁと構想するかと思います。これをマクロに代わりにやってもらうために、プログラムを作成します。
プログラム設計
人がやったらこうだよね、という処理をマクロでやるために、どういった流れでプログラムを作るかを考えます。聞いたことがあるかと思いますが、フロー図、フローチャートと呼ばれるものです。
今回はそんなに難しくない処理なので、フロー図は作成せずプログラム作成しましたが、複雑な処理を考えるときは作成した方が、後々、デバッグ等でも効率的です。
プログラム作成
記事情報のファイルから、記事一覧表や、HTMLソースを生成するプログラムを作ってみます。
エクセルVBAの良さは、人間がやった方が効率が良い、マクロの方が良いなど、分担作業を考えてマクロを組むことが可能な点です。例えば、ページから情報を取得する際、どこまでコピーするかの判断は人間でやった方が効率的です。マクロでロジックを組む方が面倒です。
その考えのもと、今回も記事情報を取得してシートに貼り付けるは人間、そこから整列などの処理をマクロで実施します。
下準備
まずは下準備として、エクセルに下記３つのシートを作成します。シートそれぞれに役割があります。
・data ：記事情報ファイル貼り付け用
・matome ：記事一覧表用
・html ：html生成用
シート名は任意ですが、プログラム内で名称を使用するため共通化しておく必要があります。
シートの準備ができたら、「data」シートに先ほどの記事情報ファイルの中身をそのまますべて選択し、”A1"（一番左上）で貼り付けます。下記のような状態になればOKです。
これで下準備完了です。
プログラム作成に取り掛かります。
完成プログラム
作成したプログラムはこちらです。詳細については後述します。まずはエディタに記述し、実行してみてください。
◆記事一覧作成プログラム（全76行：下にスクロールして表示できます）
- Sub Macro1()
- '*************************
- '* ブログ記事一覧作成 *
- '* Prg by pisukechin *
- '*************************
- Dim row1, row2, eol As Long
- Dim adr, data, title, basename, status, udate, category As String
- Set st_data = Sheets("data")
- Set st_matome = Sheets("matome")
- Set st_html = Sheets("html")
- adr = "https://www.pisukechin.com/" '<<<ブログのアドレス
- row1 = 1
- row2 = 1
- '***** 一覧表作成（matome） *****
- eol = st_data.Cells(Rows.Count, "A").End(xlUp).Row
- Do Until row1 = eol
- data = Mid(st_data.Cells(row1, 1), 1, 5)
- Select Case data
- Case "TITLE"
- title = Mid(st_data.Cells(row1, 1), 8)
- Case "BASEN"
- basename = Mid(st_data.Cells(row1, 1), 11)
- Case "STATU"
- status = Mid(st_data.Cells(row1, 1), 9)
- Case "DATE:"
- udate = Mid(st_data.Cells(row1, 1), 7, 10)
- Case "CATEG"
- category = category & Mid(st_data.Cells(row1, 1), 11) & ","
- Case "BODY:"
- If status = "Publish" Then
- With st_matome
- .Cells(row2, 1) = udate
- .Cells(row2, 2) = title
- .Cells(row2, 3) = category
- .Cells(row2, 4) = adr & "entry/" & basename
- End With
- row2 = row2 + 1
- category = ""
- End If
- End Select
- row1 = row1 + 1
- Loop
- '***** 一覧表用のHTMLコード生成（html） *****
- st_html.Cells(1, 1) = "<ul>"
- For i = 1 To row2
- With st_matome
- st_html.Cells(i + 1, 1) = "<li>" & Str(row2 - i) & " [" & .Cells(i, 1) & _
- "]<br><a href=" & .Cells(i, 4) & ">" & .Cells(i, 2) & _
- "</a><br><br></li>"
- End With
- Next i
- st_html.Cells(row2 + 1, 1) = "</ul>"
- End Sub
どうですか？
空だったシート「matome」と「html」に何か書かれましたよね。
matomeシートにはブログの全記事のタイトル、カテゴリ、URLが一覧表示されています。htmlシートには、htmlソースが生成されているかと思います。これを全コピーしてブログに貼り付けると、全記事一覧表がリンク付きで表示されるようになります。
最初からやり直す場合は、matomeとhtmlシートの内容を空にして、マクロを動かせば、何度でも自動で作成してくれます。
プログラムの説明
プログラムの中身を説明していきたいと思います。
コメント（3～6行）
’（シングルクォーテーション）を先頭につけるとコメント分となり、プログラムでは無視されます。何をするプログラムなのかを覚書しておくと便利です。
’◇ちゃんと動いているか見ている部分だよ◇
初期設定（8～18行）
プログラム内で使う変数などを定義しています。
Dim row1, ・・・ as long
Dim adr,・・・ as string
変数の型を宣言しています。row1は長整数（long）、adrは文字（string）という感じです。カンマ区切りで複数宣言ができます。
VBAの場合、宣言しなくても動きますが、バグの原因になることもあります。
Set st_data = Sheets("data")
シートを選択するsheets("～")をいちいち書くのが面倒なので、変数（st_dataなど）に割り当てます。名前は自由に決められます。Sheets("data").cells(2,6)・・・をst_data.cells(2,6）・・・と書き換えできます。
adr = "https://www.pisukechin.com/"
記事情報ファイル内のアドレスはBASENAMEですが、フルパスになっていません。そこで後でフルパスにするため、adrに先頭アドレスを入れます。
row1 = 1
処理をしている行を記憶する変数です。処理は基本、シートの一番上（1行目）から最後の行まで処理をする流れです。今どこなのか、この変数を１ずつカウントを増やして把握します。
一覧表作成（20～59行）
matomeシートに一覧表を作る処理です。記事情報ファイル（dataシート）を上から最後の行までチェックし、matomeシートに書き出します。
eol = st_data.Cells(Rows.Count, "A").End(xlUp).Row
データが何行まであるか調べてその行数を変数eolに入れています。A列を上から下まで走査して行数を求めています。
Do Until row1 = eol ～ Loop
これはrow1（今どの行を処理しているか）がeol（最終行）にくるまで、Do ～ Loop内を繰り返します。
data = Mid(st_data.Cells(row1, 1), 1, 5)
Mid（A,B,C）は、Aの中身の文字列のB文字目からC文字分を抜き出す、という意味です。例えば、Mid("TEST12345",4,3)は、”T12”となります。ここでは、各行が何のデータ（STATUS？、TITLE?）かを判断するための5文字を抜き出して変数dataに入れています。
Select Case data ～ End Select
case "TITLE"
：
dataの中身によって処理を分けています。もし、TITLEなら、title = Mid(st_data.Cells(row1, 1), 8)が処理されます。この場合のMid(A,B)はAの文字列のB番目以降を抜き出すという意味です。TITLEの項目は「TITLE: 集まれ！～懐かし思い出の携帯～」で8文字目からの「集まれ！～懐かし・・・」を抜き出します。分かりにくいですが：の後にスペースがあります。
Case "BODY:"
If status = "Publish" Then
With st_matome
.Cells(row2, 1) = udate
：
.Cells(row2, 4) = adr & "entry/" & basename
End With
row2 = row2 + 1
category = ""
End If
変数dataがBODYとなった場合、ここを１つの記事の区切りとして認識させています。リストに挙げるかは変数statusがPublish（公開）かで判断しています。それ以外のDraftだとif ～ endifの間のリストに書き込む処理は実施しません。
.Cells(row2, 4) = adr & "entry/" & basenameで、ブログの先頭アドレスを入れた変数adrと"entry/"とbasenameをくっつけたフルパスをセルに入れています。
row2 = row2 + 1は、1行分の処理が終わったので、次の行という意味で１を足しています。
これらの処理を変数row1（処理している行）が最終行にくるまで繰り返し実施し、一覧表を自動で生成しています。
HTMLソース生成（61～73行）
作成した一覧表（matome）をもとに、ブログ等に貼ったらそのまま一覧表のページになるよう、HTMLソースをシートhtmlに生成します。
st_html.Cells(1, 1) = "<ul>"
st_html.Cells(row2 + 1, 1) = "</ul>"
ブログページにリスト表示させるため、HTMLタグの<ul></ul>と<li></li>を使用します。<ul>と</ul>で全体を囲む必要があるため、先頭と最終に書いています。
row2は一覧表作成時に出てきた変数で、現在、ここには最終行の数字が入っており、<ul>で囲うためひとつ多いrow2+1を行指定しています。
For i = 1 To row2
With st_matome
st_html.Cells(i + 1, 1) = "<li>" & Str(row2 - i) & " [" & .Cells(i, 1) & _
"]<br><a href=" & .Cells(i, 4) & ">" & .Cells(i, 2) & _
"</a><br><br></li>"
End With
Next i
ここはひとつひとつの記事をリスト化している部分です。記事数は変数row2で分かっているので、For ～ Nextで記事数分を繰り返します。<li></li>タグで、記事No（row2-i）、投稿日、タイトルなどをmatomeシートから引っ張ってきて、＆記号を使ってくっつけています。
以上がプログラムの説明になります。比較的分りやすいプログラムかと思います。上から順番に追って行けば理解できるかと思います。
まとめ
エクセルVBAマクロであそぼう！
記事一覧リンク一覧を作る、実践編をお届けしました。
マクロを使うことで手作業入力で時間がかかった作業も、マクロの自動処理であっという間に終わってしまいます。今回の使い方以外にもプログラム次第で、色々な自動処理を作成できるかと思います。
自動処理をするプログラム言語は色々とありますが、エクセルVBAマクロの良いところは、
・エクセルがあれば使える
・人間とマクロの作業分担が効率的
・グーグル先生がいっぱい！
かと思います。
プログラムで組むよりエクセルの機能（関数計算）を使った方が、マクロのロジックを考えず結果的に効率的な場合もあります。本当に単純な繰り返しをマクロにやってもらえば良いかと思います。
マクロを組んでいて分からないことがあれば、グーグルなどで検索すると答えがみつかる可能性が高いです。それだけ、多くの方が利用し、グーグル先生がネット上にたくさん居てくれています。
今回の記事で、エクセルVBAマクロやってみようかなぁと少しでも思っていただけたら嬉しいです。最後まで読んでいただき、ありがとうございました。
あわせて読みたい
