はじめに～なぜ今、Excel VBA＋Webサービスなのか？

本連載は、表計算ソフトウェアであるMicrosoft Excel（以降、Excel）と、そのプログラミング言語であるExcel VBAを用いて、各種のWebサービスを活用しようというものです。Webサービスとは、インターネット上で利用可能なようにさまさまなベンダーから提供されている便利なAPI（Application Program Interface）のことです。Webサービスというと、Webアプリケーションから本格的なプログラミング言語、たとえばPHPやJavaScriptなどから呼び出して活用するというイメージが強いのですが、実はExcel VBAからでも利用可能です。

それはもちろん、Excel VBAもWebサービスを呼び出し可能な本格的なプログラミング言語であるからなのですが（VBAはVisual Basic for Applicationsの略）、重要なのはそれがExcel上で動作し、Excelのワークシートなどを自由自在に操れるということです。つまり、Webサービスから取得した情報などをExcel VBAを使って加工し、ワークシート上に展開してグラフ化したりと、さまざまに活用することができるのです。

Excelと言えば、ビジネスパースンにとってもっともなじみ深いアプリケーションということもありますから、もしExcelからWebサービスを活用できれば、Excelの使いこなしの幅がグッと拡がるのではないでしょうか？また、昨今ではプログラミングスキルの習得がプログラマーの領域を超えて求められているということもありますから、Webサービスの利用を通じてプログラミングのスキルを習得できれば、誰にとっても大きなメリットとなるでしょう。

本連載を通じて、Webサービスの便利な活用方法を発見し、さらにExcel VBAでのプログラミングによって読者のスキル向上に貢献できれば幸いです。

連載第1回目の目的

この連載第1回目では、極めてシンプルな例を用いて、ExcelにおけるXMLデータの取り込みと、そのテーブルへの展開、そしてそれをExcel VBAで自動取得して加工するという一連の手順を学びます。Webサービスでは、取得できるデータをXML形式で提供しているものもありますので、似たようなケースでは同様の手順でのデータ取得などに展開できるでしょう。

題材としては、RSS（Rich Site Summary）を用います。RSSは、ニュースサイトなどで最新の記事一覧などを提供するための仕組みで、その名称からも窺われるようにRDF（Resource Description Framework）という形式のXMLデータで提供されます。RSSは厳密にはWebサービスではありませんが、外部の変化するデータを取り込んで活用できるという点では狭義のWebサービスと言えます。RSSの提供元としては、本連載の運営母体であるマイナビのニュースサイト、マイナビニュースを利用します（図1）。

  • 図1 完成サンプル（mynavi_news_pc.xlsm）

完成サンプルのソースコード
https://github.com/wateryinhare62/mynavi_excelvba_webservice
［NOTE］RSSのバージョン
RSSには2つのバージョンがあります。1.0と2.0です。1.0はテキスト情報のみの配信に用いられ、2.0はそれに加えてテキストの装飾やリンクの設置も可能になっています。マイナビニュースで使われているのは1.0です。よって、テキスト情報のみのシンプルなものになっています。なお、RSSはバージョンによって正式な名称が異なり、1.0ではRich Site Summaryとなっているため、本記事もそれに倣っています。
［NOTE］XML（eXtensible Markup Language）について
XMLとは、単純な文字列やデータに、階層や繰り返しなどの構造を示すマーク（タグ）を加えたマークアップ言語です。タグは独自に定義できますので、自由にデータ構造を表現することができます。この言語で書かれたXMLファイルは、表を少し複雑にしたようなもので、簡易なデータベースとしても利用することが可能です。WebサービスにおいてXMLは重要な位置を占めます。今回のRSSもXML形式で取り込みますので、ぜひ心に留めておきましょう。


なお、本連載では動作確認をWindows 10 Pro、Microsoft 365（Excel 16.0、VBA 7.1）で行っています。旧バージョンや単体のExcelで試す場合にはご注意下さい。

関心のあるジャンルのRSSのURLを取得する

最初に、マイナビニュースで関心のあるジャンルのRSSを取得するためのURLを取得します。マイナビニュースでは、全ニュース、ジャンルごと、細かなテーマの3段階でRSSが提供されています。当然、全ニュースでは情報量が膨大になりますし、テーマごとでは逆に少なかったりします。ここでは中間をとって、ジャンルごとのRSSを取得する方針で進めることにします。 マイナビニュースのトップページ（https://news.mynavi.jp/）にアクセスし、ページ下方にある「サイトインフォメーション」から「RSS」を見つけてクリックします（図2）。

  • 図2：マイナビニュース

「マイナビニュース RSSについて」のページに移りますので（図3）、「はじめに」「利用規約」を一読したあと、「RSS一覧」からRSSを取得したい関心のあるジャンルを探します。ここでは、「パソコン」を選んでクリックしました（図4）。

  • 図3：マイナビニュース RSSについて

