Excel (VBA)

Excel VBAに関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 10 Home : Microsoft 365)
ExcelVBAでSharepointのリストのデータを更新したい
投稿日時: 24/10/31 13:23:22
投稿者: Repo

お世話になります。
ExcelVBAでSharpointのリストから条件に一致するデータをシートへ出力して、その後にSharepointのリストの特定の列に完了したという値(できれば完了日)を更新したいのですが、うまくいかずに困っています。
ADODBで接続してデータの取得・出力はSELECT文でできましたが、UPDATEでのSharepointのリストへの更新ができません。
参照設定:Microsoft AxtiveX Data Objects 6.1 Library
OS:WindowsEnterprise
 
試したコードは以下になります。
Public Sub test()
 
    Dim cn As Object
    Dim rs As Object
     
    Dim ListName As String
    Dim UpdateQuery As String
    Dim i As Long
     
    On Error GoTo Err_testQuery
     
    'オブジェクトの設定
    Set cn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")
     
    Const SPO_URL = "http://mmna.Sharepoint.com/Sites/*********"
    Const SPO_LISTID = "{8b28af-******************-8df20305c}"
    ListName = "test_Purchase"
     
    cn.Open "Provider=Microsoft.ACE.OLEDB.16.0;WSS;IMEX=2;RetrieveIds=Yes;DATABASE=" & SPO_URL & ";LIST=" & SPO_LISTID & ";"
     
    'パターン1開始
    UpdateQuery = "UPDATE [" & SPO_LISTID & "] [PONo] = 'TEST' WHERE UnitCode ='KO'"
    'UpdateQuery = "UPDATE [" & ListName & "] [PONo] = 'TEST' WHERE UnitCode ='KO'"
    cn.Execute UpdateQuery
    'パターン1終了
     
  'パターン2開始
    UpdateQuery = "SELECT * FROM[" & SPO_LISTID & "] WHERE UnitCode ='KO'"
    rs.Open UpdateQuery, cn, adOpenKeyset, adLockOptimistic
     
    If rs.EOF Then
        MsgBox "データ該当なし"
    Else
        For i = 0 To rs.Field.Count - 1
            Debug.Print rs.Field(i).Name & ":" & rs.Field(i).Value
         
        Next
       rs.Field("PONo").Value = "TEST"
         
    End If
    
    Set rs = Nothing
    'パターン2終了
     
    Set cn = Nothing
 
Exit Sub
 
'エラー処理
Err_testQuery:
    'エラーメッセージ
    MsgBox "エラーナンバー:" & Err.Number & vbCrLf & _
            "エラー内容:" & Err.Description & vbCrLf & _
            "エラーソース:" & Err.Source
    If cn.State = adStateOpen Then
        Set cn = Nothing
    End If
 
 
End Sub
 
---------エラーメッセージ---------
*パターン1の場合
エラーナンバー:-2147467259
エラー内容:適正する値が正しくないか、データの入力規則に違反しているため、このフィールドを更新できません。エラーを修正していやり直して下さい。
エラーソース:Microsoft Access Database Engine
 
*パターン2の場合
エラーナンバー:-3219
エラー内容:このコンテキストでは操作は許可されていません
エラーソース:ADODB.Recordset
-------------------------------
 
パターン1,パターン2どちらかをコメントアウトしてそれぞれ実行しましたが
パターン1はcn.Execute UpdateQueryの部分で落ちます。
パターン2はDebul.Printも問題なく、
rs.Field("PONo").Value = "TEST"の後もローカルウィンドウでValueに'TEST'値が設定されているのは確認できていますが、rs.Close時に落ちます。
rs.closeを省いてもリストに更新はできていません。
 
参照テーブルとなるリストID/リスト名ですが、リスト名だとうまくいかないのでリストIDで設定しています。
 
Sharepointでのリストへのアクセス許可はあるので、
直接値を書き込んで保存することはできますが、ExcelからだとSharepointリストへの編集ができないのか、ただのUPDATE文の書き方の問題なのか判断できないのでおしえていただきたいです。
(色々調べているとADODBでリストのデータを更新している記事を見かけるので、なぜ自分のができないのか不明です)
Sharepointリストは社内で扱っているリストになり、2つ違うリストで試したのですが、同じような感じでエラーでおちます。
 
