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

ホーム
Office関連, Office 365
[Office VBA]ADOでSharePointリストに接続する方法

Office関連

2020.09.22

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

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

ここで苦戦中・・・説明がざっくりだ・・ https://t.co/7J10zpHK2H pic.twitter.com/TFyrVnTAeA

— りゅうりゅう (@blacklist_ryu) September 18, 2020

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

参考サイト

econoshift.com

エクセルとSharePointのADO連携の仕方、概要（ADOとSQLの超簡単な説明も）

https://econoshift.com/ja/excel-sharepoint-method-basics/

この記事では経営ダッシュボードや業務システムを作るためのエクセルとSharePointの連携の仕組みを分かり易く説明します。その接続の中心となる接続プラットフォームのADOとデータベース専用言語のSQLについて図解しています。

nyoro2.net

SharePoint のデータを ADO などで読む | NYORO PRESS

http://nyoro2.net/wp/?p=398

上記サイトによると、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

docs.microsoft.com

PnP PowerShell の概要

https://docs.microsoft.com/ja-jp/powershell/sharepoint/sharepoint-pnp/sharepoint-pnp-cmdlets?WT.mc_id=M365-MVP-4029057

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で接続するだけですね！
接続文字列は下記サイトに分かりやすくまとめてありました。

www.connectionstrings.com

Microsoft ACE OLEDB 12.0 Connection Strings - ConnectionStrings.com

https://www.connectionstrings.com/ace-oledb-12-0/#sharepoint

Connection Strings using Microsoft.ACE.OLEDB.12.0 for connections to Excel, Access, DBF / FoxPro, SharePoint, Excel 97, Excel 2000, Excel 2002, Excel 2003.

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 さんのブログで詳しく説明されています。)