IT Hands-on Lab

小規模組織向けIT環境の構築・運用に役立つ情報を、ハンズオン形式で紹介しています。

トップ > VBA⇔Cloud > 【Excel VBA⇔Cloud】ADO×SharePointリスト データ検索&更新
最終更新日

【Excel VBA⇔Cloud】ADO×SharePointリスト データ検索&更新

VBA⇔Cloud アプリ開発

Excel VBA ⇔ Cloud データ検索&更新 (ADO×SharePointリスト)

Excel VBAのデータベースアクセスライブラリADO(ActiveX Data Objects)を使用して、クラウド上にあるSharePointリストのデータ検索・更新を行う方法を紹介します。
  • 操作環境：
    • OS：Windows 11
    • Webブラウザー:Edge
  • 使用プラン：Microsoft 365 Business Premium(*)
    *SharePointリストにアクセスできる組織アカウントでサインインしていること

なお、この記事では、VBAの使用許可や開発メニュー表示といった初期設定や、VBAエディタの使い方の説明は割愛いたします。

 

 

SharePointリスト用意

この例では、以前紹介した書籍管理アプリのSharePointリストを使用します。
このSharePointリストの作成方法や各項目の設定は以前の記事をご覧ください。

図表1-1 SharePointリストへ

図表1-1 SharePointリストへ

 

Excel VBAのADOで必要となるSharePointリストのURLとIDを確認します。
SharePointリストの一覧画面左上の設定アイコンを選択し、[リストの設定]を選択します。

図表1-2 SharePointリストの設定へ

図表1-2 SharePointリストの設定へ

 

リストの設定画面のURLから、必要な値を特定します。

  • リストのURL：https://{ドメイン名}.sharepoint.com/sites/{サイト名}/
  • リストのID："List=%7Bxxxxxxxx-xxxx-xxxx-xxxxxxxxxxxx%7D"の"xxxxxxxx-xxxx-xxxx-xxxxxxxxxxxx"部分

図表1-3 SharePointリストのリストID確認

図表1-3 SharePointリストのリストID確認

 

Excelファイル用意

VBAを実行するExcelファイルを用意します(拡張子はxlsmとします)。

  • Menuシート：各ボタン(ActiveXコントロール)を押すと、SharePointリストに接続し、データの検索や更新を行います。

図表2-1 ExcelファイルのMainシート

図表2-1 ExcelファイルのMainシート

 

  • Searchシート：Menuシートの[書籍管理データ検索]ボタンを押すと、SharePointリストの検索結果をこのシートに出力します。

図表2-2 ExcelファイルのSearchシート

図表2-2 ExcelファイルのSearchシート

 

  • Renewシート：このシートに追加・変更・削除対象(Action列で識別)のデータを入力しておき、Menuシートの[書籍管理データ反映]ボタンを押すと、SharePointリストに反映されます。

図表2-3 ExcelファイルのRenewシート

図表2-3 ExcelファイルのRenewシート

 

  • VBAプロジェクト：Mainシート上にソースを実装します。画面に表示されているのは検索・更新ボタンクリック時にデータ検索・更新のプロシージャを呼び出すソースです。データ検索・更新のプロシージャ自体のソースは次の工程以降で紹介します。

図表2-4 ExcelファイルのVBAプロジェクト

図表2-4 ExcelファイルのVBAプロジェクト

 

VBA実行時にADOに関するエラーが発生する場合は、[ツール>参照設定]を選択し、[Microsoft ActiveX Data Objects x.x Library]のチェックをオンにします。

*この記事を作成するときに使用した環境では、チェックが未選択でもエラーは発生しませんでした

図表2-5 VBAプロジェクトの参照設定

図表2-5 VBAプロジェクトの参照設定

 

Excel VBAによるデータ検索実装

基本的にAccessやExcelのデータ検索と同じで、以下がポイント事項です。

  • コネクションの設定時に、工程1で確認したリストURLとIDを指定する
  • 検索時のテーブル名は大カッコで囲む([テーブル名]のように)
  • データ行書き出し時に意図しない型変換を避けるため頭に'を付加した
