vba oracle通过excel vba的无dsn连接
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19038554/
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
dsn-less connection for oracle through excel vba
提问by lalachka
how do i create a dsn-less connection for an oracle db through excel vba.
如何通过 excel vba 为 oracle db 创建无 dsn 连接。
here's how i'm doing it in access vba (i have functions for the variables in the string, GetOracleDriver and TNSName because each machine can have their own) but in excel i don't see the oracle driver when i tried doing it by hand.
这是我在访问 vba 中的操作方式（我有用于字符串中变量的函数，GetOracleDriver 和 TNSName 因为每台机器都可以有自己的）但是在 excel 中，当我尝试手动执行时我没有看到 oracle 驱动程序.
Dim NewConnect As String
NewConnect = "ODBC;DRIVER={" & GetOracleDriver & "};SERVER=" & TNSName & ".mycompany.com;UID=myuid;PWD=mypwd;DBQ=" & TNSName & ".mycompany.com;"
thank you
谢谢你
回答by lalachka
i apologize, this is another question i posted and then found the answer to
我道歉，这是我发布的另一个问题，然后找到了答案
the code below was taken from http://www.vbaexpress.com/forum/showthread.php?26968-How-to-extract-data-from-Oracle-Database-into-Excel-Spreadsheet-via-VBA-code&p=186731&viewfull=1#post186731
and modified a little
并稍作修改
Public Sub ImportData()
Const strSQL_c As String = "SELECT * from NR_CF_CF3_DIR"
Dim strConnection As String
Dim strDBPath As String
strConnection = "ODBC;DRIVER={Oracle in Oracle1};SERVER=mytnsname.mycompany.com;UID=myuid;PWD=mypwd;DBQ=mydbname.mycompany.com"
ActiveWorkbook.Worksheets.Add
ActiveSheet.Name = "QCF3_DIR"
QueryDB strConnection, ActiveSheet.Cells(1, 1), strSQL_c
End Sub
Public Sub QueryDB(ByVal connectionString As String, ByVal target As Excel.Range, ByVal SQL As String)
Dim qt As Excel.QueryTable
Dim ws As Excel.Worksheet
Set ws = target.Parent
Set qt = ws.QueryTables.Add(connectionString, target, SQL)
qt.Refresh BackgroundQuery:=False
End Sub
