How to pick the values from a cell range into WHERE Clause?

[Praneeth30]

I am working on database querying, and heres is what i need


sSQL = "SELECT * from ABCD.EFGH_IJK where LMN in values from [A1-A10];"


Can someone help me on how to get those values? and also i need the values to be placed in single quotes separated by a comma.

 

[Colin Legg]

Since the values come from a single column, you can transpose them to a 1-D array and then use the VBA.Strings.Join() method to create a string from them. Here's a crude outline:

[pre]

Sub foo()

Dim sSql As String

sSql = "SELECT * from ABCD.EFGH_IJK where LMN IN " & ColumnToSqlList(Range("A1:A10"))

Debug.Print sSql

End Sub

Function ColumnToSqlList(ByRef rngColumn As Range) As String

Const sDELIMITER As String = "','"
Dim vList As Variant

vList = Application.Transpose(rngColumn.Value) 'make 1D array

ColumnToSqlList = "('" & Join(vList, sDELIMITER) & "')"

End Function

[/pre]

 

[Praneeth30]

Thanks Colin, It worked