こんにちは～。「デザインもできるシステムエンジニア」のあつもり(@atumori17)です。今日も業務効率化のアイデアを発信していきますっ！
前回は「日付の曜日を判定してセルに入れる方法」を紹介しました。
今回はさらにもっと使いやすくするために、「エクセルVBAでWebAPIを使って祝日を判定する方法」を紹介していきます。祝日の判定はエクセルの関数などでできないので、WebAPIを使います。
スケジュール表を作るときなどに、祝日の判定までできるとさらに実用性が上がりますよね。
目次
祝日を判定するWebAPI
WebAPIは以下のサイトのものを使わせていただきました。
祝日判定web API
シンプルでとってもわかりやすい！こういう便利なものを作ってくれるとホント助かりますよね～。
実際にプログラムで使用するURLは
を使います。
このURLの後ろにパラメーター(日付のデータ)を付加してサーバーにリクエストを送ります。
http://s-proj.com/utils/checkHoliday.php?kind=h&date=20171128
こんな感じでサーバーにhttpリクエストを送ります。
そうするとWebAPIが「20171128」は祝休日なのかを判定してくれます。判定の結果は
- 平日ならelse
- 祝休日ならholiday
という値を返してくれます。
日付の曜日を判定してセルに入れるプログラムの作成
前回のプログラムを修正する
それでは実際にプログラムを作っていきます。
前回使用した「今月の日付と曜日を入力.xls」を開いて修正していきます。
UserForm1のSubプロシージャ「CommandButton1_Click」に次の段落で説明するコードを貼り付けます。
サンプルコードを貼り付ける
「CommandButto1_Click()」に貼り付けるコードです。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 |
Option Explicit '日付入力 Private Sub CommandButton1_Click() Dim tmp As String 'キャンセルが押された場合は処理を終了する On Error GoTo myError tmp = Application.InputBox("列を選択して下さい", "列の選択", Type:=8).Address On Error GoTo 0 Dim monthStart As Long '月初の日付 Dim monthEnd As Long '月末の日付 monthStart = 1 monthEnd = Day(DateSerial(Year(Date), Month(Date) + 1, 0)) Range(tmp).Select Dim y As Long Dim x As Long Dim col As Long '選択した列番号 Dim row As Long '選択した行番号 Dim strYear As String Dim strMonth As String Dim strDay As String Dim activeDate As String Dim activeDateFmat As String Dim apiUrL As String 'apiのURL apiUrL = "http://s-proj.com/utils/checkHoliday.php?kind=h&date=" '列が選択されていた場合 If Selection.Address = Selection.EntireColumn.Address Then col = Selection.Column For y = monthStart To monthEnd Cells(y, col).Value = y strYear = Year(Date) strMonth = Month(Date) strDay = y activeDate = strYear & "/" & strMonth & "/" & strDay '日付の区切りを取り除く activeDateFmat = Format(activeDate, "yyyymmdd") '日付から曜日の数値を取得 tmp = Weekday(activeDate) '曜日の数値から曜日を取得(Trueで曜日名を省略) tmp = WeekdayName(tmp, True) '日付の隣のセルに曜日を入れる Cells(y, col + 1).Value = tmp '休日か祝日の場合は日付を赤色にする If CheckHoliday(apiUrL & activeDateFmat) = "holiday" Then Cells(y, col).Font.ColorIndex = 3 End If Next Else '行が選択されていた場合 If Selection.Address = Selection.EntireRow.Address Then row = Selection.row For x = monthStart To monthEnd Cells(row, x).Value = x strYear = Year(Date) strMonth = Month(Date) strDay = x activeDate = strYear & "/" & strMonth & "/" & strDay '日付の区切りを取り除く activeDateFmat = Format(activeDate, "yyyymmdd") '日付から曜日の数値を取得 tmp = Weekday(activeDate) '曜日の数値から曜日を取得(Trueで曜日名を省略) tmp = WeekdayName(tmp, True) '日付の隣のセルに曜日を入れる Cells(row + 1, x).Value = tmp '休日か祝日の場合は日付を赤色にする If CheckHoliday(apiUrL & activeDateFmat) = "holiday" Then Cells(row, x).Font.ColorIndex = 3 End If Next End If End If myError: End Sub '祝休日の判定 Function CheckHoliday(ByVal apiUrLAddPra As String) 'XMLHttpRequestオブジェクトを作成 Dim HttpReq As Object Set HttpReq = CreateObject("MSXML2.XMLHTTP") HttpReq.Open "GET", apiUrLAddPra, False HttpReq.send (Null) '戻り値（祝休日の場合holiday、それ以外はelseを返す) CheckHoliday = HttpReq.responseText Set HttpReq = Nothing End Function |
プログラムコードの解説
それではプログラムコードの解説をしていきます。重要なポイントに絞って説明していきます。
プログラム全体の流れは以下のようになります。
- 今月の月末の日にちを取得する
- 処理対象の列番号または行番号を取得する
- 行か列どちらが選択されたかを調べる
- セルに日付を入力する
- 日付から曜日を取得して隣のセルに入れる
- WebAPIを使って祝日休日の判定を行い、日付の色を変える
1~5では前回の記事で紹介していますので、6のWebAPIを使うところからの説明をしていきます。
それでは早速WebAPIを使っていきます。
XMLHttpRequestオブジェクトを作成する
XMLHttpRequestオブジェクトはサーバーとクライアント間でデータ通信を行なうためのAPIです。
まずはオブジェクト変数をObject型で宣言します。
続いて先ほど宣言した変数にXMLHttpRequestオブジェクトをセットします。
全体のプログラムコードでは81~83行目になります。
|
1 2 3 |
'XMLHttpRequestオブジェクトを作成 Dim HttpReq As Object Set HttpReq = CreateObject("MSXML2.XMLHTTP") |
ここで注意点が1つあります。XMLHttpRequestオブジェクトをエクセルのVBAで使用するには参照設定を行う必要があるんです。設定しないとエラーが表示されますが、事前に設定しておいたほうが良いでしょう。
openメソッドでHTTPリクエストを作成する
WebAPIを使う準備ができましたので、今度はサーバーに対してHTTPリクエストを送ります。まずサーバーに送る前にリクエストを作成する必要があります。リクエストの作成はopenメソッドを使います。
第1引数にリクエストの種類を指定します。今回の場合、URLに日付のデータを付加して送るのでリクエストはGETを指定しています。
第2引数にはリクエストを送る先のURLを指定します。さいほど説明したようにURLに日付のデータを付加して指定します。
http://s-proj.com/utils/checkHoliday.php?kind=h&date=20171128
こんな感じになります。
第3引数は同期通信か非同期通信を指定します。Trueで非同期通信、Falseで同期通信になります。今回の場合、Falseで同期通信を指定しています。
つまりサーバーから応答があるまで次の処理は行わないようにしています。
sendメソッドでHTTPリクエストを送る
今度はopenメソッドで作成したHTTPリクストをsendメソッドを使ってサーバーに送ります。
引数にはサーバーへ送るデータを指定します。POSTの場合はここで指定しますが、GETの場合はopenメソッドの第2引数で既に日付を付加したURLを指定していますので、NULLを入れます。
responseTextプロパティで祝日判定結果を取得する
サーバーにリクエストを送ったので、その返答をもらわなければいけませんよね。そのためにはresponseTextプロパティでサーバーから返ってきたレスポンスデータを文字列として取得します。
このレスポンスデータを独自関数であるCheckHolidayの戻り値としています。
- 平日ならelse
- 祝休日ならholiday
が戻り値として渡されるわけですね。
openメソッドからresponsTextプロパティを使用するところまでの流れは以下になります。全体のプログラムコードでは106~110行目になります。
|
1 2 3 4 5 |
HttpReq.Open "GET", apiUrLAddPra, False HttpReq.send (Null) '戻り値（祝休日の場合holiday、それ以外はelseを返す) CheckHoliday = HttpReq.responseText |
ColorIndexプロパティで日付の色を変える
CheckHoliday関数の戻り値によって日付の色を変える処理をします。セル内にある文字の色を変えるには次のようにします。
今回の場合は祝休日の場合は赤文字にするようにします。全体のプログラムコードでは44~47行目、57～60行目になります。
|
1 2 3 4 |
'休日か祝日の場合は日付を赤色にする If CheckHoliday(apiUrL & activeDateFmat) = "holiday" Then Cells(y, col).Font.ColorIndex = 3 End If |
プログラムコードの解説は以上です。
祝休日を判定してセルに入れるプログラムの動作確認
それでは実際に出来上がったプログラムを動かしてみましょう。
ワークシートの起動ボタンを押します。
日付入力ボタンをクリックして、列を選択しOKボタンをクリックします。
今月の日付と曜日が入力されました。祝休日の日付はきちんと赤文字になっていますね。
行を選択した場合はこんな感じになります。
まとめ
いかがでしたか？今回の内容をまとめます。
- XMLHttpRequestのopenメソッドとsendメソッドを使いサーバーにhttpリクエストを送信する
- レスポンスデータをresponseTextプロパティを使って文字列で取得する
- ColorIndexプロパティで祝祭日に色をつける
以上「エクセルVBAでWebAPIを使って祝日を判定する方法」でした。おつかれさまでした～。
その使い方だと損してます！
会社員、個人事業主のかたへ。
無料で登録できるAmazonビジネスアカウントを知ってますか？
何と言っても便利なのが請求書払いによる後払いができるということ。
また、承認ルールを作成すれば、業務上必要な物だけに購入を制限することができます。
さらに多くの商品にビジネスアカウント価格が設定されており、個人アカウントより安く購入できます。
このように個人アカウントと比べてビジネスアカウントはお得な機能や特典がついています。
無料で登録できるので、会社員、個人事業主なら使ったほうが得なのは言うまでもありません。