SQL query in VBA, code stored in several cells in Excel

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

Rather than having an entire query in the worksheet, it is more common to put the query in VBA or the connection, and retrieve values from the worksheet. — Degan, Jul 19 '17 at 14:55

score 0 · Accepted Answer · answered Jul 19 '17 at 14:48

0

 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

answered Jul 19 '17 at 14:48

Harassed Dad

4,669
1
10
12

1

Or why not add the space in code? SqlText = SqlText & " " & r.Text – Sam Jul 19 '17 at 15:00

Bruno Bieri · Answer 2 · 2017-07-19T15:06:23.857

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

SQL query in VBA, code stored in several cells in Excel

2 Answers2

Linked