VBA throws an error when picking up cell values into SQL query

I get the following error when picking up values for my SQL from certain cells. However, when i hard code the values into the code, it works fine. so, it’s clearly something to do with the syntax of how i have written it.

I am looking to make this dynamic for several reasons and hoping someone can help me.

The error: The Microsoft Access Database Engine could not find the object “objectname”

Working code where the values are hard-coded:

mySQL = "SELECT * FROM [listname];"
With cnt
.ConnectionString = _
"Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=0;RetrieveIds=Yes;DATABASE=https://sharepoint.com/sites1/xx/;LIST= {0C3G7BTF-000E-4C16-8E07-E4B7F525069F};"
.Open
End With

Code that does not work:

mySQL = "SELECT * FROM ['" & Sheet3.Range("c1") & "'];"
With cnt
.ConnectionString = _
"Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=0;RetrieveIds=Yes;DATABASE=https://sharepoint.com/sites1/xx/;LIST= {'" & Sheet3.Range("d1") & "'};"
.Open
End With

Answer

I would advise putting your connection string value in a variable, and printing it (eg. debug.print myConnectionString) to perform a character per character comparison.

Also, you have single quotes surrounding your reference to sheet.range(“d1”)

"Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=0;RetrieveIds=Yes;DATABASE=https://sharepoint.com/sites1/xx/;LIST= {'" & Sheet3.Range("d1") & "'};"

Maybe those are the cause of your issue. You appear to have the same problem in the mySQL variable assignation.

Edit :

Maybe this could work :

mySQL = "SELECT * FROM [" & Sheet3.Range("c1") & "];"
myConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=0;RetrieveIds=Yes;DATABASE=https://sharepoint.com/sites1/xx/;LIST= {" & Sheet3.Range("d1") & "};"
debug.print myConnectionString
'Open execution window (ctrl g) to get the value of myConnectionString

With cnt
.ConnectionString = myConnectionString
.Open
End With