【Microsoft 365】ADOを利用してSharepoint Listの内容を一括で更新する

Microsoft365

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の1行目をタイトル行としているため、タイトル行の位置を設定しています。

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

おすすめ書籍

数少ないモダン UI を中心とした書籍です。
基本的なことから他ツールのとの連携まで画像や例付きで紹介されていますので、まずはこの一冊を読むことをおすすめします。

クラシック UI 時代には重宝した一冊です。
モダン UI に関する情報が少ないため、直接活用できるものではありませんが、SharePoint で何ができるかを知るにはいいと思います。

おわりに

Excel VBAとADOを利用して、Sharepoint List のデータを更新するプログラムでした。
エラー処理などを考慮していないやっつけプログラムのため、実際に利用する際はエラー処理などを追加していただけたらと思います。
基本的には、前回の記事で紹介した、Sharepoint Listに対してADOでSelectクエリを実行するプログラムとセットで利用しています。社内では、データの照会などはSharepointやPowerAppsを利用し、データの更新については、このプログラムを利用するような運用を行っています。
Microsoft365の環境にすべて移管したいところですが、今までの運用を崩す以上の利点がみつからなかったため現在の運用で落ち着きました。

Sharepoint Listに対してADOでSelectクエリを実行するプログラムについては以下の記事を参照ください。

タイトルとURLをコピーしました