用ado和sql在vba上实现excel单元格引用

d7v8vwbk  于 2021-07-24  发布在  Java
关注(0)|答案(1)|浏览(410)

下面的代码用于连接到sharepoint列表并删除“代码列”值与“值”匹配的行,我能否将下面代码中的“值”更改为单元格引用,如[a1]?怎样?
视频代码源:https://www.youtube.com/watch?v=uwrvldfaapq&list=plo0amptfifdrcpiwbqjgb3qt3rkomjdbn&index=5

  1. Sub allTst_SharePoint()
  2. Dim mySQL As String
  3. Dim cnt As ADODB.Connection
  4. Dim rst As ADODB.Recordset
  5. Dim xuser As String
  6. Dim xactivity As String
  7. Dim xtimesince As Date
  8. Set cnt = New ADODB.Connection
  9. Set rst = New ADODB.Recordset
  10. mySQL = "Delete * FROM [mylist] WHERE [Code] = 'VALUE' ;"
  11. With cnt
  12. .ConnectionString = _
  13. "Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=0;RetrieveIds=Yes;DATABASE=mySPsite;LIST={myguid};"
  14. .Open
  15. End With
  16. cnt.Execute mySQL, , adCmdText
  17. If CBool(rst.State And adStateOpen) = True Then rst.Close
  18. Set rst = Nothing
  19. If CBool(cnt.State And adStateOpen) = True Then cnt.Close
  20. Set cnt = Nothing
  21. End Sub
vulvrdjw

vulvrdjw1#

尝试

  1. Dim myValue As String
  2. myValue = Range("a1")
  3. mySQL = "Delete * FROM [mylist] WHERE [Code] = '" & myValue & "' ;"

相关问题