SharePoint内のExcelに書かれた情報をVBA（マクロ）で取りに行く

• URLをコピーしました！

VBAを使ってSharePoint内のExcelから情報を取得する

PowerPlatform関連のアプリを使って業務環境を構築する際「旧環境からの移行」という課題があります。

例えば以下のようなケースです。今回は部分的にクラウド環境に移行するという事例を用意してみました。

• Bというアプリから取り出した情報（csv）を加工し社内の共有サーバー内のExcelに展開
• 共有サーバー内のExcelは情報検索やレポート作成のマスターデータとして使われている
• マスターデータへのアクセス、情報取得は各PC毎に用意された.xlsm内のExcelVBAを使う

Aさんはここから環境に変化を加えることを考えているようです。

既存の環境の中から共有サーバーをSharePointに置き換えたいです。ただしVBAによるデータへのアクセスはこのままにしたいな～。

さらに詳細を確認してみると画像のように環境の移行を考えているとの事です。

マスターデータの置き場所を変えるという事ですよね。SharePoint×ExcelVBAで作業環境を構築することは可能です。

SharePoint（クラウド環境）にデータを置くことはできると思います。しかし今までの様にデータにアクセスすることはできるのでしょうか？

ポイントはVBAで用意したコード内のパスを書き換えるところですね。ExcelVBAを使えばSharePoint内の.xlsxファイルにアクセスできますよ。

今回は既存の環境からできるだけ変更点を少なくした状態でクラウド環境への移行を目指します。

各種用語の説明をした後に冒頭の事例を使って作業内容を説明していきます。

Excelのワークブック自体を取得することもできます。こちらの記事をご覧ください。

SharePointとは何かを説明してからチームサイトについて説明しています。

SharePointの環境を図解とURLで理解することができます。

PowerPlatformとは何かについて説明しています。

SharePointについて網羅的に書かれている書籍です。非常に分かりやすく書かれています。

電子書籍はこちらです。私はこちらの書籍を電子書籍で購入しました。

SharePointとは

SharePointについてはこちらの記事で詳しく紹介しています。

 SharePointとTeamsを連携させる方法｜チームサイトの作り方

チームサイトについても上のリンク記事で詳しく説明しています。

端的に申し上げますとチームサイトは「Microsoft Teamsで作ったチームに連動したサイト」です。

一定のグループメンバーで情報を共有する為にポータルサイトのようなを環境を構築することができます。

チームサイトの中に用意されたデータファイルを格納することができる場所です。

書籍によっては「ドキュメントライブラリ」と書かれているものもあります。

こちらの記事では画像を使ってSharePointの環境を説明しています。ドキュメントについても説明しています。

 【図解とURLを使用】3つのポイントからSharePointの構成を理解する

SharePointは環境を理解するのが大変です。用意された画像を見ることで環境を理解することができました。

ExcelVBAとは

ExcelVBAのVBAはVisual Basic for Applications の略です。

簡単に説明すると「Excelに備え付けられたプログラミング言語」です。

Visual Basic for Applications（ビジュアルベーシック・フォー・アプリケーションズ、VBA）は、主にマイクロソフト製のMicrosoft Officeシリーズに搭載されているプログラミング言語である。

細かい作業をまとめて一連のプログラムにすることで特定の作業を自動化させることができます。

たとえばExcel内で以下のような作業をイメージしてみてください。

3つの作業を手作業で行うことを考えると何度か作業を重ねることになりますよね。

1. セルA１に「おはようございます」と記入する
2. セルA１の文字列「おはようございます」をカットする
3. セルA1からカットした文字列「おはようございます」をセルB1に貼り付ける

しかしVBAでプログラミングを行えば3つの作業を高速かつ1クリックに集約することができます。

このような個々の作業の集合体に対してVBAを使って用意するコード群を「マクロ」と呼びます。

文章としては「ExcelVBAを用いてマクロを構築しました」というような使い方が一般的です。

マクロを自力で構築することができれば反復作業は一瞬で終わります。ExcelVBAを勉強すると定時で帰ることができるようになりますよ。

電子書籍はこちらです。

基本的にはExcelの画面上部に配置されたアイコンの作業を自動化できますが他にも色々なことができます。

1. OSに対する操作：フォルダ内の任意のデータや情報にアクセスする
2. Webスクレイピング：Webサイトの情報を取得する
3. 他のアプリケーションとの連携：SharePointをはじめとしたMicrosoft関連のアプリケーションとの接続

Webスクレイピングを使えばクラウドシステムへのデータ入力を自動化・・・なんていう事も可能になります。

慣れてくると「一日の仕事を全てマクロで自動化・・・」というのも夢ではないです。

実際私は一日の仕事の多くをマクロ化しています

資格試験なども用意されており数値で自身のスキルを証明することも可能です。

参考：VBAエキスパート

私ExcelVBAについてもブログを展開しています。興味がある方はこちらのサイトもご覧ください。

初心者向けの記事から上級者向けの記事まで幅広く情報を用意しています。

ExcelVBAについて事例をもとにコードを紹介しているサイトです。

事例

冒頭と同じ事例を使って作業内容を説明します。再度画像を用意します。

