VBA中的SQL查询，代码存储在Excel中的多个单元格中 [英] SQL query in VBA, code stored in several cells in Excel

问题描述

我正在尝试编写VBA代码，以便在通过SQL检索数据之前允许用户在Excel中输入(例如更改日期).只要我将SQL查询存储在单个单元格中，代码就可以正常工作.但是，SQL查询包含62行，因此在单个单元格内对其进行编辑并不是最佳选择.

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.

我想在Excel中将SQL代码拆分为每行1行，以便于编辑.该SQL代码当前存储在"SQL"表中的单元格"A1"中.

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

推荐答案

 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)

请注意，从A2到A62的每行文本都必须以空格开头，以确保在连接时各行保持分隔状态

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

这篇关于VBA中的SQL查询，代码存储在Excel中的多个单元格中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！