できない場合はPowerAutomateでのデータ更新も検討していますが、ただVBAで行ったほうが処理速度が速いのでなるべくVBAでできればいいなと考えています。
またSharepointAPIというやり方もネット記事で見かけましたが、社内でツールをダウンロードするのに制限等あるのでADODBでできれば、、という感じです。
 
ご教授の程よろしくお願いします。
 

回答
投稿日時: 24/10/31 14:41:41
投稿者: Suzu

SharePoint に対しては判りかねますが
 
 
ローカルの xlsx ファイルに対してなら
 
パターン1 : SQL UPDATE句 による データ更新
 SQL 構文 に問題があります。 SET句が必要
 
 
パターン2 : レコードセット カーソルのあるレコードの更新
 「Close時に落ちる」と言うのが、何を表しているのか理解しかねますが
  更新の為には、UPDATEメソッド が 必要
 
 
まずは、ローカルファイルに対しテストを行い SharePointファイルに対しテストを進めてはどうでしょう。

投稿日時: 24/10/31 14:59:31
投稿者: Repo

Suzu様
 
コメントありがとうございます。
 
パターン1 : SQL UPDATE句 による データ更新
 SQL 構文 に問題があります。 SET句が必要
→こちらについて、質問のコードが間違っておりました。記述ミスですみません。
SET句は実際のコードには記述しておりましたがエラーで落ちます
UpdateQuery = "UPDATE [" & SPO_LISTID & "] SET [PONo] = 'TEST' WHERE UnitCode ='KO'"
 
パターン2 : レコードセット カーソルのあるレコードの更新
 「Close時に落ちる」と言うのが、何を表しているのか理解しかねますが
  更新の為には、UPDATEメソッド が 必要
 
→ こちらについて
     rs.Field("PONo").Value = "TEST"の後に
     rs.Update
を追記して実行したところ以下のエラーメッセージが表示されました(パターン1のエラーメッセージと同様)
 
エラーナンバー:-2147467259
エラー内容:適正する値が正しくないか、データの入力規則に違反しているため、このフィールドを更新できません。エラーを修正してやり直して下さい。
エラーソース:Microsoft Access Database Engine
 
 
一度ローカルファイルに対してUPDATEができるかを検討しています。取り急ぎ上記についてご連絡いたしました

回答
投稿日時: 24/10/31 15:59:11
投稿者: Suzu

掲示コードをよく見るとオブジェクトのメンバー(メソッド名、コレクション)について
いろいろ間違っている様ですが、エラーメッセージを見る限り
 
それらが問題では無さそうなので、それらはおいておくとして
 
 
rs.Fields("PONo").Value = "TEST"
rs.Update
 
"UPDATE [" & SPO_LISTID & "] SET [PONo] = 'TEST'
 
どちらでも
 
フィールド「PONo」に、文字 TEST に書き換える 際に
 

引用:
エラーナンバー:-2147467259
エラー内容:適正する値が正しくないか、データの入力規則に違反しているため、このフィールドを更新できません。エラーを修正してやり直して下さい。
エラーソース:Microsoft Access Database Engine

 
だとすれば
 
PONo のデータ型 が、数値型として認識されているフィールドに、
文字列を入れようとしている事が考えられます。
 
認識は自動で行われる様なので
 
 
ADODBでExcelを操作する時の列毎の型判定の基準
https://www.excellovers.com/entry/adodbdatatype01
 
ExcelシートをSQLで検索(型が一致しないエラー)
https://qiita.com/EmikoKishi/items/7c5e4154cf0443991b10
 
