【Microsoft 365】ADOを利用してSharepoint Listの一覧を取得する

Microsoft365
2022.07.22

うちにはPower BI のライセンスがないため、Sharepoint Listにためられたデータから、使い慣れたExcelでサクッとグラフや表を作成したい場合があります。一覧をExcelファイルやCSVファイルにエクスポートする機能がありますが、抽出する際にSelect文で集計したり、絞り込んだりしたかったため、Excel VBAで作成しました。

概要

環境は以下になります。

  • Windows 11 pro
  • Excel for Microsoft 365
  • Sharepoint Online
  • 参照設定
    • Microsoft Active X Data Objects 6.1 Library
  • その他
    • 書き込むシートの名前を「MainSheet」に変更しています。
    • コマンドボタンをシート上に追加し、名前を「GetButton」としています。

指定したSharepoint サイトのListに対して Select文を発行し、取得した一覧をExcelのシートに書き込んでいくプログラムです。
タイトル行は事前にエクセルに書き込んでありますので、コードで書き込む列を指定しています。
エクセルの最大行を超えるデータを考慮していないプログラムですので、取り扱うデータ量が多い場合は、別シートに書き込むような処理を追加してください。

サンプルコード

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 GetButton_Click()

    Dim con As Object
    Dim rs As Object
    Dim sql As String

    Dim ws As Worksheet
    Set ws = MainSheet
    ws .Range("A" & (TitleRow + 1), "XFD1048576").ClearContents


    ' 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



    ' add a Query to select all records from your List
    sql = "SELECT * FROM [名前一覧] ;"

    With rs
        ' confirm that recordset is closed
        If .State = 1 Then .Close
    
        ' Recordset settings parameters
        .ActiveConnection = con
        .CursorType = adOpenDynamic 
        .CursorLocation = adUseClient
        .LockType = adLockOptimistic 
        .Source = sql
        .Open
    
        Dim i As Long
        i = TitleRow + 1

        If .RecordCount < 1 Then ' no records
    
        Else 
            Do While Not .EOF
                ws.Range("A" & i).Value = .Fields(ID).Value
                ws.Range("B" & i).Value = .Fields(Title).Value
                ws.Range("C" & i).Value = .Fields(Name).Value                
                i = i + 1
                .MoveNext
            Loop
        End If
    
        .Close
    End With
    
    Set rs = Nothing
    
CleanExit:
    If con.State = 1 Then
        con.Close
        Set con = Nothing
    End If
    
    MsgBox "Finished reading data from 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

Recordset オブジェクトの Fields オブジェクトから値を取り出す時に指定する名前を設定しています。
私の環境では表示列名でも問題なく取得出来ました。うまく取得出来ない場合は内部列名を指定してください。もしくは、列を作成する際に英語で列名を指定する方が見やすいコードになると思います。

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
    ws.Range("A" & (TitleRow + 1), "XFD1048576").ClearContents

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 
        .Source = sql
        .Open

レコードの件数が０件の場合は何も処理をしません。
必要に応じてメッセージを表示するなり処理を追加してください。

レコードの件数が1以上の場合、最後のレコードになるまでエクセルシートのセルに値を書き込む処理を繰り返します。件数が1048576を超えない前提で作成していますので、必要に応じて新しいシートを作成するなどの処理を追加してください。

        If .RecordCount < 1 Then ' no records
    
        Else 
            Do While Not .EOF
                ws.Range("A" & i).Value = .Fields(ID).Value
                ws.Range("B" & i).Value = .Fields(Title).Value
                ws.Range("C" & i).Value = .Fields(Name).Value                
                i = i + 1
                .MoveNext
            Loop
        End If

おすすめ書籍

ひと目でわかるMicrosoft 365 SharePoint運用管理編

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

ひと目でわかるMicrosoft 365 SharePoint運用管理編
created by Rinker
ひと目でわかるOffice 365ビジネス活用28の事例 SharePoint Server 2016対応版

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

ひと目でわかるOffice 365ビジネス活用28の事例 SharePoint Server 2016対応版
created by Rinker

おわりに

Excel VBAとADOを利用して、Sharepoint List のデータを直接取得するプログラムでした。
エラー処理などを考慮していないやっつけプログラムのため、実際に利用する際はエラー処理などを追加していただけたらと思います。
Sharepoit Listは簡単にデータをためていけるため利用しやすいのですが、データの件数によっては、PowerAppsでフィルターをかける際に制限があったりと工夫が必要な場合があります。Dataverse などを利用したらいいのだとは思いますが、予算がでないため抜け道を探すのに苦労します。

Sharepoint Listに対してUpdateクエリを実行する方法については、以下の記事を参考ください。

【Microsoft 365】ADOを利用してSharepoint Listの内容を一括で更新する
Sharepoint ListはPowerAppsと連携したり、チーム内での情報をためたり、簡易データベースとして利用できたりとかなり便利です。ただExcelになれていると、関数で内容を編集や変換したりしたいときがあるのですが、用途が違いま...
naberium.com
タイトルとURLをコピーしました