在excel vba中使用sql中的数组或字典作为from子句 [英] Using an array or dictionary as from clause in sql in excel vba
本文介绍了在excel vba中使用sql中的数组或字典作为from子句的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
可以使用数组或字典作为SQL语句中的表。
Is it possible to use an array or dictionary as the table in a SQL statement.
strSQL =SELECT * FROM& myArray&
e.g. strSQL = "SELECT * FROM " & myArray & ""
提前感谢
推荐答案
由@Nathan_Sav提供以下代码:
Expanding upon the idea provided by @Nathan_Sav the following code should to it:
Dim a(3) As String
a(0) = "1 as C1"
a(1) = 2
a(2) = 3
a(3) = 4
Debug.Print "select * from (SELECT " & Join(a, " UNION ALL SELECT ") & ") as tmp"
< h2>更新:
这是一个短子手动连接/构造必需的字符串:
Update:
Here is a short sub to manually concatenate / construct the necessary string:
Option Explicit
Sub tmpTest()
Dim strSQL As String
Dim varArray As Variant
Dim lngRow As Long, lngColumn As Long
varArray = ThisWorkbook.Worksheets(1).Range("A1:G12")
strSQL = "select * from "
strSQL = strSQL & "(select "
For lngRow = LBound(varArray, 1) + 1 To UBound(varArray, 1)
For lngColumn = LBound(varArray, 2) To UBound(varArray, 2)
'First row must contain column names
If lngRow = LBound(varArray, 1) + 1 Then
strSQL = strSQL & varArray(lngRow, lngColumn) & _
" as [" & varArray(lngRow - 1, lngColumn) & "], "
Else
strSQL = strSQL & varArray(lngRow, lngColumn) & ", "
End If
'Beyond the last column a UNION ALL SELECT must be added
' except for the last row
If lngColumn = UBound(varArray, 2) And _
lngRow < UBound(varArray, 1) Then
strSQL = Mid(strSQL, 1, Len(strSQL) - 2) & _
" union all select "
End If
Next lngColumn
Next lngRow
strSQL = Mid(strSQL, 1, Len(strSQL) - 2) & ") as tmp"
Debug.Print strSQL
End Sub
上述代码假定数据在sheet1上在 A1:G12
的范围内,第一行包含列标题。
The above code assumes the data to be on sheet1 in the range A1:G12
where the first row contains the columns headers.
这篇关于在excel vba中使用sql中的数组或字典作为from子句的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文