Private Sub searchSPListBook()

  '定数宣言
  Const site_url As String = "https://{ドメイン名}.sharepoint.com/sites/{サイト名}/" 'SPリストのURL
  Const list_id As String = "{xxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx}" 'SPリストのID
  Const adOpenKeySet = 1 'CursorType:1=キーセット
  Const adLockReadOnly = 1 'LockType:1=読取専用
  
  '変数宣言
  Dim wb As Workbook
  Dim ws As Worksheet
  Dim conn As Object
  Dim res As Object
  Dim sql As String
  Dim cnt_row As Long
  Dim cnt_col As Long
  
  '出力先ワークシートの設定 、データ初期化
  Set wb = ThisWorkbook
  Set ws = wb.Worksheets("Search")
  ws.Rows("1:" & ws.Range("A" & ws.Rows.Count).End(xlUp).Row).Delete
  
  'コネクションの設定、オープン(SPリスト)
  Set conn = CreateObject("ADODB.Connection")
  conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=2;RetrieveIds=Yes;DATABASE=" & site_url & ";LIST=" & list_id & ";"
  
  'レコードセットの設定、レコードセットの取得(SELECT文実行)
  Set res = CreateObject("ADODB.Recordset")
  sql = "SELECT ID,category,status,borrower,borrowed_date,isbn_code, "
  sql = sql & "published_date,book_name,author,publisher,memo,thumbnail "
  sql = sql & "FROM [書籍管理];"
  res.Open sql, conn, adOpenKeySet, adLockReadOnly
  
  'レコードセットsearchシートに書き出す
  For cnt_row = 0 To res.RecordCount - 1
    For cnt_col = 0 To res.Fields.Count - 1
      If (cnt_row = 0) Then
        ws.Cells(cnt_row + 1, cnt_col + 1) = res(cnt_col).Name 'タイトル行
      End If
      ws.Cells(cnt_row + 2, cnt_col + 1) = "'" & res(cnt_col).Value 'データ行(頭に'を付加)
    Next cnt_col
    res.MoveNext
  Next cnt_row
  
  'レコードセットとコネクションのクローズ
  res.Close
  Set res = Nothing
  conn.Close
  Set conn = Nothing

End Sub
*{ドメイン名}、{サイト名}は工程1で確認した値とし、これらのカッコ{ }は不要です。なお、リストIDのカッコ{ }は必要です

 

Excel VBAによるデータ更新実装

基本的にAccessやExcelのデータ更新と同じで、以下がポイント事項です。

  • コネクションの設定時に、工程1で確認したリストURLとIDを指定する
  • この例では、データの変更・削除のキーとして、SharePointリストに予め用意されているID列を使用した(もちろん他の列をキーにすることも可能)
Private Sub renewSPListBook()

  '定数宣言
  Const site_url As String = "https://{ドメイン名}.sharepoint.com/sites/{サイト名}/" 'SPリストのURL
  Const list_id As String = "{xxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx}" 'SPリストのID
  Const adOpenDynamic = 2 'CursorType:2=動的
  Const adLockOptimistic = 3 'LockType:3=レコード毎楽観的ロック
  Const adSearchForward = 1 'searchDirection:1=前方から検索
  
  '変数宣言
  Dim wb As Workbook
  Dim ws As Worksheet
  Dim conn As Object
  Dim res As Object
  Dim cnt_row As Long
  Dim cnt_res As Long
  Dim max_row As Long
  Dim max_col As Long
  Dim array_name As Variant
  Dim array_value As Variant
  
  '出力先ワークシートの設定
  Set wb = ThisWorkbook
  Set ws = wb.Worksheets("Renew")
  
  'コネクションの設定、オープン(SPリスト)
  Set conn = CreateObject("ADODB.Connection")
  conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=2;RetrieveIds=Yes;DATABASE=" & site_url & ";LIST=" & list_id & ";"
  
  'レコードセットの設定、レコードセットの取得
  Set res = CreateObject("ADODB.Recordset")
  res.Open "書籍管理", conn, adOpenDynamic, adLockOptimistic
  
  'Renewシートの最大行列数を求め、データ反映で使用するvariant変数のサイズを定義する
  max_row = ws.Range("A" & ws.Rows.Count).End(xlUp).Row - 1
  max_col = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column - 1
  ReDim array_name(max_col - 2)
  ReDim array_value(max_col - 2)
  
  'データ反映対象のタイトル行をvariant変数に格納する(action,ID列をのぞく)
  For cnt_col = 0 To max_col - 2
    array_name(cnt_col) = ws.Cells(cnt_row + 1, cnt_col + 3)
  Next cnt_col
  
  With res
    For cnt_row = 0 To max_row - 1
    
    'データ反映対象のデータ行をvariant変数に格納する(action,ID列をのぞく)
    For cnt_col = 0 To max_col - 2
      If ws.Cells(cnt_row + 2, cnt_col + 3) <> "" Then
        array_value(cnt_col) = ws.Cells(cnt_row + 2, cnt_col + 3).Value
      Else
        array_value(cnt_col) = Null
      End If
    Next cnt_col
    
    'データ反映を行う
    If ws.Cells(cnt_row + 2, 1).Value = "ins" Then 'データ追加
      .addNew array_name, array_value
    ElseIf ws.Cells(cnt_row + 2, 1).Value = "upd" Then 'データ変更
      .MoveFirst
      .Find "ID = " & ws.Cells(cnt_row + 2, 2).Value, 0, adSearchForward
      .Update array_name, array_value
    ElseIf ws.Cells(cnt_row + 2, 1).Value = "del" Then 'データ削除
      .MoveFirst
      .Find "ID = " & ws.Cells(cnt_row + 2, 2).Value, 0, adSearchForward
      .Delete
    End If
    
    Next cnt_row
  End With
  
  'レコードセットとコネクションのクローズ
  res.Close
  Set res = Nothing
  conn.Close
  Set conn = Nothing
  
