Using Excel to Build SQL Where Clause

Thought this might be useful.

I often have a list of information sent in excel and need to write a sql statement to get more info on the data from a sql or access DB. I built this function and use it as an add in. It takes a range of excel values and builds an "In" statement that can be used in a where a clause.

Code is below...

Here is some commented code. I;ve added another function in here that I have equally useful.

When I say add in I mean a VBA add in - although the code should work in VB if the Excel reference is set in the project. I have not tried it out in VB though , so I can't promise anything.

SQL_Where is useful when you have a list of items you want include in a where clause. Most recenlty I was sent a list of Security Cusips I and needed to query for data on them. These cusips came in a range of cells in Excel.

Insert Script is useful when you use excel to create records for a sql table. Since you cannot cut and paste the records from Excel , this function will create a SQL cammand to insert the values from Query Analyzer.

Option Explicit
Public Function SQL_WHERE(ByRef ViewRange As Excel.Range, _
Optional ByRef StringIdentifier As String = "'", _
Optional ByRef DateIdentifier As String = "'") As String


'StringIdentifier - sets the character to be placed around string values
'DateIdentifier - sets the character to be placed around date values ex. use # for Access , '' for SQL


Dim r As Excel.Range, i As Integer
ReDim a_values(0) As Variant 'To store the values of the non_empty cells in the range
Dim sBuffer As String 'To store the text of our "in clause"
Dim bAllNumeric As Boolean
Dim sTypeChar As String

bAllNumeric = True 'As we loop through the cells we need to know if ALL values are numeric

'loop through all cells in the input range to determine the data type and remove blank values

For Each r In ViewRange
If r.Value <> "" Then 'ignore blanks
ReDim Preserve a_values(UBound(a_values) + 1)
a_values(UBound(a_values)) = r.Value
If Not IsNumeric(r.Value) Then bAllNumeric = False 'If any value is not numeric we will assume that the field is a string
End If
Next

'loop through all cells in the input range and contstruct the "in" string

For i = 1 To UBound(a_values)
Select Case True
Case IsNumeric(a_values(i)): sTypeChar = ""
Case IsDate(a_values(i)): sTypeChar = DateIdentifier
Case Else: sTypeChar = StringIdentifier
End Select
sBuffer = sBuffer & sTypeChar & a_values(i) & sTypeChar
If Not i = UBound(a_values) Then sBuffer = sBuffer & ", "
Next i

SQL_WHERE = " IN (" & sBuffer & ")"

Erase a_values

End Function


Function InsertScript(Tbl As String, Rg1 As Range, Rg2 As Range) As String
' Takes as input TBL - the name of the SQL table to be insert into
' Rg1, the range of cells that contains the column names in TBL
' Rg2, the range of values to insert

'The idea here is to alow the user to quickly a create a series of SQL commands to insert data into a SQL table
'This is useful because SQL does not support clipboard pasting from Excel or Access

Dim c1 As Range
Dim str As String

InsertScript = "Insert into [" & Tbl & " ] ("

'Loop trough Rg1 and create columns part of the script

For Each c1 In Rg1
InsertScript = InsertScript & "[" & str & c1.Value & str & "],"
Next

InsertScript = Left(InsertScript, Len(InsertScript) - 1)
InsertScript = InsertScript & ") Values("

'Loop trough Rg2 and create values part of the script

For Each c1 In Rg2
If Not IsNumeric(c1.Value) Then 'Note that the asumption here is for SQL - using ' for char around dates
str = "'"
Else
If (IsDate(c1.Value) And c1.Value > 30000) Then str = "'" Else str = Empty
End If
InsertScript = InsertScript & str & c1.Value & str & ","
Next

InsertScript = Left(InsertScript, Len(InsertScript) - 1) & ")"

End Function