Excel VBAからSharepoint ListへADODBを使って読み書きする #VBA

なぜ今更VBAなのか

他にもリッチな言語やBIツールがあったり、PowerQueryがExcelに搭載されたりしているこの世の中においてなぜ未だにVBAでSharepointにアクセスしようとするのか。という疑問はたしかにその通りですと認めるしか無いけれど、ツールを開発できる環境がVBAくらいしかない。外部ソフトインストールも禁止。という職場もあるのです。
むしろSharepointが使えるようになったことが画期的！すごい！ありがとう！！
そんなわけでSharepoint ListをちょっとしたDB代わりに使いたい場合なんかに参考になるはずです。

いろいろ調べたり試行錯誤して、なんとか読み・書きが出来たのでメモしておく

接続する

ポイントは接続文字列中のLIST=はSharepoint ListのIDなのか名前なのかという点。
参考サイトによってIDだ名前だと分かれていたが自分の環境ではIDでは接続できず、名前で接続できた。

    Dim SharepointURL as String
    Dim ListName as String

    SharepointURL = "https://xxxxx.sharepoint.com/sites/yyyyyy"
    ListName="YourListName" '{xxxx-000-xxxxx}みたいなIDじゃなくて、普通にリストの名前でOK
    
    Dim cn as Object
    Set cn = CreateObject("ADODB.Connection")
    cn.Open "Provider=Microsoft.ACE.OLEDB.16.0;WSS;IMEX=2;RetrieveIds=Yes;DATABASE=" & SharepointURL & ";LIST=" & ListName & ";"

これでDB(Sharepoint List)に接続できる

問い合わせ

あとは普通にADODBでクエリを投げるだけだがポイントはSQLのFROM節に何を指定するのかという点
カッコ付きのリスト名を指定したら接続できた。

例)Sharepoint Listの名前がUserTableだった場合

SELECT * FROM [UserTable] WHERE ....

みたいな感じ。なぜかは知らない。

ADODB接続はサンプルコードを見てもらうとして、続きから書くと

    Dim rs as Object
    Dim QueryString as String
    
    Set rs = CreateObject("ADODB.Recordset")
    QueryString = "SELECT * FROM [" & ListName & "]"

    rs.Open QueryString,cn,adOpenForardOnly,adLockReadOnly

Openメソッドの第2,3引数については下記参照。参照設定せずに使うと多分定数使えないので値直打ちするか勝手に自分で定義するかすること

定数	値	説明
adOpenDynamic	2	動的カーソルを使用します。他のユーザーによる追加、変更、削除が表示され、プロバイダーがサポートしていない場合はブックマークを除き、Recordset 内のすべての種類の移動が許可されます。
adOpenForwardOnly	0	既定値。順方向専用カーソルを使用します。静的カーソルと同じですが、レコードを前方にしかスクロールできない点が異なります。これにより、Recordset を 1 回だけパススルーする必要がある場合に、パフォーマンスが向上します。
adOpenKeyset	1	キーセットカーソルを使用します。他のユーザーが追加したレコードを表示できないことを除き、動的カーソルと同様ですが、他のユーザーが削除したレコードには Recordset からアクセスできません。他のユーザーによるデータの変更は引き続き表示されます。
adOpenStatic	3	静的カーソルを使用します。これは、データの検索やレポートの作成に使用できるレコードセットの静的コピーです。他のユーザーによる追加、変更、または削除は表示されません。
adOpenUnspecified	-1	カーソルの種類を指定しません。

定数	値	説明
adLockBatchOptimistic	4	オプティミスティックバッチ更新を示します。バッチ更新モードで必要です。
adLockOptimistic	3	レコードごとのオプティミスティックロックを示します。プロバイダーは、Update メソッドを呼び出すときにのみレコードをロックする、オプティミスティックロックを使用します。
adLockPessimistic	2	レコードごとのペシミスティックロックを示します。プロバイダーは、レコードを正常に編集するために必要な処理を行います。通常は、編集直後にデータソースでレコードをロックします。
adLockReadOnly	1	読み取り専用レコードを示します。データを変更することはできません。
adLockUnspecified	-1	ロックの種類を指定しません。複製の場合、複製は元のロックタイプと同じロックタイプで作成されます。

読む

クエリ結果の処理の仕方

    rs.Fields.Item("YourColumnName").Value

これで今いる行の、指定した各カラムの値を取得できる

クエリ結果を一回なめるだけの場合は下記のテンプレを使えばOK
カラム名は好きに書き換えて

    rs.MoveFirst
    Do Until rs.EOF
        Debug.Print rs.Fields.Item("YourColumnName").Value
        rs.MoveNext
    Loop

書く

↑の読む場合はレコードセットのLockTypeをReadOnlyで開いたが、書く場合は当然それだとエラー吐くので注意。では何にするのか？だが、adLockOptimisticでいいと思う。たぶん。もっと適切なのがあればそれで。
CursorTypeは用途に合わせてどうぞ

ポイントは、.AddNewと同時にレコードを渡した場合は.Updateは自動で行われるため不要。
逆に順々に新規レコードに値をセットして、揃ったら更新したい場合は.AddNew時にレコードを渡さず空で新規レコード作成してその後値をセット、完了したら.Updateという流れになる。

    Dim SampleTime as Date
    Dim SampleValue as Integer

    SampleTime= #31/10/2023 10:00 AM#
    SampleValue = 1
    
    Set rs = CreateObject("ADODB.Recordset")
    With rs
        .Source=ListName
        .ActiveConnection = cn
        .CursorType = adOpenKeyset
        .LockType = adLockOptimistic
        .Open
    End With

    '実際にデータを書き込み
    rs.AddNew Array("StringColumn","ValueColumn","TimeColumn"),Array("文字列",SampleValue,SampleTime)

閉じる

使ったら閉じる

    rs.Close
    cn.Close