ガッツの日記 第７回 「エクセルとSharepointリスト」それぞれのいいとこどりをする方法

【今回の記事はデスクトップのエクセルの新たな活用方法の提案です】

ここ数年でSharePointリストなどのクラウドサービスを利用する人はかなり多くなってきています

クラウドはネットさえつながればどこでも使うことができ、特にSharepointリストは複数人でデータを共有して仕事するのに便利な機能が満載です

履歴の復元が行えたり、登録者や更新日時が確認できます

何より3000万行を蓄積できるのがいいです

エクセルはあくまで表計算ソフトであり、大量のデータを蓄積していくのには本来は向いてません

蓄積できる行数も1シート100万前後です

一方、使い慣れたデスクトップのエクセルをこれからも使用していきたいという方も多いと思います

特にVBAを使って業務を回してきたケースの場合は、そのままデスクトップのエクセルを継続的に使用せざる負えないです

ここからが本題です

実は、

Sharepointリストとデスクトップのエクセルを組み合わせて使用する方法があります

ADOという仕組みを使うと「Sharepointリストからエクセルへのデータ取得」「エクセルからSharepointリストへのデータ追加・更新」が自動で行えます

以前、私はこのADOを使用して「Accessとエクセル」を連携するシステムを開発したことがあります

この方法ならばAccessにデータを一元管理しながら、利用者各自のエクセルファイルにて作業を行うことができます

何が一番素晴らしいかというと、売上管理や商品管理などの各種エクセルにてマスタを共通利用できる点です

エクセルでデータ管理を行っていると、複数ファイルでのマスタの追加や更新などの処理が煩雑になりがちです

ADOを使用した仕組みの場合は、Accessにてマスタ管理を行えば、利用者の各種エクセルファイルにてマスタを自動でダウンロードして使いまわすことができます

今回の記事では、Accessの代わりにSharepointリストを使用するわけですが、クラウドで利用できる点なども含めてかなり効率的な仕組みができると考えています

VBAを使い込んでいた組織では、VBAを引き続き有効利用しながらクラウドを活用できるのも大きいです

以降の記事では、Sharepointリストの特徴なども交えながらデータ取得・追加・更新のサンプルコードを解説していきます

尚、今回の記事はVBA経験者を主な対象としていますのでその点は予めご了承ください

事前準備

ライブラリーの追加

ADOをVBAで使用する場合には、Visual Basicのツールタブ⇒参照設定からMicrosoft ActiveX Data Objectsライブラリを追加する必要があります

ここが抜けてしまうと、コードをいくら書いても動きませんので注意が必要です

サイトURL及びリストID

ADOを使用してSharepointリストをデータソースとして使用する場合は「サイトURL」と「リストID」を使用します

サイトURLについては分かりやすいと思います

上記画像の「sites」の後と「Lists」の前までがサイトURLです

リストIDは普段聞きなれないと思いますが、Sharepointリストの内部名のことです

今回の記事で使用するSharepointリストのリスト名は「Sales_Test」になりますが、別の名前/IDをADO内では使用します

リストIDの取得はこちらから行います

上の画像の「リストの設定」をクリックすると、次に開く画面でリストIDが取得できます

こちらの「%7B」と「%7D」の間がリストIDになります

共通構文

以下、サンプルコードを実際に解説していくわけですが、共通の部分があります

******

Dim objConnect As Object ‘ADODB.Connection
Dim recordSet As Object ‘ADODB.Recordset
Const Sharepointurl As String = ①”https://xxxx5.sharepoint.com/sites/TEST“
Const listid As String = ②”b24faf63-1f23-xxxx-858e-278ead83f878“

③Set objConnect = CreateObject(“ADODB.Connection”)
④objConnect.Open “Provider=Microsoft.ACE.OLEDB.16.0;WSS;IMEX=2;RetrieveIds=Yes;DATABASE=” & Sharepointurl & “;LIST=” & listid & “;”
⑤Set recordSet = CreateObject(“ADODB.Recordset”)

******

①②ではサイトURL、リストIDを設定します

③では、ADOとの接続を設定し、④で実際に①②のサイトURLとリストIDを通じて接続を行います

⑤では取得したレコード/行一式を格納する箱を設定します

Sharepointリストの列名を取得

本来であれば、早速データの取得のサンプルコードから紹介したいところなのですが、Sharepointリストの列名の取得から始めたいと思います

何故かと言うと、ここにSharepointリストの特徴の１つがあるからです

