祝日データ取得
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分程度かかるので、そこが悩みどころです。
![](/note/forme_blog.png)
汎用的な方法とは言えませんが、こんなアイデアがあるのだと思っていただければありがたいです。