SharePoint内のExcelに書かれた情報をVBA（マクロ）で取りに行く
VBAを使ってSharePoint内のExcelから情報を取得する
PowerPlatform関連のアプリを使って業務環境を構築する際「旧環境からの移行」という課題があります。
例えば以下のようなケースです。今回は部分的にクラウド環境に移行するという事例を用意してみました。
- Bというアプリから取り出した情報（csv）を加工し社内の共有サーバー内のExcelに展開
- 共有サーバー内のExcelは情報検索やレポート作成のマスターデータとして使われている
- マスターデータへのアクセス、情報取得は各PC毎に用意された.xlsm内のExcelVBAを使う
Aさんはここから環境に変化を加えることを考えているようです。
Aさん既存の環境の中から共有サーバーをSharePointに置き換えたいです。
代わってVBAによるデータへのアクセスはこのままにしたいですね。
さらに詳細を確認してみると画像のように環境の移行を考えているとの事です。
Plat（プラット）マスターデータの置き場所を変えるという事ですよね。
SharePoint×ExcelVBAで作業環境を構築することは可能です。
AさんSharePoint（クラウド環境）にデータを置くことはできると思います。
しかし今までの様にデータにアクセスすることはできるのでしょうか？
Plat（プラット）ポイントはVBAのパスを書き換えるところですね。
ExcelVBAを使えばSharePoint内の.xlsxファイルにアクセスできますよ。
今回は既存の環境からできるだけ変更点を少なくした状態でクラウド環境への移行を目指します。
各種用語の説明をした後に冒頭の事例を使って作業内容を説明していきます。
関連記事
SharePointとは何かを説明してからチームサイトについて説明しています。
SharePointの環境を図解とURLで理解することができます。
PowerPlatformとは何かについて説明しています。
関連書籍
SharePointについて網羅的に書かれている書籍です。非常に分かりやすく書かれています。
ポチップ
電子書籍はこちらです。私はこちらの書籍を電子書籍で購入しました。
SharePointとは
SharePointについてはこちらの記事で詳しく紹介しています。
チームサイト
チームサイトについても上のリンク記事で詳しく説明しています。
端的に申し上げますとチームサイトは「Microsoft Teamsで作ったチームに連動したサイト」です。
一定のグループメンバーで情報を共有する為にポータルサイトのようなを環境を構築することができます。
ドキュメント
チームサイトの中に用意されたデータファイルを格納することができる場所です。
書籍によっては「ドキュメントライブラリ」と書かれているものもあります。
こちらの記事では画像を使ってSharePointの環境を説明しています。ドキュメントについても説明しています。
AさんSharePointは環境を理解するのが大変です。
用意された画像を見ることで環境を理解することができました。
ExcelVBAとは
ExcelVBAのVBAはVisual Basic for Applications の略です。
簡単に説明すると「Excelに備え付けられたプログラミング言語」です。
Visual Basic for Applications（ビジュアルベーシック・フォー・アプリケーションズ、VBA）は、主にマイクロソフト製のMicrosoft Officeシリーズに搭載されているプログラミング言語である。
Wikipedia：Visual Basic for Applications から抜粋
できること（基本編）
細かい作業をまとめて一連のプログラムにすることで特定の作業を自動化させることができます。
たとえばExcel内で以下のような作業をイメージしてみてください。
- セルA１に「おはようございます」と記入する
- セルA１の文字列（おはようございます）をカットする
- セルA1からカットした文字列（おはようございます）をセルB1に貼り付ける
3つの作業を手作業で行うことを考えると何度か作業を重ねることになりますよね。
しかしVBAでプログラミングを行えば3つの作業を高速かつ1クリックに集約することができます。
このような個々の作業の集合体に対してVBAを使って用意するコード群を「マクロ」と呼びます。
文章としては「ExcelVBAを用いてマクロを構築しました」というような使い方が一般的です。
Plat（プラット）マクロを自力で構築することができれば反復作業は一瞬で終わります。
ExcelVBAを勉強すると定時で帰ることができるようになりますよ。
ポチップ
電子書籍はこちらです。
できること（応用編）
基本的にはアイコンにある作業を全て自動化できますが他にも色々なことができます。
- フォルダ内の任意のデータや情報にアクセスする
- Webサイトの情報を取得する（Webスクレイピング）
- 他のアプリケーションとの連携（SharePointをはじめとしたMicrosoft関連のアプリケーションなど）
慣れてくると「一日の仕事を全てマクロで自動化・・・」なんていうことも可能です。
その他：資格試験も用意されています
資格試験なども用意されており数値で自身のスキルを証明することも可能です。
参考：ExcelVBAを無料で勉強することができます
私ExcelVBAにつてもブログを展開しています。興味がある方はこちらのサイトもご覧ください。
初心者向けの記事から上級者向けの記事まで幅広く情報を用意しています。
ExcelVBAについて事例をもとにコードを紹介しているサイトです。
事例
冒頭と同じ事例を使って作業内容を説明します。再度画像を用意します。
オレンジ色の背景の移行前（旧環境）から水色の背景の移行後（新環境）のフローを構築していきます。
まずはやることを整理します。
- グループメンバーがアクセスする事ができるSharePointのチームサイトを構築する
- アプリBから出力したcsvデータを展開する為のExcel（.xlsx）をSharePointのドキュメント内に配置
- グループメンバーが持っているExcelVBAで書かれた.xlsmファイルのコードを書き換える
これだけです。今回はコーディングが伴いますができるだけ少ない手数になる様に工夫してみます。
Aさん色々考えることがあり難しそうな印象を持っていました。
しかしやることを整理すると先が見えてできそうな気がしてきました。
Plat（プラット）システムを構築するときは頭で考えずやることを書き出しましょう。
仕事や課題が整理されて心理的にもスッキリすることが多いです。
作業1_SharePointのチームサイトを構築する
チームサイトの作り方はこちらです。
作業2_SharePointのドキュメントにデータを配置する
続いてSharePointのチームサイト内のドキュメントに.xlsxファイルを配置します。
作業3_ExcelVBAで書かれた.xlsmファイルのコードを書き換える
最後の作業です。グループメンバーが持っている.xlsmファイルのコードを書き換えます。
SharePointのチームサイト内のExcelにアクセスするためのコードはこちらです。
Aさんコードがたくさん出てきました・・・
Plat（プラット）内容は全て理解してほしいとことですが今回はポイントを絞ります。
まずは3行だけ理解してください。
Sub SharePointのドキュメント内のファイルへのアクセス()
Dim pth As String 'path ファイルパス
Dim fle As String 'file ファイル名
Dim wb As Workbook 'workbook ワークブック
pth = "https://******.sharepoint.com/sites/******/Shared%20Documents/General/"
fle = "******.xlsx"
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Workbooks.Open pth & fle, PASSWORD:="******", ReadOnly:=True
Set wb = ActiveWorkbook
'*************************************
'ここにシート内からデータを取得するなどの具体的な作業内容に対するコードを書く
'*************************************
wb.Close savechanges:=True
MsgBox "作業終了", vbInformation, "報告"
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub複数行のコードが用意されていますがPlatさんが言っていたように重要なのはハイライトされている3行です。
今回はこの3行に絞って解説します。（残りのコードについての質問はお問い合わせフォームにてお願いします）
WorkbooksオブジェクトのOpenメソッド
まずは13行目です。ここに書かれているコードでワークブックを開いています。
ExcelVBAのことが分かっている人にはとてもかんたんなコードですが理解できない方も居るかと思います。
よってOpenメソッドを使ったコードを日本語で書き直してみます。
- ファイルパス「変数pth」＆ファイル名「変数fle」のワークブックを開いてください
- ワークブックに設定されたパスワードは******です
- 開く時は読み取り専用で開いてください
１番に書かれている「変数pth」と「変数fle」は７、８行目で設定したファイルパスとファイル名です。
２番で「パスワード」、３番で「開き方」という様にパラメータを2つ追加で指定しています。
Openメソッドは型が決まっているので必要な情報を与えてあげれば動いてくれます。
色々気にせず型通りに情報を並べてあげることに注力しましょう。
キーポイント
ここで最も重要になるのはファイルパスです。
“https://******.sharepoint.com/sites/******/Shared%20Documents/General/”
*（アスタリスク）の部分を日本語にしてみます。
“https://各組織の環境.sharepoint.com/sites/チームサイト名/Shared%20Documents/General/”
「Shared%20Documents」以降はそれぞれデータを用意される方の環境によってパスが変わります。
私は「ドキュメント」の直下に「General」というフォルダを用意しその中に対象のExcelを格納しています。
よって上記のようなファイルパスになります。
なぜOpenメソッドにファイルパスとファイル名を直書きしないのか？
結論としては「メンテナンス性」を向上させる為に直書きをせずに変数を使っています。
では今回用意したVBAのコードを使ってメンテナンス性が上がるのか具体的に考えてみましょう。
- もとは共有サーバーのファイルパスが用意されていた
- 今回はSharePointのドキュメントへのファイルパスを用意し書き換えた
今回やったことはこれだけです。要するにファイルパスを書き換えるだけなんですよ。
ファイルパスがどこに書かれているのかを分かりやすくしておけばメンテナンス性が上がりますよね。
この環境を用意する為にファイルパスを変数にしてOpenメソッドと別にコードを用意しています。
結果
エラーがなければ個人PCからExcelVBAを使ってSharePointに用意されたExcelにアクセスする事ができます。
AさんSharePointに配置されたExcelにVBAでアクセスすることができました。
Plat（プラット）ExcelVBAはまだまだ第一線で活躍することができる言語です。
他の言語でも使える要素がたくさんあるので是非勉強してください。
まとめ
「旧環境からの移行」という軸の中からExcelVBAとの連携について記事を用意しました。
一気にクラウド環境に移行できればよいのですが色々な縛りがあって・・・という方が多いと聞いています。
そんなときは今回の事例のように「部分移行」のようなことも考えてみてください。