Select Data in SQL Table keyed to Excel Range using VBA

[Mit-Eknil]

I am trying to select data in an SQL Table that matches a list (or Table of part numbers I have in Excel 2007. The connection to the SQL database works but gives me the database field heading with no result. I can get a good result if I just use the SELECT query to be equal to a variable set to the value of a single cell.
Sub PNinfo()

Dim sqlstring As String​

Dim connstring As String​

Dim PNList As TableStyle
Dim jbqt As QueryTable

​

sqlstring = "SELECT dbo.Quote.Part_Number, dbo.Quote.RFQ, dbo.Quote.Quote FROM dbo.Quote Where dbo.Quote.Part_Number = '" & ActiveSheet.ListObjects("PNList") & " '"

​

connstring = "ODBC;DSN=database32;UID=User1;PWD=password;DATABASE=PRODUCTION"

​

Set jbqt = ActiveSheet.QueryTables.Add(Connection:=connstring, Destination:=Cells(7, 4), Sql:=sqlstring)

​

jbqt.Refresh

​

End Sub

It may be better to set this up as a Join between the SQL table and the Excel list (Range or List) but I think the main issue is, what is the syntax to get the passed through SQL statement to use the data in Excel.

Thanks for any suggestions

 

[ChrisM]

SQL is a string input, so your Excel data must be in string format. You are trying to pass a ListObject (which is an object) into a field expecting a string. You need to prep your Excel data ahead of time, perhaps by looping through the listobject, and reading all those values into a comma delimited string. If Part_Number is a text/string field in your database you will also need to wrap your parts with double quotes, ie "1234".

You also need to change your SQL from:
Part_Number = xxx

to

Part_Number In (your_excel_comma_delimited_string) , such as Part_Number In ("1234", "5678", "7890")

 

[Mit-Eknil]

Thanks ChrisM. Your suggestion worked fine but I had to change the " to ' and it took some time to figure out how to get the ' in the right place. Finally I just trimmed the ends of the string. Here is the final code for any one interested. This reads down a list of part numbers in column B and if there is a 1 in column A next to it, that text (part number) is included in the select string.

Sub PNCheck2()

Dim sqlstring As String
Dim connstring As String
Dim jbqt As QueryTable
Dim pnroot As String
Dim Pnrow As Integer

pnroot = "'"

For Pnrow = 7 To 106
If Cells(Pnrow, 1) = 1 Then
pnroot = pnroot & Cells(Pnrow, 2) & Chr(39) & ", " & Chr(39)
End If
Next Pnrow

pnroot = Left(pnroot, Len(pnroot) - 4)
pnroot = Right(pnroot, Len(pnroot) - 1)

Cells(1, 4) = pnroot

sqlstring = "SELECT dbo.Quote.Part_Number, dbo.Quote.RFQ, dbo.Quote.Quote FROM dbo.Quote Where dbo.Quote.Part_Number In ( '" & pnroot & "')"

connstring = "ODBC;DSN=database32;UID=User1;PWD=password;DATABASE=PRODUCTION"

Set jbqt = ActiveSheet.QueryTables.Add(Connection:=connstring, Destination:=Cells(7, 5), Sql:=sqlstring)

jbqt.Refresh

End Sub