Microsoft365の機能の一つであるFormsのアンケートフォームを使って情報を収集し、その結果をデスクトップアプリのExcelへ読み込むことで、データをVBA等で使いやすい状態にする方法を、先日Zoomで説明させて頂きました。
資料を準備せずに開催したので、本日より手順を文章化していきます。
Formsを使う意味
クラウドサービスが無い頃は、ExcelやWordでアンケートフォームを作成し、メールで社員に拡散してメールで受け取ったファイルをVBAで集計するという作業が数多く見られました。（VBAで集計しているだけマシかもしれませんが）
昨今はクラウドサービス上でアンケートフォームを作成して自動的に集計するところまで簡単に出来るようになりました。
Microsoft365を契約している企業であれば、新たに契約しなくてもFormsという機能を使ってアンケートフォームの作成と集計が可能です。
従来のExcel VBAに頼った方法では、アンケートの項目やレイアウトに応じてVBAの修正が必要でしたが、Formsを活用すればそのような手間がかかりません。
アンケートの作成も、ポチポチとクリックするだけで簡単にできるようになっています。
アンケート収集をFormsへ置き換えるために必要なこと
Formsを使って回答を収集しデータベース形式のExcelに集めるのは簡単にできます。
しかしながら、回答結果を従来の帳票に変換したり、社内のファイルサーバーに何かしたい場合は、データをVBAで扱える状態にしなければなりません。
VBAだけでMicrosoft365上のデータを安定して読み込むのは非常に難しく、手動コピペに頼るかMicrosoft365の他の機能やPowerQueryを組み合わせる必要があります。
私が知っている方法を簡単な順に説明します。
Formsの回答をデスクトップアプリ版Excelへ取り込む方法
1. Formsを作成 → Excelで保存 → 手動でコピペ
まずは何もしない方法です。
この方法は、Formsの基本的な操作とExcelの手動操作で非常に簡単に運用できる方法です。多く手作業が含まれますが、集計を数回しか行わない用途ではこれで十分です。
また、個人の（無償版の）Microsoftアカウントでも、使うことができます。
アンケート結果が一覧形式で入力されているだけでも十分な効率化が見込めますが、結果を集計する度にフォームの回答ページを開いて新しいエクセルブックがダウンロードする必要がある上に、ダウンロードフォルダにファイルが増殖していくため、手動でファイルを置き換えたり内容を集計ブックにコピペしたりと煩雑になります。
- Microsoft365 Formsでアンケートフォームを作成する方法
2. Excelを作成 → Formsを作成 → クラウド上のExcelに自動で書き込まれる → デスクトップExcelで取り込む
この方法は、企業版のMicrosoft365（Basic以上の全てのプラン）で使用可能です。
個人の保存場所（OneDrive for Business）や、組織の保存場所（SharePoint Online）にxlsxファイルを保存することで、数回のクリックでアンケート結果が自動で追記されていくように設定できます。
- Microsoft365 Excelと同期するフォームを作成する方法
書き込まれたデータの取り込みは、複数の方法がありますがPowerQueryがおすすめです。
OneDriveでファイルを同期することで、数式やVBA等で読むことも不可能ではありませんがパソコンの環境設定に依存してしまいますし、ファイルの同期やパスの指定などハマりどころが多いため推奨しません。
→ デスクトップExcelのPowerQueryで取り込み
- OneDrive上のExcelファイルの内容をPowerQueryで取り込む方法（後日執筆）
この方法も非常に手軽で便利ですが、書き込み先のデータがExcelブックである以上誤ってユーザーが壊しやすいというリスク等を孕んでおり、次項のSharePoint Onlineのリスト(Lists)より安定性に欠けることがあります。
3. Formsを作成 → PowerAutomateでSharePoint Online リスト(Lists)へ蓄積 → デスクトップExcelのPowerQueryで取り込み
この方法は、Microsoft365の自動化機能であるPowerAutomateを使用して簡単なプログラムを書き、回答データをSharePoint Onlineのリストに転機させます。
リストはデータベースのテーブルに近い性質をもっており、データの更新単位がレコード毎（回答1件毎）で、機械的に情報の追加・更新・削除を操作することができます。
列（フィールド）にはデータ型を指定でき、文字/数値情報だけではなく、プルダウン選択や、画像等のメタデータ、別リストのアイテムの参照、ユーザー等のオブジェクト情報も保存できます。（※フォームの回答はあまり型指定しないほうが良い場合もあります）
このような性質から、リストに溜め込んだデータは様々な用途で活用しやすいです。
リストは非常に強力ですが、PowerAutomate、SharePoint、LIstsと言ったMicrosoft365の幅広い知識が必要となるため、先の方法に比べるとかなりハードルは高めです。 （使い方を覚えてしまえば10分程度で組めるし、Excel管理より柔軟な要件に対応することができます。ただし、きちんとした仕組みにしようとすると開発工数が飛躍的に伸びる傾向があります。）
- SharePoint サイトにListsを作成する方法（後日執筆）
- Formsに投稿された内容をPowerAutomateでListsへ転記する方法（後日執筆）
- SharePoint Listsの内容をPowerQueryで取り込む方法（後日執筆）
もっと本格的なデータベースやDataverseに書き込むという方法もありますが、更にハードルが高くなるので割愛します。
補足記事
以下の内容についても記事を書くかも知れない
- VBAで安定して別ブックのPowerQueryの更新をする方法
- VBAでSharePoint Listsの内容を直接読み書きする方法(ADODBを使用）
- AccessでSharePoint Listsの内容を一括修正する方法
最後に
難易度は下に行くほど難しくなりますが、フォームの運用期間、集計頻度、展開規模が大きくなるほど後者を使うほうが有利になります。
また、FormsはEUCに近いレベルで運用することを想定されています。本当に大規模・重要な場面では、業務フロー全体を考慮して開発された専用システムへ任せることも忘れないでください。
ここで説明した内容以外にもフォームを読み込む方法があるかもしれません。 もし詳しい人がいたらTwitter等で教えて頂けると幸いです。
以上
それではまた明日♪ちゅんちゅん(・8・)