Office関連

[Office VBA]ADOでSharePointリストに接続する方法

先日Twitterで @blacklist_ryu さんが下記ツイートをされていました。

ADOでSharePointリストに接続するところで苦戦されているようで、私も気になったので確認してみました。

参考サイト

上記サイトによると、SharePointサイトのURLとリストのIDさえ分かれば接続できるようなので、まずはID(GUID)を取得してみます。

ページのソースからSharePointリストIDを取得

接続したいリストをブラウザーで開き、ソースを表示したところ、「listId」として、それらしい値が載っていました。

ただ、この方法ではサイトの仕様が変わるとIDが取得できなくなる可能性もあるので、別の方法も試してみます。

Get-PnPListによるSharePointリストIDの取得

SharePoint Online: PowerShell to Get List ID (GUID)」によると、PnP PowerShellライブラリのGet-PnPListで簡単に取得できそうです。

PnP PowerShellのインストール

PnP PowerShellをまだインストールしていない場合は、PowerShell(管理者権限)で下記コマンドを実行します。

Install-Module SharePointPnPPowerShellOnline

Get-PnPListの実行

PnP PowerShellがインストールできていれば、上記参考サイトにある通り、Get-PnPListでID含めてサイトの情報を列挙できます。

$url = "https://****.sharepoint.com/"
$usr = "(user)@****.onmicrosoft.com"
$cred = Get-Credential -UserName $usr -Message "Type the password."
Connect-PnPOnline -Url $url -Credentials $cred
Get-PnPList | Select-Object Title, Id

ADOでSharePointリストに接続する方法

リストのIDさえ分かれば、あとはADOで接続するだけですね!
接続文字列は下記サイトに分かりやすくまとめてありました。

Public Sub Sample()
  Dim cn As Object 'ADODB.Connection
  Dim rs As Object 'ADODB.Recordset
  Const SharePointUrl As String = "https://****.sharepoint.com/"
  Const ListId As String = "{********-****-****-****-************}"
  
  Set cn = CreateObject("ADODB.Connection")
  cn.Open "Provider=Microsoft.ACE.OLEDB.16.0;WSS;IMEX=2;RetrieveIds=Yes;DATABASE=" & SharePointUrl & ";LIST=" & ListId & ";" 'プロバイダーは適宜変更
  Set rs = CreateObject("ADODB.Recordset")
  With rs
    .Open "SELECT * FROM list", cn
    .MoveFirst
    Do Until .EOF
      Debug.Print .Fields.Item("Name").Value
      .MoveNext
    Loop
    .Close
  End With
  cn.Close
End Sub

上記コード実行時、365アカウントでサインインしていない場合は下図のようなログイン画面が表示されるので、必要事項を入力してサインインします。

接続に問題が無ければ、下図のようにリストの内容が出力されます。

SharePointのリストに関しては、ロールアウトが始まったばかりのMicrosoft Listsと、どのような使い分けがされていくのかまだ不透明な部分がありますが、すぐに使えなくなることは無いだろうと思います。
(Microsoft Listsに関しては下記 @ai_yamasaki さんのブログで詳しく説明されています。)

また、ADOによるSharePointリストへの接続に関して検索したところ、下記サイトを見つけました。

これによると、接続する際はリストのIDではなく、リスト名でも大丈夫なようです。
わざわざIDを取得した手間は一体何だったんだ・・・!?😅

ADOの解説サイト


2021/9/15 追記:
@KotorinChunChun さんから下記ご報告をいただきました。

Updateメソッドで行ハンドル無効エラーが発生したとのこと。
私も以前同様のエラーが発生したことがあるのですが、そのときは入力が必須になっている列が未入力になっていることが原因でした。
他にも同様に事例がないか検索したところ、下記の質問がヒットしました。

Did you check the SharePoint list mandatory fields? Few columns would automatically be marked as ‘Required’ (eg: Title column). Removing such criteria or including such columns in the code by passing a value would help.

https://stackoverflow.com/questions/50255127/row-handle-is-invalid-error-excel-vba-sharepoint-list-automation より

との回答が寄せられていたので、まずは入力必須となっている列を確認するのが良いのかもしれません🙂

[Office Scripts]London Excel Meetup Groupのイベント紹介前のページ

Teams上のPower Appsを触ってみました。次のページ

関連記事

  1. Office関連

    Google スライドで新規プレゼンテーションを作成するVBAマクロ

    ここ数日PowerPointのマクロに加え、Google Apps S…

  2. Office関連

    文字列を横方向に移動するWordマクロ(WordBasic編)

    いつもお世話になっているWord MVPの新田さんが、まるでカニの動き…

  3. Office関連

    Excel Services JavaScript APIを試してみました(2)

    前回の記事で、JavaScriptコードを貼り付けてExcelワークブ…

  4. Office関連

    Excelで地理データや株価情報を取得する方法

    下記記事にある、今年3月に追加された「データの種類」機能を使って、Ex…

  5. Office関連

    セル内にあるブックマークをカウントするWordマクロ

    Twitterを眺めていたら下記ツイートを発見しました。【Wo…

  6. アイコン一覧

    Office 365アイコン(imageMso)一覧(U)

    Office 365のデスクトップ版Officeアプリケーション(Wo…

コメント

  • コメント (0)

  • トラックバックは利用できません。

  1. この記事へのコメントはありません。

Time limit is exhausted. Please reload CAPTCHA.

※本ページはプロモーションが含まれています。

Translate

最近の記事

アーカイブ

PAGE TOP