SQL 使用vba将sql数据导入excel
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13425642/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
import sql data into excel using vba
提问by user1681610
I'm trying to pull data from SQL table into excel. I've recorded a macro doing so using the data source tool. However the amount of data I'm pulling generally crashes excel. Is there a way to add a variable in my vba script to limit the data pulled from the sql table? Essentially adding a where clause to a select statement in sql.
我正在尝试将 SQL 表中的数据提取到 excel 中。我已经使用数据源工具录制了一个宏。但是，我提取的数据量通常会导致 excel 崩溃。有没有办法在我的 vba 脚本中添加一个变量来限制从 sql 表中提取的数据？本质上是在 sql 中的 select 语句中添加一个 where 子句。
Thanks!
谢谢！
-Sean
-肖恩
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
"OLEDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Data Source=10.22.30.215;Use Procedure for Prepare=1;Aut" _
, _
"o Translate=True;Packet Size=4096;Workstation ID="FakeName";Use Encryption for Data=False;Tag with column collation when possible=Fa" _
, "lse;Initial Catalog=FakeCatelog"), Destination:=Range("$A")). _
QueryTable
.CommandType = xlCmdTable
.CommandText = Array( _
"""FakeName""")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.SourceConnectionFile = _
"C:\UFakeFilePathodc"
.ListObject.DisplayName = "FakeName"
.Refresh BackgroundQuery:=False
End With
End Sub`
回答by Kevin Pope
Try connecting to the database and doing a query instead of trying to pull the entire database into your sheet. This should get you started:
Make sure to add the ""Microsoft ActiveX Data Objects 6.0 Library" Reference or run this line once:ActiveWorkbook.VBProject.References.AddFromGuid "{B691E011-1797-432E-907A-4D8C69339129}", 6, 0
尝试连接到数据库并执行查询，而不是尝试将整个数据库拉入您的工作表中。这应该让您开始：
确保添加“Microsoft ActiveX Data Objects 6.0 Library”参考或运行此行一次：ActiveWorkbook.VBProject.References.AddFromGuid "{B691E011-1797-432E-907A-4D8C69339129}", 6, 0
Sub QueryDB()
Dim dbName As ADODB.Connection
Dim dbResults As ADODB.Recordset
Set dbName = openDBConn("YOURDATABASE", "YourTable")
Set dbResults = dbName.Execute("SELECT * FROM YOURDATABASE")
While Not dbResults.EOF
'Do Something'
dbResults.MoveNext
Wend
End Sub
Function openDBConn(dataSource As String, table As String) As ADODB.Connection
Dim newDBConn As ADODB.Connection
Set newDBConn = New ADODB.Connection
newDBConn.CommandTimeout = 60
Dim strConn As String
strConn = "PROVIDER=SQLOLEDB;DATA SOURCE=" & dataSource & ";INITIAL CATALOG=" & table & ";INTEGRATED SECURITY=SSPI"
newDBConn.Open strConn
Set openDBConn = newDBConn
End Function
回答by whytheq
Once you are happy with the code it might be worth switching it to late binding. Early bindingis good as you get full intellisense when developing the application but I find late binding to be a little less troublesome as time goes by and applications get upgraded to new versions.
一旦您对代码感到满意，可能值得将其切换到late binding. Early binding很好，因为您在开发应用程序时可以获得完整的智能感知，但我发现随着时间的推移和应用程序升级到新版本，后期绑定会变得不那么麻烦。
Also as I use the connection strings quite a lot it can be good to have it decalred at the top of your module ...saves digging around for this hard code in the future:
此外，由于我经常使用连接字符串，因此将它贴在模块的顶部会很好……将来可以节省为此硬代码的挖掘工作：
(p.s. this is just Kevin's code with a couple of changes; not necessarily improvements but more just alternatives)
（ps 这只是 Kevin 的代码，有一些更改；不一定是改进，而只是替代方案）
Global Const strConn As String = "PROVIDER=SQLOLEDB;DATA SOURCE=" & dataSource & ";INITIAL CATALOG=" & table & ";INTEGRATED SECURITY=SSPI"
Sub QueryDB()
Dim dbName As Object
Dim dbResults As Object
Set dbName = CreateObject("ADODB.Connection")
dbName = openDBConn("YOURDATABASE", "YourTable")
Set dbResults = CreateObject("ADODB.Recordset")
dbResults = dbName.Execute("SELECT * FROM YOURDATABASE")
While Not dbResults.EOF
'Do Something'
dbResults.MoveNext
Wend
End Sub
Function openDBConn(dataSource As String, table As String) As ADODB.Connection
Dim newDBConn As Object
Set newDBConn = CreateObject("ADODB.Connection")
newDBConn.CommandTimeout = 60
newDBConn.Open strConn
Set openDBConn = newDBConn
End Function