▼

  • 図4：RSS一覧

クリックすると、たとえばGoogle ChromeですとRSSの内容がそのまま表示されます（図5）。この内容自体は今回は不要で、必要なのはURLの方です。このURL欄の内容をコピーして、あとから利用できるようにメモ帳などのテキストエディタに控えておいて下さい。この場合は、「https://news.mynavi.jp/rss/digital/pc」となります。

  • 図5 「パソコン」のRSS

データをワークシートに取り込む

RSSのURLを取得したら、ExcelのワークシートにRSSのデータを取り込み、テーブルなどとして配置してみましょう。XMLデータをそのまま見たのでは何のことかよくわからないのが（図5参照）、ワークシートに取り込むことでグッと見やすくなり、データの意味を理解しやすくなるでしょう。

RSSのXMLデータを読み込む

これはExcelでの作業になります。Excelを起動し、白紙のワークシートを作成したら、リボンから［開発］タブをクリックし、［XML］グループの［ソース］ボタンをクリックすると、ウインドウ右側に「XMLソース」ウインドウが表示されます（図6）。

［NOTE］［開発］タブが表示されていない場合
リボンに［開発］タブが表示されていない場合は、以下のようにして表示させて下さい。

・リボンのどこかで右クリックする
・表示されるメニューから［リボンのユーザー設定］をクリックする
・「Excelのオプション」ダイアログボックスが表示されるので、右側の［リボンのユーザー設定］欄が「メイン タブ」となっているのを確認し、下の一覧から「開発」にチェックを入れる

・［OK］ボタンをクリックする

  • 図6：［XMLソース］ウィンドウ

「XMLソース」ウインドウの右下にある［XMLの対応付け］ボタンをクリックすると、「XMLの対応付け」ダイアログボックスが表示されます（図7）。これの［追加］ボタンをクリックすると、「XMLソースの選択」ダイアログボックスがさらに開きますので、［ファイル名］欄に先ほど控えたRSSのURLを貼り付けて［開く］ボタンをクリックします（図8）。

  • 図7：［XMLの対応付け］ダイアログボックス

▼

  • 図8：［XMLソースの選択］ダイアログボックス

「指定したXMLソースはスキーマを参照していません。XMLソースデータに基づいてスキーマを作成します。」という警告が出る場合がありますが、問題ないのでそのまま［OK］ボタンをクリックして下さい。なお、次回以降にこの警告を抑制したい場合には、［今後、このメッセージを表示しない］にチェックを入れてから［OK］ボタンをクリックします（図9）。

  • 図9：「指定したXMLソースはスキーマを参照していません。」という警告

「XMLの対応付け」ダイアログボックスの［このブック内のXMLの対応付け］欄に1行追加されます（図10）。これを確認して［OK］ボタンをクリックすると、「XMLソース」ウインドウに読み込んだデータ項目が階層的に表示されます。［ブック内のXMLの対応付け］欄が「RDF_対応付け」となっていることから、RDF（RSSのデータ形式）と認識されていることがわかります（図11）。

  • 図10：［このブック内のXMLの対応付け］欄に1行追加された

▼

  • 図11：「XMLソース」ウインドウに読み込んだデータ項目が階層的に表示された

これで、RSSのXMLデータがひとまず読み込まれました。

RSSのXMLデータの構造

次に、読み込んだXMLデータをワークシートに取り込んでみますが、その前にRSSのXMLデータの形式の理解を少し掘り下げます。図11の、階層的に表示されたデータ項目が、XMLデータの構造です。マイナビニュースのRSSは、以下のような構造になっています。

rdf:RDF … ルート   (1)
        channel … サイトの概要  (2)
            rdf:about … サイトのURI   (3)
            title … サイトのタイトル
            link … サイトのリンク
            description … サイトの要約
            dc:language … 言語
            dc:rights … 著作権
            dc:date … 取得日時
            dc:publisher … 発行者
            dc:creator … 制作者
            dc:subject … 主題
            items … 目次    (4)
                rdf:Seq
                    rdf:li … 記事のリンク
                    ………
            atom:link   (5)
            ………
        item … 記事の概要    (6)
            rdf:about … 記事のURI  (7)
            title … 記事のタイトル
            link … 記事のリンク
            description … 記事の要約
            dc:date … 記事の更新日時

マイナビニュースのRSSのデータ形式

