【VBAリファレンス】ExcelリファレンスGoogle Calendar iCal形式で祝日取得

ExcelからGoogle CalendarのiCal形式を活用して祝日データを自動取得する高度な手法

Excelでスケジュール管理や工数管理システムを構築する際、避けて通れないのが「日本の祝日」の判定です。多くの開発者が内閣府のCSVファイルをダウンロードして読み込む手法をとりますが、この方法には「更新のたびに手動操作が必要」「リンク切れのリスク」「長期的なメンテナンスコスト」という課題があります。

本稿では、Google Calendarが提供する公開iCal（.ics）形式のデータへ、VBAから直接HTTPリクエストを送信し、解析することで、常に最新の祝日データを自動取得するプロフェッショナルな実装手法を解説します。

iCal形式の構造と解析の理論的背景

Google Calendarが提供する日本の祝日カレンダーは、RFC 5545で定義されたiCalendar（iCal）形式です。このテキストデータは、特定の見出し（BEGIN:VEVENT）から始まり、終了（END:VEVENT）するブロックの集合体です。

主要なデータ構造は以下の通りです。
・DTSTART: 祝日の日付（YYYYMMDD形式）
・SUMMARY: 祝日の名称

VBAでこれを扱う場合、単純な文字列操作では改行コードやエンコードの問題に直面します。特に日本語が含まれる場合、UTF-8で配信されるため、これをShift-JIS環境のVBAで正しく処理するにはADODB.Streamオブジェクトを用いたバイト変換が不可欠です。DOMやXMLパーサーを使わず、正規表現と文字列操作を組み合わせることで、外部ライブラリに依存しない堅牢な実装が可能になります。

実装のための技術的アプローチ

今回の実装では、MSXML2.XMLHTTPを使用してインターネット上のカレンダーデータにアクセスします。取得したバイナリデータをADODB.Streamでテキストに変換し、正規表現を用いて日付と名称を抽出します。

この手法の最大の利点は、Excelファイルを開くたび、あるいはボタン一つで常に最新の祝日情報が取得できる点です。Google側のURLが変わらない限り、一度構築すれば半永久的にメンテナンスフリーで稼働します。

サンプルコード：iCal解析による祝日取得実装

以下のコードは、標準モジュールに貼り付けて使用してください。Microsoft XML, v6.0およびMicrosoft ActiveX Data Objects 6.1 Libraryへの参照設定が必要です。

Option Explicit

' 必要な参照設定:
' 1. Microsoft XML, v6.0
' 2. Microsoft ActiveX Data Objects 6.1 Library

Public Sub GetJapanHolidays()
    Dim url As String
    Dim http As MSXML2.XMLHTTP60
    Dim adoStream As ADODB.Stream
    Dim responseText As String
    
    ' Google Calendar 日本の祝日（公開iCal）
    url = "https://calendar.google.com/calendar/ical/ja.japanese%23holiday%40group.v.calendar.google.com/public/basic.ics"
    
    Set http = New MSXML2.XMLHTTP60
    http.Open "GET", url, False
    http.send
    
    If http.Status <> 200 Then
        MsgBox "祝日データの取得に失敗しました。" & vbCrLf & "ステータスコード: " & http.Status
        Exit Sub
    End If
    
    ' UTF-8のバイナリを文字列へ変換
    Set adoStream = New ADODB.Stream
    adoStream.Type = adTypeBinary
    adoStream.Open
    adoStream.Write http.responseBody
    adoStream.Position = 0
    adoStream.Type = adTypeText
    adoStream.Charset = "UTF-8"
    responseText = adoStream.ReadText
    adoStream.Close
    
    ' 解析処理（簡易的な抽出）
    Call ParseICalData(responseText)
End Sub

Private Sub ParseICalData(ByVal iCalData As String)
    Dim lines() As String
    Dim i As Long
    Dim dtStr As String, summary As String
    Dim ws As Worksheet
    Dim rowIdx As Long
    
    Set ws = ThisWorkbook.Sheets(1)
    ws.Cells.Clear
    ws.Range("A1:B1").Value = Array("日付", "祝日名")
    rowIdx = 2
    
    lines = Split(iCalData, vbCrLf)
    
    For i = LBound(lines) To UBound(lines)
        If InStr(lines(i), "DTSTART;VALUE=DATE:") > 0 Then
            dtStr = Replace(lines(i), "DTSTART;VALUE=DATE:", "")
            ' YYYYMMDDを日付形式に変換
            dtStr = Left(dtStr, 4) & "/" & Mid(dtStr, 5, 2) & "/" & Right(dtStr, 2)
        End If
        
        If InStr(lines(i), "SUMMARY:") > 0 Then
            summary = Replace(lines(i), "SUMMARY:", "")
            ws.Cells(rowIdx, 1).Value = DateValue(dtStr)
            ws.Cells(rowIdx, 2).Value = summary
            rowIdx = rowIdx + 1
        End If
    Next i
    
    MsgBox "祝日データの更新が完了しました。"
End Sub

実務における高度なアドバイスと注意点

1. キャッシュの制御：頻繁にリクエストを送るとGoogle側から一時的にブロックされる可能性があります。実務では「取得したデータを一度シートに保存し、現在の日付と比較して、最終更新から30日経過している場合のみ再取得する」といったロジックを組み込むのがプロの流儀です。

2. エラーハンドリング：インターネット環境は常に不安定です。オフライン環境やプロキシ環境下での実行を想定し、On Error GoTo句によるエラートラップを必ず実装してください。

3. データの加工：iCalデータには、祝日以外にもイベントが含まれる場合があります。抽出したデータに対して、VLOOKUPやCOUNTIFで利用しやすいように、日付型として明示的に変換（DateValue関数）しておくことが重要です。

4. 参照設定の代替案：参照設定が煩わしい場合は、CreateObjectを用いた「遅延バインディング」を採用してください。配布先のPC環境に依存せず、コードのポータビリティが格段に向上します。

まとめ

Excel VBAを用いたGoogle Calendarからの祝日取得は、単なる自動化を超えた「外部API連携の第一歩」です。内閣府のCSV配布に依存する従来の手法から脱却し、iCalという標準規格を活用することで、システムの保守性は飛躍的に向上します。

プロフェッショナルなエンジニアとして、常に「手動作業をいかにゼロにするか」を追求し、このような外部リソースを賢く利用するアーキテクチャを設計してください。本稿のコードをベースに、皆様の業務環境に合わせてカスタマイズを行うことで、より高度なカレンダー管理ツールが構築できるはずです。技術的な探究心を忘れず、堅牢で美しいコードを書き続けてください。