如何将长而复杂的sql查询嵌入Excel代码中？
当我必须嵌入一个简单的查询(例如Select * from table1)时，下面的VBA代码可以成功地将数据从SQL数据库获取到Excel工作表，但是这段代码无法工作，并且显示了
编译错误:用户定义类型未定义.
当我有复杂而冗长的查询时：
Sub ConnectSqlServer()
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sConnString As String
' Create the connection string.
sConnString = "Provider=SQLOLEDB;Data Source=vrsqladhoc;" & _
"Initial Catalog=TACT_REV;" & _
"Integrated Security=SSPI;"
' 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("select distinct column1 from table1;")
' Check we have data.
If Not rs.EOF Then
' Transfer result.
Sheets(2).Range("A2").CopyFromRecordset rs
' Close the recordset
rs.Close
Else
MsgBox "Error: No records returned.", vbCritical
End If
End Sub我打算在VBA代码中嵌入的长查询如下所示：
SELECT Count(column1) as status_count, case when
missing_flag = 1 then ' Missing' else ' Available' end as status,'Name' as Document_Type
FROM table1 cm
inner join table2 md on md.column1 = cm.column2
WHERE cm.column3 = 'R'
and cm.column4 = 3
group by case when
missing_flag1 = 1 then 'Missing' else 'Available' end
Union All
SELECT Count(column1) as status_count, case when
missing_flag = 1 then ' Missing' else ' Available' end as status,'Name' as Document_Type
FROM table2 cm
inner join table2 md on md.column1 = cm.column2
WHERE cm.column3 = 'R'
and cm.column4 = 3
group by case when
missing_flag1 = 1 then 'Missing' else 'Available' end
Union All
SELECT Count(column1) as status_count, case when
missing_flag = 1 then ' Missing' else ' Available' end as status,'Name' as Document_Type
FROM table3 cm
inner join table2 md on md.column1 = cm.column2
WHERE cm.column3 = 'R'
and cm.column4 = 3
group by case when
missing_flag1 = 1 then 'Missing' else 'Available' end
Union All
SELECT Count(column1) as status_count, case when
missing_flag = 1 then ' Missing' else ' Available' end as status,'Name' as Document_Type
FROM table4 cm
inner join table2 md on md.column1 = cm.column2
WHERE cm.column3 = 'R'
and cm.column4 = 3
group by case when
missing_flag1 = 1 then 'Missing' else 'Available' end
Union All
SELECT Count(column1) as status_count, case when
missing_flag = 1 then ' Missing' else ' Available' end as status,'Name' as Document_Type
FROM table5 cm
inner join table2 md on md.column1 = cm.column2
WHERE cm.column3 = 'R'
and cm.column4 = 3
group by case when
missing_flag1 = 1 then 'Missing' else 'Available' end
Union All
SELECT Count(column1) as status_count, case when
missing_flag = 1 then ' Missing' else ' Available' end as status,'Name' as Document_Type
FROM table6 cm
inner join table2 md on md.column1 = cm.column2
WHERE cm.column3 = 'R'
and cm.column4 = 3
group by case when
missing_flag1 = 1 then 'Missing' else 'Available' end
Union All
SELECT Count(column1) as status_count, case when
missing_flag = 1 then ' Missing' else ' Available' end as status,'Name' as Document_Type
FROM table7 cm
inner join table2 md on md.column1 = cm.column2
WHERE cm.column3 = 'R'
and cm.column4 = 3
group by case when
missing_flag1 = 1 then 'Missing' else 'Available' end
Union All
SELECT Count(column1) as status_count, case when
missing_flag = 1 then ' Missing' else ' Available' end as status,'Name' as Document_Type
FROM table8 cm
inner join table2 md on md.column1 = cm.column2
WHERE cm.column3 = 'R'
and cm.column4 = 3
group by case when
missing_flag1 = 1 then 'Missing' else 'Available' end请让我知道如何将长SQL查询嵌入到VBA代码中，而不会出现任何错误。
回答 7
Stack Overflow用户
发布于 2018-01-04 17:46:26
要将SQL压缩到VBA中，请执行以下操作：
Dim sql As String
sql = "SELECT Count(column1) as status_count, case when " & _
"missing_flag = 1 then ' Missing' else ' Available' end as status,'Name' as Document_Type" & _
"FROM table1 cm " & _
"inner join table2 md on md.column1 = cm.column2 " & _
"WHERE cm.column3 = 'R' " & _
"and cm.column4 = 3 " & _
"group by case when " & _
"missing_flag1 = 1 then 'Missing' else 'Available' end "
sql = sql & " UNION ALL "
sql = sql & " keep going like this ..."如果使用简短的SQL和易于阅读的语句，您可以在一条语句中这样做，但是使用long语句，您将在VBA中遇到延拓行(_)问题。这就是为什么我把它分解成sql的几个设置。
这是蛮力和不理想的解决方案，想恶梦去调试和维护！
最好是以存储过程的形式直接存储在db中，并让excel vba调用它。
Stack Overflow用户
发布于 2018-01-04 17:52:59
具体是如何构建连接字符串：
sConnString = "Provider=SQLOLEDB;Data Source=vrsqladhoc;" & \_ "Initial Catalog=TACT\_REV;" & \_ "Integrated Security=SSPI;"
通过连接字符串文本。
现在，在这么长的查询中遇到的一个问题是，VBA不会让您生成跨越10-15行物理代码的逻辑代码行。请注意，上面的语句是一条逻辑代码行，但它跨越3行物理行，其中包含2行连续标记。
所以你就这么做：
Dim sql As String
sql = "SELECT Count(column1) as status_count, case when " & _
" ..." & _
" ..." & _
" ..." & _
"and so on"当编译器开始抱怨有太多的行延续时，您只需将其与自身连接起来：
sql = sql & " rest of the " & _
" ...query"显然这会变得非常丑陋，非常快。修复该查询中的错误将是一场噩梦。维护这个查询将是一场噩梦。只要看看这个问题就会让你做恶梦。
不管语言如何，这样复杂的查询不属于源代码。它们属于服务器、视图或存储过程。如果无法在数据库上创建视图或存储过程，请将查询提供给能够创建视图或存储过程的人。
如果绝对没有办法避免这种情况，那么我建议您使用StringBuilder来进行字符串连接。结果至少要比混乱的交错线-连续级联干净得多，而且效果会更好。
Dim sql As String
With New StringBuilder
.Append "SELECT ..."
.Append "..."
.Append "..."
.Append "..."
.Append "..."
.Append "..."
sql = .ToString
End With附带注意:无论您做什么，从不将用户输入连接到查询中。把它参数化。
Stack Overflow用户
发布于 2019-07-24 09:58:35
我倾向于编写我的SQL查询，以便嵌入到Notepad++中的Excel/VBA中，然后将它们保存为一个简单的.sql (文本)文件。我有一个脚本，它使用.fso (FileSystemObject)读取文件的内容，并使用它来形成查询(到我的DB的连接都是锅炉板)。
当一个文件准备好部署到业务时，我将整个SQL查询复制到工作表上的TextBox中，然后可以通过以下方法引用：
strSQL = ThisWorkbook.Sheets("SQL_Query").TextBoxes("SQL_Text").Text这样做的好处是，Notepad++的所有缩进都保持不变。
最后阶段是确保工作表的可见属性设置为.xlSheetVeryHidden，当然，代码是密码保护的。
https://stackoverflow.com/questions/48100470
复制相似问题
腾讯云开发者
