SharePoint内のExcelに書かれた情報をVBA(マクロ)で取りに行く
VBAを使ってSharePoint内のExcelから情報を取得する
PowerPlatform関連のアプリを使って業務環境を構築する際「旧環境からの移行」という課題があります。
例えば以下のようなケースです。今回は部分的にクラウド環境に移行するという事例を用意してみました。
- Bというアプリから取り出した情報(csv)を加工し社内の共有サーバー内のExcelに展開
- 共有サーバー内のExcelは情報検索やレポート作成のマスターデータとして使われている
- マスターデータへのアクセス、情報取得は各PC毎に用意された.xlsm内のExcelVBAを使う
Aさんはここから環境に変化を加えることを考えているようです。
既存の環境の中から共有サーバーをSharePointに置き換えたいです。
代わってVBAによるデータへのアクセスはこのままにしたいですね。
さらに詳細を確認してみると画像のように環境の移行を考えているとの事です。
マスターデータの置き場所を変えるという事ですよね。
SharePoint×ExcelVBAで作業環境を構築することは可能です。
SharePoint(クラウド環境)にデータを置くことはできると思います。
しかし今までの様にデータにアクセスすることはできるのでしょうか?
ポイントはVBAで用意したコード内のパスを書き換えるところですね。
ExcelVBAを使えばSharePoint内の.xlsxファイルにアクセスできますよ。
今回は既存の環境からできるだけ変更点を少なくした状態でクラウド環境への移行を目指します。
各種用語の説明をした後に冒頭の事例を使って作業内容を説明していきます。
関連記事
SharePointとは何かを説明してからチームサイトについて説明しています。
SharePointの環境を図解とURLで理解することができます。
PowerPlatformとは何かについて説明しています。
関連書籍
SharePointについて網羅的に書かれている書籍です。非常に分かりやすく書かれています。
電子書籍はこちらです。私はこちらの書籍を電子書籍で購入しました。
SharePointとは
SharePointについてはこちらの記事で詳しく紹介しています。
チームサイト
チームサイトについても上のリンク記事で詳しく説明しています。
端的に申し上げますとチームサイトは「Microsoft Teamsで作ったチームに連動したサイト」です。
一定のグループメンバーで情報を共有する為にポータルサイトのようなを環境を構築することができます。
ドキュメント
チームサイトの中に用意されたデータファイルを格納することができる場所です。
書籍によっては「ドキュメントライブラリ」と書かれているものもあります。
こちらの記事では画像を使ってSharePointの環境を説明しています。ドキュメントについても説明しています。
SharePointは環境を理解するのが大変です。
用意された画像を見ることで環境を理解することができました。
ExcelVBAとは
ExcelVBAのVBAはVisual Basic for Applications の略です。
簡単に説明すると「Excelに備え付けられたプログラミング言語」です。
Visual Basic for Applications(ビジュアルベーシック・フォー・アプリケーションズ、VBA)は、主にマイクロソフト製のMicrosoft Officeシリーズに搭載されているプログラミング言語である。
Wikipedia:Visual Basic for Applications から抜粋
できること(基本編)
細かい作業をまとめて一連のプログラムにすることで特定の作業を自動化させることができます。
たとえばExcel内で以下のような作業をイメージしてみてください。
- セルA1に「おはようございます」と記入する
- セルA1の文字列(おはようございます)をカットする
- セルA1からカットした文字列(おはようございます)をセルB1に貼り付ける
3つの作業を手作業で行うことを考えると何度か作業を重ねることになりますよね。
しかしVBAでプログラミングを行えば3つの作業を高速かつ1クリックに集約することができます。
このような個々の作業の集合体に対してVBAを使って用意するコード群を「マクロ」と呼びます。
文章としては「ExcelVBAを用いてマクロを構築しました」というような使い方が一般的です。
マクロを自力で構築することができれば反復作業は一瞬で終わります。
ExcelVBAを勉強すると定時で帰ることができるようになりますよ。
電子書籍はこちらです。
できること(応用編)
基本的にはExcelの画面上部に配置されたアイコンの作業を自動化できますが他にも色々なことができます。
- OSに対する操作:フォルダ内の任意のデータや情報にアクセスする
- Webスクレイピング:Webサイトの情報を取得する
- 他のアプリケーションとの連携:SharePointをはじめとしたMicrosoft関連のアプリケーションとの接続
Webスクレイピングを使えばクラウドシステムへのデータ入力を自動化・・・なんていう事も可能になります。
慣れてくると「一日の仕事を全てマクロで自動化・・・」なんていう事も夢ではありません。
その他:資格試験も用意されています
資格試験なども用意されており数値で自身のスキルを証明することも可能です。
参考:ExcelVBAを無料で勉強することができます
私ExcelVBAにつてもブログを展開しています。興味がある方はこちらのサイトもご覧ください。
初心者向けの記事から上級者向けの記事まで幅広く情報を用意しています。
ExcelVBAについて事例をもとにコードを紹介しているサイトです。
事例
冒頭と同じ事例を使って作業内容を説明します。再度画像を用意します。
オレンジ色の背景の移行前(旧環境)から水色の背景の移行後(新環境)のフローを構築していきます。
まずはやることを整理します。
- グループメンバーがアクセスする事ができるSharePointのチームサイトを構築する
- アプリBから出力したcsvデータを展開する為のExcel(.xlsx)をSharePointのドキュメント内に配置
- グループメンバーが持っているExcelVBAで書かれた.xlsmファイルのコードを書き換える
これだけです。今回はコーディングが伴いますができるだけ少ない手数になる様に工夫してみます。
色々考えることがあり難しそうな印象を持っていました。
しかしやることを整理すると先が見えてできそうな気がしてきました。
システムを構築するときは頭で考えずやることを書き出しましょう。
仕事や課題が整理されて心理的にもスッキリすることが多いです。
作業1_SharePointのチームサイトを構築する
チームサイトの作り方はこちらです。
作業2_SharePointのドキュメントにデータを配置する
続いてSharePointのチームサイト内のドキュメントに.xlsxファイルを配置します。
作業3_ExcelVBAで書かれた.xlsmファイルのコードを書き換える
最後の作業です。グループメンバーが持っている.xlsmファイルのコードを書き換えます。
SharePointのチームサイト内のExcelにアクセスするためのコードはこちらです。
コードがたくさん出てきました・・・
内容は全て理解してほしいとことですが今回はポイントを絞ります。
まずは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 Filename:= 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」のフルパスで指定したワークブックを開いてください
- ワークブックに設定されたパスワードは******です
- 開く時は読み取り専用で開いてください
1番に書かれているのがパラメータ「FileName」です。
「変数pth」と「変数fle」は7、8行目で設定したフルパス(パスとファイル名)です。
その後2番で「Password」、3番で「ReadOnly」という様にパラメータを2つ指定しています。
- FileName ・・・ フルパス(パスとファイル名)
- Password ・・・ パスワード(Excelファイル側で任意に設定可能)
- ReadOnly ・・・ 読み取り専用で開くかどうか(Trueは読み取り専用で開く)
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にアクセスする事ができます。
SharePointに配置されたExcelにVBAでアクセスすることができました。
ExcelVBAはまだまだ第一線で活躍することができる言語です。
他の言語でも使える要素がたくさんあるので是非勉強してください。
まとめ
「旧環境からの移行」という軸の中からExcelVBAとの連携について記事を用意しました。
一気にクラウド環境に移行できればよいのですが色々な縛りがあって・・・という方が多いと聞いています。
そんなときは今回の事例のように「部分移行」のようなことも考えてみてください。