図11では、それぞれの項目の先頭に「ns1:」などと付いていますが、ここでは無視して構いません。このうちトップレベルの「rdf:RDF」(1)は、XMLデータのルート要素で、RDFであることがわかるように命名されています。第2レベルの「channel」(2)と「item」(6)は重要で、このうち「channel」はサイトの概要、「item」は個々の記事の概要を保持しています。
「channel」には、サイトのタイトルや要約、作成者、取得日などの情報(3)が入っています。頻繁に更新されるものではないですが、基本情報として取り込んでおきましょう。「item」には、個々の記事のタイトルや要約、更新日、リンクなどの情報(7)が入っています。
なお、「channel」の下位には「items」(4)がありますが、さらにその下位の「rdf:Seq」は目次に相当し、その下の「rdf:li」の繰り返しが個々の記事へのリンクになっています。リンクだけ必要な場合にはここだけ参照すればいいのですが、実際には記事の概要も欲しいため、ここは使用せずに「item」の方を使用します。
(5)は無視して構いません。
「channel」と違い、「item」の方は記事の数だけ繰り返されます。こちらも、あまねく取り込むことにします。

「channel」を取り込む

まず、概要である「channel」を取り込みましょう。「channel」には繰り返しはありませんので、シンプルに見た目が表になるように取り込むことにします。B列は表の見出しとして、C列にデータを配置します。横方向に配置するといたずらに表が大きくなりますので、縦方向に配置することにします。
「XMLソース」ウインドウに表示されたデータ項目のうち、「ns3:channel」の下にある「ns1:about」から「ns4:subject」までの10個の項目を、1個ずつ順番にワークシートのC1セル～C10セルにドラッグ・アンド・ドロップします。すると、C1セルからC10セルに青色の枠が付くようになります。このように、XMLデータの各項目は1個ずつワークシート上に配置することができます（図12）。

  • 図12：「channel」配下をドラッグ・アンド・ドロップ

ここでC1セル上で右クリックし、表示されるメニューから［XML］―［XMLデータの更新］をクリックします。これにより、ワークシートにXMLデータの内容が反映されます（図13）。「XML インポート エラー」ダイアログボックスが表示されることもありますが、多くはXML上のデータをテキスト（文字列）として取り込んだという内容です。とりあえずは無視して構いません（図14）。

  • 図13：「channel」データの更新

  • 図14：「XML インポート エラー」ダイアログボックス

見た目を整えるために、「language」（言語）を「ja」から「日本語」にして、「date」（取得日時）を標準フォーマットからなじみ深いYYYY/MM/DD HH:MM:DDに変更しておきましょう（取得日時だけは、ユーザー定義の日付フォーマットになっています）。
「date」を変更すると右寄せになってしまうので、C1～C10セルは左寄せにしておきます。また、見出しをB列に入れておきましょう（図15）。

  • 図15：「language」と「date」を修正し見出しを設定

「item」を取り込む

次に「item」を取り込みましょう。「item」には繰り返しがありますので、Excelのテーブルとして取り込むことにします。
「XMLソース」ウインドウに表示されたデータ項目を下の方にスクロールし、「RDF」―「item」配下にある「ns3:item」をワークシートのA12セル（「channel」の表の1行空けて下」）にドラッグ・アンド・ドロップします。すると、「テーブル1」という名前のテーブルが作成され、リボンは［デザイン］タブに切り替わって、上に［テーブルツール］と表示されています。このように、項目ごとではなくまとめて配置することもできます。なお、この作業を何度も行うと、テーブルの名称が「テーブル2」…というように変化していきますので注意して下さい（図16）。

  • 図16：「channel」配下をドラッグ・アンド・ドロップ

［テーブルデザイン］タブの「外部のテーブルデータ」グループにある［更新］ボタンをクリックすると、データが「テーブル1」に読み込まれます（図17）。「channel」同様に「XML インポート エラー」ダイアログボックスが表示されることもありますが、とりあえずは無視して構いません（図18）。

  • 図17：「item」データの更新

  • 図18：「XML インポート エラー」ダイアログボックス

テーブルの場合には、見出しにXMLデータの要素名がひとまず入っていますので、これを適当に書き換えると良いでしょう（図19）。また、「channel」と同様に「date」を整形して左寄せにしたいところですが、数が多いので手作業では大変です。このへんがVBAの出番でしょう。さらに、「link」と被っていると思われる「about」を隠れて見えないようにして（「channel」をB列に配置したのはそのためです）、マウスクリックで記事が見れるように「title」にハイパーリンクを設定しましょう。これもVBAで行っていきます。

  • 図19：見出しを設定

これで、XMLデータをワークシートに取り込むことができました。ブックを保存しておきましょう。このとき、「マクロあり」のブック（*.xlsm）として保存する必要があります。VBAのコードを含むブックは、マクロありでなければならないからです。

Excel VBAで自動化する

