Sharepoint ListはPowerAppsと連携したり、チーム内での情報をためたり、簡易データベースとして利用できたりとかなり便利です。ただExcelになれていると、関数で内容を編集や変換したりしたいときがあるのですが、用途が違いますのでSharepoint ListではさすがにExcelほどの自由度はありません。
社内でExcelを簡易データベースとして運用されていたものがあり、それをSharepoint Listに移行する際に、いままでの利便性などを残したいと思い、Excel VBAとADOで作成しました。
概要
環境は以下になります。
- Windows 11 pro
- Excel for Microsoft 365
- Sharepoint Online
- 参照設定
- Microsoft Active X Data Objects 6.1 Library
- その他
- 書き込むシートの名前を「MainSheet」に変更しています。
- コマンドボタンをシート上に追加し、名前を「UpdateButton」としています。
指定したSharepoint サイトのListに対して、MainSheetのID列をキーとしてSharepoint List内の情報を更新するコードです。入力値チェックは行っていませんので、実際に運用する際は更新するSharepoint List にあわせて処理を追加してください。
サンプルコード
Const ServerUrl As String = "https://hogehoge.sharepoint.com/sites/hogehogehoge"
Const ListName As String = "12345678-1234-1234-1234-123456789012"
' Parameters for using ADO with Late Binding
Const adOpenDynamic = 2
Const adOpenStatic = 3
Const adUseClient = 3
Const adUseNone = 1
Const adUseServer = 2
Const adLockPessimistic = 2
Const adLockOptimistic = 3
Const adLockBatchOptimistic = 4
' Field Names in SharePoint List
Const ID As String = "ID"
Const Title As String = "Title"
Const Name as string = "名前"
Const TitleRow As Long = 1
Private Sub UpdateButton_Click()
Dim con As Object
Dim rs As Object
Dim sql As String
Dim ws As Worksheet
Set ws = MainSheet
' Create the connection object with ADO
Set con = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
' Open the connection
With con
.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=0;RetrieveIds=Yes;" & _
"DATABASE=" & ServerUrl & ";" & _
"LIST=" & ListName & ";"
.Open
End With
With rs
' confirm that recordset is closed
If .State = 1 Then .Close
Dim i As Long
i = TitleRow + 1
' Recordset settings parameters
.ActiveConnection = con
.CursorType = adOpenDynamic
.CursorLocation = adUseClient
.LockType = adLockOptimistic
For i = TitleRow + 1 To 1048576
If Trim(ws.Range("A" & i).Value) = "" Then GoTo Continue
sql = "UPDATE [請求書メール配信可否] SET " _
& Title & "='" & ws.Range("B" & i).Value & "' " _
& "," & Name & "='" & ws.Range("C" & i).Value & "' " _
& " WHERE " & ID & "=" & ws.Range("A" & i).Value
' Recordset settings parameters
.Source = sql
.Open
Continue:
Next
End With
Set rs = Nothing
CleanExit:
If con.State = 1 Then
con.Close
Set con = Nothing
End If
MsgBox "Finished updating data for SharePoint list", vbOKOnly
ErrHand:
Debug.Print Err.Number, Err.Description
End Sub
詳細
接続するSharepointサイトのURLとリスト名を設定します。ServerUrlにはSharepointサイトのホームのURLを設定します。ListNameにはリストのListIDを設定します。
ListIDはリストの設定のURLに含まれるList=%7B12345678-1234-1234-1234-123456789012%7D部分の%7Bと%7Dに挟まれた部分です。
Const ServerUrl As String = "https://hogehoge.sharepoint.com/sites/hogehogehoge" Const ListName As String = "12345678-1234-1234-1234-123456789012"
Recordset オブジェクトの Open メソッドの引数に指定するパラメータを設定しておきます。
Const adOpenDynamic = 2 Const adOpenStatic = 3 Const adUseClient = 3 Const adUseNone = 1 Const adUseServer = 2 Const adLockPessimistic = 2 Const adLockOptimistic = 3 Const adLockBatchOptimistic = 4
Sharepoint Listの内容を更新するときに指定する列名をを設定しています。
私の環境では表示列名でも問題なく取得出来ました。うまく取得出来ない場合は内部列名を指定してください。もしくは、列を作成する際に英語で列名を指定する方が見やすいコードになると思います。
Const ID As String = "ID" Const Title As String = "Title" Const Name as string = "名前"
Excelの１行目をタイトル行としているため、タイトル行の位置を設定しています。
Const TitleRow As Long = 1
Connection や Recordset など必要なオブジェクトなどを宣言しています。
また、取得する前に以前のデータを削除したいため、タイトル行以外を ClearContents で削除しています。
Dim con As Object
Dim rs As Object
Dim sql As String
Dim ws As Worksheet
Set ws = MainSheet
Connection と Recordset を CreateObject でインスタンス化します。
本手順では「Microsoft.ACE.OLEDB.12.0」を利用しますので、ConnectionString の Provider に「Microsoft.ACE.OLEDB.12.0」を指定し、Database と List には先ほど設定した ServerURL と ListNameを設定します。その後、Open メソッドで接続を開始しています。
Set con = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
' Open the connection
With con
.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=0;RetrieveIds=Yes;" & _
"DATABASE=" & ServerUrl & ";" & _
"LIST=" & ListName & ";"
.Open
End With
Select クエリを設定しています。
From でリスト名を指定しています。フィールド名と同様で私の環境では表示列名でも問題なく取得出来ました。うまく取得出来ない場合は内部列名を指定するか、英語名でリストを作成してください。
sql = "SELECT * FROM [名前一覧] ;"
Recordset のパラメータを設定し、Open で先ほど設定したクエリを実行しています。
カーソルの種類などは、環境に合わせて変更してください。
.ActiveConnection = con
.CursorType = adOpenDynamic
.CursorLocation = adUseClient
.LockType = adLockOptimistic
MainSheetの最後の行になるまでSharepoint Listに対してクエリを実行します。
ID列が空白の場合はスキップするようにしています。
どのタイミングで処理を止めるかなどは、環境に合わせて変更してください。
For i = TitleRow + 1 To 1048576
If Trim(ws.Range("A" & i).Value) = "" Then GoTo Continue
sql = "UPDATE [請求書メール配信可否] SET " _
& Title & "='" & ws.Range("B" & i).Value & "' " _
& "," & Name & "='" & ws.Range("C" & i).Value & "' " _
& " WHERE " & ID & "=" & ws.Range("A" & i).Value
' Recordset settings parameters
.Source = sql
.Open
Continue:
Next
おすすめ書籍
ひと目でわかるMicrosoft 365 SharePoint運用管理編
数少ないモダン UI を中心とした書籍です。
基本的なことから他ツールのとの連携まで画像や例付きで紹介されていますので、まずはこの一冊を読むことをおすすめします。
ひと目でわかるOffice 365ビジネス活用28の事例 SharePoint Server 2016対応版
クラシック UI 時代には重宝した一冊です。
モダン UI に関する情報が少ないため、直接活用できるものではありませんが、SharePoint で何ができるかを知るにはいいと思います。
おわりに
Excel VBAとADOを利用して、Sharepoint List のデータを更新するプログラムでした。
エラー処理などを考慮していないやっつけプログラムのため、実際に利用する際はエラー処理などを追加していただけたらと思います。
基本的には、前回の記事で紹介した、Sharepoint Listに対してADOでSelectクエリを実行するプログラムとセットで利用しています。社内では、データの照会などはSharepointやPowerAppsを利用し、データの更新については、このプログラムを利用するような運用を行っています。
Microsoft365の環境にすべて移管したいところですが、今までの運用を崩す以上の利点がみつからなかったため現在の運用で落ち着きました。
Sharepoint Listに対してADOでSelectクエリを実行するプログラムについては以下の記事を参照ください。
