SQL query in VBA, code stored in several cells in

Question

I'm trying to write VBA code that allows user input (e.g change date) in Excel before retrieving data through SQL. The code works fine as long as I store the SQL query in a single cell. However, the SQL query includes 62 lines, so editing it inside a single cell is not optimal.

I would like to split up the SQL code to 1 line per row in Excel, allowing for easier editing. The SQL code is currently stored in the "SQL" sheet, in cell "A1".

Any ideas how to make this work with multiple rows/cells?

Sub ConnectSqlServer()

Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sConnString As String

' Create the connection string.
sConnString = "Provider=OraOLEDB.Oracle;Data Source=xxxxxxx;User Id=xxxxxxx;Password=xxxxxxxxx;"

' Create the Connection and Recordset objects.
Set conn = New ADODB.Connection
Set rs = New ADODB.Recordset

' Open the connection and execute.
conn.Open sConnString
Set rs = conn.Execute(Sheets("SQL").Range("A1"))

For intColIndex = 0 To rs.Fields.Count - 1
Sheets("Output").Range("A1").Offset(0, intColIndex).Value = rs.Fields(intColIndex).Name
Next

' Check we have data.
If Not rs.EOF Then
    ' Transfer result.
    Sheets("Output").Range("A2").CopyFromRecordset rs
' Close the recordset
    rs.Close
Else
    MsgBox "Error: No records returned.", vbCritical
End If

' Clean up
If CBool(conn.State And adStateOpen) Then conn.Close
Set conn = Nothing
Set rs = Nothing

End Sub

Answer 1

If you want to select only the non blank lines of the first row where you have your SQL query you could use something like this.

This has the benefit that you won't need to change the code once you add a line or two to your SQL query.

Public Function FirstRowAsString() As String

    Dim variantValues() As Variant
    Dim stringValue As String
    Dim rowCounter As Long

    range("A1").Select
    range(Selection, Selection.End(xlDown)).Select

    variantValues = Selection.Value

    For rowCounter = 1 To UBound(variantValues, 1) Step 1
        stringValue = stringValue + " " + CStr(variantValues(rowCounter, 1))
    Next rowCounter

    FirstRowAsString = stringValue

End Function

Answer 2

 Dim SqlText as String
 Dim r as range
 For each r in Range("A1:A62")
      SqlText = SqlText & r.Text
  Next r
  Set rs = conn.Execute(sqlText)

Just be careful to start each line of text from A2 to A62 with a space to ensure the lines remain seperate when concatenated