End Sub
*{ドメイン名}、{サイト名}は工程1で確認した値とし、これらのカッコ{ }は不要です。なお、リストIDのカッコ{ }は必要です

 

Dataverse・Dataverse for TeamsテーブルへのADO接続(参考)

SharePointリストではなく、DataverseやDataverse for Teamsのテーブルに対し、VBAのADOでデータ検索・更新ができるか試してみました。認証関連(おそらく多要素認証)で問題が生じ、使用できませんでした。

別の方法として、「CData Excel Add-In for Microsoft Dataverse」のような有料ツールを導入すると、VBAによるデータ検索・更新ができそうです。今回は試していないため、紹介はしません。

 

また、VBAからDataverse Web APIを使用したデータ検索・更新については、Dataverseなら可能ですので、次回以降の記事で紹介します。なお、Dataverse for TeamsはDataverse Web APIに対応しておらず、使用できません。

 

2025/07/02追記:SharePointリストの列名の扱い(参考)

SharePointリストの列名でADOにおけるSQLの予約語が使われている場合、別の文字に置き換わりますので、取扱うときにご注意ください(以下)。

  • [ ](半角大カッコ) ⇒ ()
  • .(半角ドット) ⇒ #

図表6-1 SharePointリストの列名の扱い(SPリスト)

図表6-1 SharePointリストの列名の扱い(SPリスト)

 

図表6-2 SharePointリストの列名の扱い(データ検索SQLと結果)

図表6-2 SharePointリストの列名の扱い(データ検索SQLと結果)

 

予約語の使用による置き換わりが発生しているかどうかわからない場合は(SQLエラーが解消されない場合も)、一度ADOのSQLにおいて、SELECT * FROM [SharePointリスト名]で全列出力してみると手っ取り早いです。

図表6-3 SharePointリストの列名の扱い(全列検索)

図表6-3 SharePointリストの列名の扱い(全列検索)

 

*この例で追加した列のようにSharePointリストの列名は日本語も使用できます。しかし、内部名が分かり辛くなるため、初回作成時は英語の列名で作成し、後で日本語に変更することを推奨します

 

おわりに

今更VBA？と思う方もいらっしゃるかもしれません。しかし、ITに詳しい人が組織内に殆どいない環境において、何かあったときに組織内で素早く対応できるよう、あえてVBAを採用することもあるかと思います。
SharePointリストはクラウド上にありますが、AccessやExcelのデータと同様、VBAのADOを使ってデータ検索・更新を行えるため、そうした環境でも運用しやすいです。

 

私がよく使う例として、各種申請システムのデータの状態変化に応じてSharePointリストに通知用データを登録するVBAをスケジュール実行し、Power Automateで担当者へTeamsやEmailによる通知を行っています。また、SharePointリストのデータ入替や一括更新の際もVBAを使用します。
VBAはOfficeツールのライセンス料以外にお金がかかりませんし、SharePointリストもPower Platformからの接続で追加料金がかかりません。これらの機能を組み合わせ、お金をかけずに効率化を行うのに適しています。

 

 

当ブログ内の関連記事

elmgrn.hatenablog.com