ADOでAccess・Excel・CSVへ接続・操作
https://atsumitm.iobb.net/its/its-042.php
 
 
MaxScanRows は Excelには適用されなかったはずですし
schema.ini も 用をなさなかったはず。
 
 
解決方法(一応)
https://qiita.com/EmikoKishi/items/7c5e4154cf0443991b10#%E8%A7%A3%E6%B1%BA%E6%96%B9%E6%B3%95%E4%B8%80%E5%BF%9C
 
 
データ型 が 自動で認識されている 為であるなら SQL での アクセスは諦めた方が手っ取り早い気がします。
 
最終的に SharePoint にて運用するのであれば、複数人が同時にアクセスする可能性があるのですよね?
本格的な DBを使うなり、 排他処理を Excelにまかせるなりした方が良いと思います。

投稿日時: 24/11/01 14:30:27
投稿者: Repo

Suzu様
 
コメントありがとうございます。
返信が遅くなり申し訳ありません。
 
コードの記述ミス等すみません
(業務で使用するコードを直接コピペできないため、画面を照らし合わせながら記述をしており所々間違えておりご迷惑をおかけします)
 
ローカルファイルで行ったところうまくいきました。
 

引用:

PONo のデータ型 が、数値型として認識されているフィールドに、
文字列を入れようとしている事が考えられます。

 
この部分を検証してみてSQL文の末尾に 
"ORDER BY [PONo] DESC"を追記しましたがやはりrs.Update時にエラーになりました。
 
型についても気になったのでレコードセットをループして検証しました。
まずフィールドの型が何かを調べました
コードを以下のように変更してみました(転記しましたが記述ミスがあったらすみません)
 
If rs.EOF Then
    MsgBox "データ該当なし"
Else
    'レコードセットループ
If rs.EOF Then
        MsgBox "データ該当なし"
Else
    'レコードセットループ
    Do While Not rs.EOF
        Debug.Print rs.Fields(0).Name & ":" & rs.Fields(0).Value 'IDフィールド
        Debug.Print rs.Fields(47).Name & ":" & rs.Fields(47).Value 'PONoフィールド
        
        '1.PONoフィールドの型
        Debug.Print rs.Fields(47).Name & ":" & GetFieldTypeName(rs.Fields(47).Type)
        
        '2.PONoフィールドが読み取り専用かどうかを確認
        If (rs.Fields(47).Attributes And adFldUpdatable) = 0 Then
            Debug.Print rs.Fields(47).Name & "は読み取り専用です"
        Else
            Debug.Print rs.Fields(47).Name & "は更新可能です"
        End If
        'PONoフィールドが文字列型の場合
        If rs.Fields(47).Type = adVarWChar Then
            rs.Fields(47).Value = "TEST"
        End If
         
        rs.MoveNext
    rs.Update
    Loop
    rs.Close
              
    'コメントアウト
    'For i = 0 To rs.Field.Count - 1
    ' Debug.Print rs.Field(i).Name & ":" & rs.Field(i).Value
    'Next
    'rs.Field("PONo").Value = "TEST"
 
…以下続く
 
End If
 
==================プロシージャ外別途記述===========================
'フィールド型を文字列に変換する関数
Function GetFieldTypeName(FieldType As Integer) As String
    Select Case FieldType
        Case adInteger
           GetFieldTypeName = "adInteger"
        Case adVarWChar
           GetFieldTypeName = "adVarWChar"
        Case adLongVarWChar
           GetFieldTypeName = "adLongVarWChar"
        Case Else
           GetFieldTypeName = "Unknown"
    End Select
End Function
====================================================
 
まず、1.PONoフィールドの型は"adVarWChar"となっており、
2.PONoフィールドが読み取り専用かどうかについては更新可能でした。
 
 
If rs.Fields(47).Type = adVarWChar Then
   rs.Fields(47).Value = "TEST"
End If
("TEST"と文字列を指定するパターンとCVar("TEST")と指定するパターンを行いました)
 
この部分はTrueとなりrs.Fields(47).Valueに"TEST"と書きこめましたが
rs.MoveNextでエラーで落ちます。
フィールドは更新可能となっているのにできないのがそこが謎ですが、やはり型がだめだという事でしょうか
 