初回はこれで良いのですが、毎日これを行うとなると面倒です。そこで、Excel VBAを使って自動化しましょう。すでに構造は取り込まれているので、あとは個々のデータを更新するだけのプログラムとなります。ニュースのチェックは毎日1回行うものとして、ブックを開いたときに自動的に更新するとします。

ブックを開いたときに自動実行するコードを書く

ブックを閉じてしまっていたら、まず開いて下さい。[Alt]＋[F11]キーでVBエディタを開き、左ペインで［ThisWorkbook］をダブルクリックし、開くコードウィンドウの上部で、［Workbook］、［Open］を選択します。「Private Sub Workbook_Open()」は開いたときに自動実行されるコードになりますので、この中にプログラムを記述していきます（図20）。

  • 図20：ブックを開いたときに自動実行するVBA

データの更新

図20のコードを順番に見ていきます。(1)で、ワークシートの書き換えに伴うちらつきを抑制するために、画面更新を一時的に止めます。(2)はデータの更新を行うもので、これだけですべてのデータが自動的に更新されます。

Private Sub Workbook_Open()
    ' 画面の更新を止める
    Application.ScreenUpdating = False  '(1)

    ' データの更新
    ActiveWorkbook.RefreshAll           '(2)

概要（channel）を整える

概要（channel）を整える部分を見ていきます。主に、手動で修正していた部分を自動化します。(3)では、「language」に相当するセルの値が「ja」ならば、「日本語」に変更しています。とりあえず他言語は考慮していませんが、RSSが返す内容に応じて書き換えたり追加すると良いでしょう。(4)では、「date」に相当するセルについて、文字「T」を空白に、ハイフン「-」をスラッシュ「/」に置き換え、さらに「+09:00」は余計なので削除、ということを行っています。これで目指す書式になります。

    ' 概要に関する処理
    ' 「ja」を「日本語」に置き換える
    If Cells(5, 3) = "ja" Then          '(3)
        Cells(5, 3) = "日本語"
    End If

    ' 日付を見やすくする
    With Cells(7, 3)                    '(4)
        .Replace What:="T", Replacement:=" "
        .Replace What:="-", Replacement:="/"
       .Replace What:="+09:00", Replacement:=""
    End With

記事一覧（item）を整える

記事一覧（item）を整える部分です。(5)では、情報としては余剰の「about」に相当するA列を隠しています。(6)で日付を整えていますが、方法は概要におけるものと範囲指定を行う・左寄せにする以外は同じです。ひととおり終了したら、セル幅を自動調整しています。(7)では「title」に「link」の内容をハイパーリンクとして設定しています。ハイパーリンクを設定し終わったら、「link」の列は不要になりますので「about」同様に不可視とします。これで、記事一覧はシンプルに3列で構成されるようになります。最後に、(8)で選択を解除していますが、ExcelにはVBAを含めて選択を解除するという機能はないので、A1セルを単独で選択することで代替しています。

    ' 記事に関する処理
    ' 「about」欄を隠す
    Columns(1).Hidden = True            '(5)

    ' 日付を整える
    Range("テーブル1").Select           '(6)
    firstRow = Selection.Cells(1, 1).Row
    For i = 1 To Selection.Rows.Count
        With Cells(firstRow + i - 1, 2)
            .Replace What:="T", Replacement:=" "
            .Replace What:="-", Replacement:="/"
            .Replace What:="+09:00", Replacement:=""
            .HorizontalAlignment = xlLeft
        End With
    Next
    Columns(2).AutoFit

    ' ハイパーリンクを設定する
    Range("テーブル1").Select          '(7)
    firstRow = Selection.Cells(1, 1).Row
    For i = 1 To Selection.Rows.Count
        ActiveSheet.Hyperlinks.Add Anchor:=Cells(firstRow + i - 1, 3), Address:=Cells(firstRow + i - 1, 5)
    Next
    Columns(5).Hidden = True

    '選択の解除
    Range("A1").Select                  '(8)
End Sub

以上で、マイナビニュースのRSSを自動的に読み込んで成形するExcelブックの作成が完了しました。

まとめ

今回は、マイナビニュースのRSSを取り込んでテーブル等を作成、それをVBAで自動的に更新するコードを書くことでExcelとVBAを使ってWebサービスを活用する基本的な事例を紹介しました。以降、XMLデータを扱うようなケースがあれば、応用できるのではないかと思います。次回は、より実用的なWebサービスとして、運賃の自動計算の事例を取り上げたいと思います。

WINGSプロジェクト 山内直著/山田祥寛監修
＜WINGSプロジェクトについて＞テクニカル執筆プロジェクト(代表山田祥寛)。海外記事の翻訳から、主にWeb開発分野の書籍・雑誌/Web記事の執筆、講演等を幅広く手がける。一緒に執筆をできる有志を募集中