******

Sub getFields()
（共通）

With recordSet
①.Open “SELECT * FROM [Sales_TEST];”, objConnect
②.MoveFirst

Dim i As Long

③For i = 0 To recordSet.Fields.Count - 1

    ④Debug.Print recordSet.Fields(i).Name

⑤Next

   .Close

End With

objConnect.Close

End Sub

******

①にてレコードを該当Sharepointリストから取得し、②でレコードの最初に移動します

③～⑤でレコードの列名を繰り返し、イミディエイトウィンドウに表示します

取得した列名を見て頂くと、設定していない列が取得されます

ここがAccessと違うところです

一番上に出ているIDはレコードを追加すると「１，２，３・・・」と自動採番されます

このIDは今回もSharepointリストの更新で使用します

ちなみに、使用した接続やレコード一式は必ず閉じる/Closeするようにします

データの取得

Sharepointリストからデータを取得するには、２つの方法があり、列別に取得する方法とレコードセットを丸ごと取得する方法です。

列別

前述の列名で取得した「列名」をここで使用します

*太字の部分です

******

With recordSet
.Open “SELECT * FROM [Sales_TEST];”, objConnect
.MoveFirst

Dim i As Long

Do Until .EOF
  Debug.Print .Fields.Item("注文NO").Value
  .MoveNext
Loop

.Close

End With

objConnect.Close

******

一括

こちらは一括でSharepointリストからレコード一式をワークシートにコピーします

下のコードではSheet5のA2セルに一括でコピーしています

******

With recordSet
.Open “SELECT * FROM [Sales_TEST];”, objConnect

Worksheets("Sheet5").Range("A2").CopyFromRecordset recordSet

.Close

End With

objConnect.Close

******

もし、データが一行もない場合は「データが無かった旨」を表示できるようにすると便利です

.Open “SELECT * FROM [Sales_TEST];”, objConnect

If recordSet.BOF = True And recordSet.EOF = True Then
   recordSet.Close
   objConnect.Close
   MsgBox "該当するデータはありません。"
   Exit Sub
End If

データの追加

以下の①から⑧までを一式で使います

******

①recordSet.Open “[Sales_Test]”, objConnect, adOpenKeyset, adLockOptimistic

②objConnect.BeginTrans

③recordSet.AddNew
④recordSet.Fields("注文NO").Value = Worksheets("Sheet6").Cells(2, 1).Value
⑤recordSet.Fields("SalesAmount").Value = Worksheets("Sheet6").Cells(2, 2).Value

⑥recordSet.Update

⑦objConnect.CommitTrans

⑧recordSet.Close

******

①のadOpenKeysetやadoLockOptimisticは他に指定する方法がありますが、こちらの方法であれば大概は問題ありません

④⑤で追加データをワークシートから読み込んでいます

追加データが複数行ある場合は「For ~ Next」内に③～⑥を入れてコードを書きます

そして最後に⑦を実行します

データの更新

前述のようにSharepointリストのIDを使用し、更新するレコードを指定するのがポイントです

ですので実際には、事前にレコードの取得をしておいてIDを特定できるようにする必要があります

******

objConnect.BeginTrans

①recordSet.Filter = "ID = " & Worksheets("Sheet7").Cells(2, 1).Value
recordSet.Fields("SalesAmount").Value = Worksheets("Sheet7").Cells(2, 2).Value
recordSet.Update

******

データの追加も同様ですが、接続が行えない場合は「ロールバック処理/もとに戻す」を行えるようようにしておくのがよいです

＜まとめ＞

今回はADOの仕組みにより、Sharepointリストからエクセルにデータ取得したり、エクセルからデータ追加・更新を行う方法を解説しました

今回紹介した内容は基本的な内容であり、実際には条件式を組み合わせるなど様々なパターンが考えられます

実はSharepointリストとADOにて接続する場合、1点厄介なことがあります

ADOで扱うSharepointリストに参照列が存在する場合です

上の画像の一番右、CustomerName列は参照列です

他のSharepointリストから参照しています

参照列をADOで取得すると、次の画像のように参照しているSharepointリストのIDに置き換わってしまいます

ですので、IDを変換する仕組みが必要になります

データ追加の時も、参照列がある場合には同様にIDを変換する仕組みが必要です

とにかくSharepointリストを扱う上では「ID」が非常に重要です

この点は強く意識しておきましょう！

にほんブログ村