他の方法でやろうかを検討していますが、上司からマクロでできないかは言われているので、なかなか決断に迷いますが、プロの目から見てやはり難しいでしょうか?
 

回答
投稿日時: 24/11/01 16:38:53
投稿者: Suzu

引用:
ローカルファイルで行ったところうまくいきました。

 
これは、ローカルファイルに対し ADOでの接続、書き換え操作を行い希望通りに動作した
という認識であっていますか?
またその時のコードは、最初の質問文のコードと言う事でしょうか?
 
だとすれば、
ローカル では OK
SharePoint では NG
であり、SharePointになった事で、そのままのコードではNGになる という事であり、
ConnectionString なり、Driverなり、Provider等
接続について、見直しが必要なのでは?
 
引用:
コードを以下のように変更してみました(転記しましたが記述ミスがあったらすみません)

以降については、SharePoint に対し実行?
接続について、何も変えていないのであれば 更新できなくて当然でしょう。
というか、UpDateの位置に問題があるので
このコードでは、ローカルでも更新はできないと思いますが・・・
 
UpDateの位置に問題があるので、ローカルにしろSharePointにしろ 希望通りの動作にはならないはずです。
それとも、「落ちる/落ちない」だけで 希望通りになっている と判断されているのでしょうか?
 
 
 
何をもって、ローカルではうまくいったと判断したのか?
    データの中身も確認し 書き換えられているのを確認したのでしょうか?
ローカルに対し、うまく行ったコードの 接続先 を SharePointに変え 更新されるのか、されないのか?
 
24/11/01 14:30:27 掲示 の コードの接続先を ローカル として 希望通りの動作になるのか
        〃             SharePointにして 希望通りの動作になるのか
 
きちんと、段階を踏んで 要因について推測が行える様にテストを行うようにしましょう。
 
 
 
スペルミス等 細かい部分は コンパイル時にエラーとなるので回答者側でも
スペルミスだろうなとは推測はできますが
 
全体フローについて 順番が違っているのであれば
転記ミスなのかどうか、回答者には判断つきません。
 
フローについては転記ミスが無い様に願います。

投稿日時: 24/11/01 20:48:09
投稿者: Repo

コメントありがとうございます。
 

引用:

これは、ローカルファイルに対し ADOでの接続、書き換え操作を行い希望通りに動作した
という認識であっていますか?
またその時のコードは、最初の質問文のコードと言う事でしょうか?

 
会社のパソコンで処理を行ったため、こちらには転記できませんが、
Cドライブの同じフォルダにテーブルのあるExcelファイルを置き、
そこに更新ができるかを検証しました
 
処理はADODB.Connectionで接続し、レコードセットでrs.Updateで行いました。
本日書いたような処理です。
 
    'レコードセットループ
    Do While Not rs.EOF
        rs.Fields(47).Value = "TEST"
        rs.Update
    Loop
 
引用:

何をもって、ローカルではうまくいったと判断したのか?
    データの中身も確認し 書き換えられているのを確認したのでしょうか?
ローカルに対し、うまく行ったコードの 接続先 を SharePointに変え 更新されるのか、されないのか?

 
この部分についてはrs.Updateでエラートラップに落ちずに、テーブルも変更されのでSharepointでの接続に問題があるとこちらは確認しました。
 
引用:

全体フローについて 順番が違っているのであれば
転記ミスなのかどうか、回答者には判断つきません。
フローについては転記ミスが無い様に願います

 
Sharepoitnリストデータ出力時では問題がなかったのに、同じようなフローでも更新時にエラーになってしまうので、フローを見直すといってもどう変更すべきかが判断できませんでした。
色んなサイトにコネクションに接続してコードセットにセットして更新している例が記載されており、忠実に真似しているのに、できないのであればこれ以上変更すべきといっても一体どこを改善すべきか難しいです。
 
ADODBでの処理以外に何か方法があれば教えていただきたいと思っていましたが、多分ないのかなと感じています。
 
色々ご相談いただいてすみませんが、これ以上進めても解決はできないと感じたので解決済みにいたします。
アドバイスありがとうございました。