vba 在 Excel 宏中运行 SQL 脚本
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11652877/
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
Running SQL Script in Excel Macro
提问by Frankie C
I have a question regarding Macros in Excel and how to use them. I recently created a SQL Query that will hit several TFS databases to extract data. I would simply like to run this script from excel such that the output of the script is automatically published in excel with the individual column headers. I have seen this done before in excel by using VB Macros.
我有一个关于 Excel 中的宏以及如何使用它们的问题。我最近创建了一个 SQL 查询，它将访问多个 TFS 数据库以提取数据。我只想从 excel 运行这个脚本，这样脚本的输出就会自动发布在 excel 中，并带有各个列标题。我以前在 excel 中使用 VB 宏看到过这个。
As a workaround, I created a small bat file to run sqlcmd, check a sql file containing the script, and finally push the output to a csv file. The only problem with this, is that the CSV's output pushes the results into a single line and does not separate them with headers.
作为一种解决方法，我创建了一个小的 bat 文件来运行 sqlcmd，检查包含脚本的 sql 文件，最后将输出推送到一个 csv 文件。唯一的问题是 CSV 的输出将结果推送到一行中，并且没有将它们与标题分开。
Current code for batfile:
batfile 的当前代码：
sqlcmd -E -S TFSServer\TFS2010 -i C:\test\testquery.sql -o C:\test\test.csv
sqlcmd -E -S TFSServer\TFS2010 -i C:\test\testquery.sql -o C:\test\test.csv
回答by Kevin
Why not use an ssis package or ssrs report for dataextraction? Macros are so... last century.
为什么不使用 ssis 包或 ssrs 报告进行数据提取？宏是如此......上个世纪。
If you insist on using a macro, here is an example:
如果你坚持使用宏，这里是一个例子：
Dim con As ADODB.Connection, _
cmd As ADODB.Command, _
rs As ADODB.Recordset, _
strSQL As String, _
i As Integer
strSQL = "EXEC SP_StoredProcedureToGetData"
Set con = New ADODB.Connection
con.Open "Provider=SQLOLEDB;Data Source=SQLSERVER;" & _
"Initial Catalog=DATABASE;User ID=USERNAME;Password=PASSWORD"
Set cmd = New ADODB.Command
With cmd
.ActiveConnection = con
.CommandText = strSQL
.CommandType = adCmdStoredProc
End With
Set rs = New ADODB.Recordset
With rs
.CursorLocation = adUseClient
.CursorType = adOpenKeyset
.LockType = adLockBatchOptimistic
End With
Set rs = cmd.Execute
Cells(1, 1) = "Column1"
Cells(1, 2) = "Column2"
Cells(1, 3) = "Column3"
Cells(1, 4) = "Column4"
Cells(1, 5) = "Column5"
Cells(2, 1).CopyFromRecordset rs
Set con = Nothing
Set cmd = Nothing
Set rs = Nothing