オレンジ色の背景の移行前（旧環境）から水色の背景の移行後（新環境）のフローを構築していきます。

まずはやることを整理します。

1. グループメンバーがアクセスする事ができるSharePointのチームサイトを構築する
2. アプリBから出力したcsvデータを展開する為のExcel（.xlsx）をSharePointのドキュメント内に配置
3. グループメンバーが持っている.xlsmファイルのコードを書き換える

これだけです。今回はコーディングが伴いますができるだけ少ない手数になる様に工夫してみます。

色々考えることがあり難しそうな印象を持っていました。しかしやることを整理すると先が見えてできそうな気がしてきました。

システムを構築するときは頭で考えずやることを書き出しましょう。仕事や課題が整理されて心理的にもスッキリすることが多いです。

チームサイトの作り方はこちらです。

 SharePointとTeamsを連携させる方法｜チームサイトの作り方

続いてSharePointのチームサイト内のドキュメントに.xlsxファイルを配置します。

最後の作業です。グループメンバーが持っている.xlsmファイルのコードを書き換えます。

SharePointのチームサイト内のExcelにアクセスするためのコードはこちらです。

コードがたくさん出てきました・・・

全て理解してほしいのですが今回はポイントを絞りましょう。まずは3行だけ理解してください。

複数行のコードが用意されていますがPlatさんが言っていたように重要なのはハイライトされている3行です。

今回はこの3行に絞って解説します。（残りのコードについての質問はお問い合わせフォームにてお願いします）

まずは13行目です。ここに書かれているコードでワークブックを開いています。

ExcelVBAのことが分かっている人にはとてもかんたんなコードですが理解できない方も居るかと思います。

よってOpenメソッドを使ったコードを日本語で書き直してみます。

1. 変数「pth」＆変数「fle」のフルパスで指定したワークブックを開いてください
2. ワークブックに設定されたパスワードは******です
3. 開く時は読み取り専用で開いてください

１番に書かれているのがパラメータ「FileName」です。

「変数pth」と「変数fle」は７、８行目で設定したフルパス（パスとファイル名）です。

その後２番で「Password」、３番で「ReadOnly」という様にパラメータを2つ指定しています。

• FileName　・・・　フルパス（パスとファイル名）
• Password　・・・　パスワード（Excelファイル側で任意に設定可能）
• ReadOnly　・・・　読み取り専用で開くかどうか（Trueは読み取り専用で開く）

Openメソッドは上記3つのほかにもパラメータを設定することができます（以下リンク参照）

 Workbooks.Open メソッド (Excel)

Openメソッドは型が決まっているので必要な情報を与えてあげれば動いてくれます。

色々気にせず型通りに情報を並べてあげることに注力しましょう。

ここで最も重要になるのはフルパス（一般的にはパスとファイル名）です。

フルパスは「パス」と「ファイル名」に分ける事ができます。詳細はこちらをご覧ください。

 【VBA】絶対パス相対パスの書き方｜理解はカレントディレクトリがポイント

その中で今回はパスに焦点をあててみます。（フルパスからファイル名を除いたものです）

“https://******.sharepoint.com/sites/******/Shared%20Documents/General/”

*（アスタリスク）の部分を日本語にしてみます。

“https://各組織の環境.sharepoint.com/sites/チームサイト名/Shared%20Documents/General/”

「Shared%20Documents」以降はそれぞれデータを用意される方の環境によってパスが変わります。

私は「ドキュメント」の直下に「General」というフォルダを用意しその中に対象のExcelを格納しています。

よって上記のようなパスになります。

結論としては「メンテナンス性」を向上させる為です。あえて変数を使っています。

では今回用意したVBAのコードを使ってメンテナンス性が上がるのか具体的に考えてみましょう。

• もとは共有サーバーのパスとファイル名が用意されていた
• 今回はSharePointのドキュメントへのパスだけを用意し書き換えた（ファイル名は変えてない）

今回やったことはこれだけです。要するにフルパスの中からパスを書き換えるだけなんですよ。

フルパスがコード内でどこに書かれているのかを分かりやすくしておけばメンテナンス性が上がりますよね。

この環境を用意する為にパスを変数にしてOpenメソッドと別にコードを用意しています。

参考：ファイル名も変数にしています。理由はパスと同じです。（メンテナンス性向上の為）

エラーがなければ個人PCからExcelVBAを使ってSharePointに用意されたExcelにアクセスする事ができます。

SharePointに配置されたExcelにVBAでアクセスすることができました。

ExcelVBAはまだまだ第一線で活躍することができる言語です。他の言語でも使える要素がたくさんあるので是非勉強してください。

ExcelVBAを使えばSharePointサイトの中にあるExcel本体を社内環境に展開することができます。

まとめ

「旧環境からの移行」という軸の中からExcelVBAとの連携について記事を用意しました。

一気にクラウド環境に移行できればよいのですが色々な縛りがあって・・・という方が多いと聞いています。

そんなときは今回の事例のように「部分移行」のようなことも考えてみてください。

参考：色々やりたいことはあるけどコードが思いつかない方はこちらのブログをご覧ください

• URLをコピーしました！