从 VBA 运行 SQL 文件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27043116/
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 file from VBA
提问by user1087906
I'm currently trying to figure out how to run a .sql file from VBA. I can get the VBA to run sql code inside it, but as I have the SQL script already wrote, I'd like to just be able to call the file, run the code and pass the data back to Excel. Even if it's calling the file and passing the code back to VBA to run, that'd be fine, but it's just easier to maintain the code there instead of in the VBA editor.
我目前正在尝试弄清楚如何从 VBA 运行 .sql 文件。我可以让 VBA 在其中运行 sql 代码，但由于我已经编写了 SQL 脚本，我希望能够调用文件、运行代码并将数据传回 Excel。即使它正在调用文件并将代码传递回 VBA 以运行，那也没关系，但是在那里而不是在 VBA 编辑器中维护代码更容易。
Database is Oracle, but I've got the connection running and working atm.
数据库是 Oracle，但我的连接正在运行并在 atm 中工作。
' Makes the SQL call to collect the data.
Dim strConOracle As String
Dim oConOracle, oRsOracle
Dim strSQL As String
Dim lngCount As Long
Dim strCount As String
strConOracle = "Driver={Microsoft ODBC for Oracle}; CONNECTSTRING=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)"
strConOracle = strConOracle & "(HOST=" & HOST & ")(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=" & DBNAME
strConOracle = strConOracle & "))); uid=" & ORACLE_USER_NAME & " ;pwd=" & ORACLE_PASSWORD & ";"
Set oConOracle = CreateObject("ADODB.Connection")
Set oRsOracle = CreateObject("ADODB.RecordSet")
oConOracle.Open strConOracle
strSQL = ""
Set oRsOracle = oConOracle.Execute(strSQL)
ActiveSheet.Cells.CopyFromRecordset oRsOracle
oConOracle.Close
Set oRsOracle = Nothing
Set oConOracle = Nothing
That's the code that I have atm, minus the database connection which is declared at the top.
那是我有 atm 的代码，减去在顶部声明的数据库连接。
Ideally, I'd just be changing the strSQL part.
理想情况下，我只是更改 strSQL 部分。
Thanks for any help
谢谢你的帮助
回答by cboden
something like that will do the job for you:
这样的事情会为你做的工作：
Dim strSQL As String
strSQL = ""
Dim hnd As Integer
hnd = FreeFile
Open "C:\Temp\yourScript.sql" For Input As hnd
Dim row As String
Do Until EOF(hnd)
Line Input #hnd, row
strSQL = strSQL & row & vbNewLine
Loop
Close #hnd
