祝日データ取得

May 14, 2024

excel Excel for Mac Mac QC KAIZEN

祝日データ取得

Excelでスケジュール関連のシートを作成する際に祝日データを参照する事が多いと思います。手動で入力する場合もあるかもしれませんが、VBAを使って自動化してみましょう。

祝日データ取得VBAコード

祝日などは法律により変わる場合もある為、必要に応じて更新できるようにするにします。祝日データ自体はExcelApi様が提供しているAPIを用いて取得します。このままWEBSERVICE関数を使ってシートにから直接参照するのも良いですが、結構な範囲を使う点とオフライン時やExcelApi様への負担軽減を考慮するならば、マクロを走らせた場合のみ更新するのが適切です。

更にWEBSERVICE関数はExcel for Macでは利用できないので代替方法が必要になります。今回はFormeStudioを利用することでこの点を解決します。それでは具体的なコードは以下になります。

Sub SetupHoliday()

On Error GoTo gc_error

Dim hl As ListObject
Dim trec As ListRow
Dim od As Date
Dim hs As String: hs = ""
Dim s As Integer, z As Integer: z = 1350'向こう約3年間の祝日取得
Dim hns() As String
Dim f As Boolean: f = False

od = Range("origindate").value'起点の日付を取得

ReDim hns(z)

If ThisWorkbook.Worksheets("HOLIDAY").ListObjects.Count > 0 Then
    Set hl = ThisWorkbook.Worksheets("HOLIDAY").ListObjects(1)'HOLIDAYシートのテーブルに祝日を貼り付けていく。
    hs = FormeWebApi("https://api.excelapi.org/datetime/holiday?date=" & od)
    If InStr(hs, "ERROR") = 0 Then
        For s = 0 To z
            Range("A1").value = "サーバーにアクセス中(" & (s + 1) & "/" & z & ")"
            hs = FormeWebApi("https://api.excelapi.org/datetime/holiday?date=" & od + s)
            If InStr(hs, "タイムアウト") > 0 Or hs = "プロシージャの呼び出し、または引数が無効です。" Then
                f = True
                Range("A1").value = "インターネット接続エラーにより更新に失敗しました。"
                Exit For
            End If
            hns(s) = hs
        Next
    Else
        f = True
        Range("A1").value = "サーバーエラーにより更新に失敗しました。"
    End If
    Range("A1").value = "祝日データの展開準備中..."
    If Not (f) Then
        If Not hl.DataBodyRange Is Nothing Then
            Range("A1").value = "祝日テーブルを初期化..."
            hl.DataBodyRange.Delete
        End If
        For s = 0 To z
            Range("A1").value = "祝日データを検査中(" & (s + 1) & "/" & z & ")"
            If hns(s) <> "" And InStr(hns(s), "ERROR") = 0 Then
                Range("A1").value = "祝日データを書き込み中..."
               Set trec = hl.ListRows.Add
               With trec
                   .Range(1).value = od + (s)
                   .Range(2).value = hns(s)
               End With
           End If
        Next
        Range("A1").value = Now() & "にデータ更新を行いました。"
    End If
End If

Exit Sub
gc_error:
    MsgBox ("ERROR が発生しました。インターネット接続を確認して下さい。: " & Error(Err.Number))

End Sub

このコードはFormeStudio上でコーディングすることを想定したものであることを留意してください。またExcel for Macでは先にシート上でFORMEAPI関数を実行してスクリプトファイルを先にインストールする必要がある場合があります。

このコードはApiがアクセス可能な場合のみに休日データ用テーブルをリセット後に休日データのみを向こう3年間分記入するものです。これによりExcelApi様への負担軽減と祝日データの入力という手間から開放されます。ただしExcel for Macではスクリプトの関係上10分程度かかるので、そこが悩みどころです。