Cell Reference on VBA using ADO and SQL

Question

The code below is used to connect to a sharepoint list and delete rows WHERE "Code Column" values match 'VALUE', can i change 'VALUE' in the code bellow to a cell reference, like [A1]? how?

Video Code Source: https://www.youtube.com/watch?v=UWrVLdFaapQ&list=PLo0aMPtFIFDrcPiWbqJGb3qt3rkOmjDbN&index=5

Sub allTst_SharePoint()


Dim mySQL As String
Dim cnt As ADODB.Connection
Dim rst As ADODB.Recordset
Dim xuser As String
Dim xactivity As String
Dim xtimesince As Date


Set cnt = New ADODB.Connection
Set rst = New ADODB.Recordset


mySQL = "Delete * FROM [mylist] WHERE [Code] = 'VALUE' ;" 


With cnt
    .ConnectionString = _
    "Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=0;RetrieveIds=Yes;DATABASE=mySPsite;LIST={myguid};"
    .Open
End With

cnt.Execute mySQL, , adCmdText
    
If CBool(rst.State And adStateOpen) = True Then rst.Close
Set rst = Nothing
If CBool(cnt.State And adStateOpen) = True Then cnt.Close
Set cnt = Nothing

End Sub

Dy.Lee · Accepted Answer · 2020-07-11 15:23:04Z

1

Try

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

answered Jul 11, 2020 at 15:23

Dy.Lee

7,5471 gold badge14 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Denis Araujo Over a year ago

Perfect! You are awsome, thank you, have a good weekend.

Brian M Stafford Over a year ago

@DenisAraujo If an answer has solved your question please consider accepting it by clicking the check-mark. This indicates to the wider community that you've found a solution. There is no obligation to do this.

Collectives™ on Stack Overflow

Cell Reference on VBA using ADO and SQL

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related