使用VBA在Excel中查询SQL Server表 [英] Using VBA to query a SQL Server table in Excel
本文介绍了使用VBA在Excel中查询SQL Server表的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在使用VBA查询Microsoft Excel中的表。我已经写了一些代码来尝试完成这个任务,但是我不断得到一个错误:
运行时错误'1004'说这是一般的ODBC错误。
我不知道我需要做什么才能使此代码正常运行,以便我可以查询此表。
我正在使用SQL Server Express,我正在连接到的服务器: .\SQLEXPRESS
数据库:
查询产品表
VBA代码:
Sub ParameterQueryExample()
'---在Sheet1上创建一个ListObject-QueryTable,它使用
'Cell Z1中的值作为ProductID参数SQL查询
'一旦创建,查询将刷新Z1更改。
Dim sSQL As String
Dim qt As QueryTable
Dim rDest As Range
'--build连接字符串 - 必须使用ODBC才能允许参数
Const sConnect =ODBC; &安培; _
Driver = {SQL Server Native Client 10.0}; &安培; _
Server = .\SQLEXPRESS; &安培; _
Database = TSQL2012; &安培; _
Trusted_Connection = yes
'--build SQL语句
sSQL =SELECT *& _
FROM TSQL2012.Production.Products Products& _
WHERE Products.productid =?;
'--create ListObject并获取QueryTable
设置rDest = Sheets(Sheet1)。范围(A1)
rDest.CurrentRegion.Clear'可选 - 删除现有table
设置qt = rDest.Parent.ListObjects.Add(SourceType:= xlSrcExternal,_
Source:= Array(sConnect),Destination:= rDest).QueryTable
带有qt.Parameters.Add(ProductID,xlParamTypeVarChar)
.SetParam xlRange,Sheets(Sheet1)。范围(Z1)
.RefreshOnChange = True
结束使用
'--populate QueryTable
与qt
.CommandText = sSQL
.CommandType = xlCmdSql
.AdjustColumnWidth = True'添加任何其他表属性这里
.BackgroundQuery = False
.Refresh
结束
设置qt = Nothing
设置rDest = Nothing
End Sub
解决方案
使用OLEDB提供程序为Excel,它应该工作正常。 >
I'm trying to query a table in Microsoft Excel using VBA. I've written up some code to try and accomplish this task, but I keep getting an error:
run-time error '1004': Saying it's a General ODBC error.
I'm not sure what I need to do to get this code to run properly so I can query this table.
I'm using SQL Server Express, the server I'm connecting to: .\SQLEXPRESS
Database:
Querying the products table VBA Code:
Sub ParameterQueryExample()
'---creates a ListObject-QueryTable on Sheet1 that uses the value in
' Cell Z1 as the ProductID Parameter for an SQL Query
' Once created, the query will refresh upon changes to Z1.
Dim sSQL As String
Dim qt As QueryTable
Dim rDest As Range
'--build connection string-must use ODBC to allow parameters
Const sConnect = "ODBC;" & _
"Driver={SQL Server Native Client 10.0};" & _
"Server=.\SQLEXPRESS;" & _
"Database=TSQL2012;" & _
"Trusted_Connection=yes"
'--build SQL statement
sSQL = "SELECT *" & _
" FROM TSQL2012.Production.Products Products" & _
" WHERE Products.productid = ?;"
'--create ListObject and get QueryTable
Set rDest = Sheets("Sheet1").Range("A1")
rDest.CurrentRegion.Clear 'optional- delete existing table
Set qt = rDest.Parent.ListObjects.Add(SourceType:=xlSrcExternal, _
Source:=Array(sConnect), Destination:=rDest).QueryTable
With qt.Parameters.Add("ProductID", xlParamTypeVarChar)
.SetParam xlRange, Sheets("Sheet1").Range("Z1")
.RefreshOnChange = True
End With
'--populate QueryTable
With qt
.CommandText = sSQL
.CommandType = xlCmdSql
.AdjustColumnWidth = True 'add any other table properties here
.BackgroundQuery = False
.Refresh
End With
Set qt = Nothing
Set rDest = Nothing
End Sub
解决方案
Use OLEDB Provider for Excel and it should work fine.
这篇关于使用VBA在Excel中查询SQL Server表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文