在 Excel 中使用 VBA 从 .sql 文件运行 SQL Select 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10537382/
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
Run SQL Select query from .sql file using VBA in Excel
提问by tony_g
To create dynamic select statements I am currently creating a temp file to hold the query (30k + characters due to number of joins and decodes being used). I'd like to use this temp file that has the full select statement to return the data into excel.
为了创建动态选择语句，我目前正在创建一个临时文件来保存查询（由于使用了连接和解码的数量，因此需要 30k + 个字符）。我想使用这个具有完整 select 语句的临时文件将数据返回到 excel 中。
Using my current code I can only run a short select statements. The strings I create seem to get truncated for some reason.
使用我当前的代码，我只能运行一个简短的选择语句。由于某种原因，我创建的字符串似乎被截断了。
Here's the snippet of code I'm using that creates the file and currently attempts to run the same string.
这是我正在使用的代码片段，用于创建文件并当前尝试运行相同的字符串。
' Set file details
fileDir = "C:\temp\"
filePath = "C:\temp\" & node & "_SRO_TCs.sql"
'check if directory exists, if not create it
If Dir(fileDir, cbDirectory) = "" Then
MkDir fileDir
End If
' open the file
Open filePath For Output As #1
'Write to file
outputText = sqlQuery3
Print #1, outputText
'open connection
sqlCon.ConnectionString = Conn
'Cn.CursorLocation = adUseClient
sqlCon.Open
'set and execute sql command
Set sqlCommand.ActiveConnection = sqlCon
sqlCommand.CommandText = sqlQuery3
sqlCommand.CommandType = adCmdText
sqlCommand.Execute
'open recordset
Set sqlRecordSet.ActiveConnection = sqlCon
sqlRecordSet.Open sqlCommand
'copy data to excel
ActiveSheet.Range("A1").CopyFromRecordset (sqlRecordSet) <<<< This is where i get an error returned when stepping through the code - "Run-time error '91': Object variable or With block variable not set"
'close connections
sqlRecordSet.Close
sqlCon.Close
'Close file
Close #1
When I check the file created it has a working sql select statement. I'd like to be able to run this file or the string. Please help!
当我检查创建的文件时，它有一个有效的 sql select 语句。我希望能够运行这个文件或字符串。请帮忙！
回答by Tim Williams
The Command Execute method looks like this
命令执行方法如下所示
Set recordset = command.Execute( RecordsAffected, Parameters, Options )
Your code is quite different. Hadn't looked too hard at your code before, because you said it worked with a shorter SQL statement.
你的代码完全不同。之前没有仔细研究过您的代码，因为您说它可以使用较短的 SQL 语句。
