VBA研究会で論じられた内容を記録する議事録の6本目(2023/02/18開催分のおまけ)です。前回の議事録はコチラになります。
前回は予定表のひな型をVBAで自動作成してみました。ひな形の作成を自動化できると便利ですね。
その中で、一年の休日リストを用意する必要がありました。前回の例ではコチラのサイト(https://www8.cao.go.jp/chosei/shukujitsu/gaiyou.html)から情報を取得して用意しました。ですがこの中で、おもしろいWebサービスを見つけました。今日は、そちらをご紹介しようと思います。
ExcelAPI
その名も「ExcelAPI」と言います。
「ExcelAPI」は、以下の環境で使えます。
- ネット接続環境下にある
- Excel 2013以降(WEBSERVICE関数が使用可能)
- 1日1万アクセスまで無料!!
試しにExcelで新しいブックを開いて、任意のシートのセルに以下の数式を入力してください。
=WEBSERVICE("https://api.excelapi.org/datetime/holiday?date="&"2023/1/1")
該当するセルには「元日」と出たと思います。
これはWEBSERVICE関数で以下のURLを使用して祝日の名称を取得したものです。
WEBSERVICE関数はネット上のWebサービスからデータを返す関数です。
Googleスプレッドシートの場合
Googleスプレッドシートの場合はシートのセルに以下の数式を入力してください。
=IMPORTXML("https://api.excelapi.org/datetime/holiday?date=2023/01/01",".")
同様にセルには「元日」と出たと思います。
カレンダーを作る
それではWEBSERVICE関数とExcelAPIを使ってカレンダーを作ってみましょう。以下のコードをシートに記述してください。
'カレンダーを作成 Public Sub SetCalendar() Me.Cells.Clear Me.Range("A1").Value = Year(Now) Me.Range("A2").Value = "日付" Me.Range("B2").Value = "曜日" Dim yearData As Long yearData = Year(Now) '初日を設定 Dim dateData As Date dateData = CDate(yearData & "/1/1") '先頭位置を設定 Dim rng As Range Set rng = Me.Range("A2") Dim rowCount As Long: rowCount = 0 '各日付を設定 Do '日付の表示 rng.Offset(rowCount, 0).Value = dateData rng.Offset(rowCount, 1).Value = Format(dateData, "aaa") rng.Offset(rowCount, 2).FormulaR1C1 = _ "=WEBSERVICE(""https://api.excelapi.org/datetime/holiday?date=""&RC[-2])" rowCount = rowCount + 1 dateData = dateData + 1 Loop While Year(dateData) = yearData '年の終わりまで処理 Me.Columns("A:C").EntireColumn.AutoFit 'WEBSERVICE数式を削除 Me.UsedRange.Value = Me.UsedRange.Value End Sub
結果は以下の様になったと思います。
ツールに適用する
上記のカレンダーを適用して、前回の予定表のひな形ツールを作ってみました。興味のある方はダウンロードしてみてください。
次回はフォルダーの作成
いかがでしょうか。ExcelAPIは他にも色々な機能があります。色々試してみるとおもしろいですね。次回はフォルダーの作成です。1,2個のフォルダー作成なら大した手間ではないのですが、名前を指定したフォルダーを大量に作成する作業は手間です。自動化できるとありがたいですね。楽しみに!!