从Excel 2007查询SQL并返回几个值 [英] Query SQL From Excel 2007 And Return Several Values

问题描述

我正在尝试获取少量数据，在Excel中约200个字段，并从SQL中检索数据，并在每个项目的where子句中使用该字段.

I'm attempting to take a small amount of data, about 200 fields in Excel and retreive data from SQL with that field in the where clause for each item.

TABLE:

ID  Name    Phone
1   Test1   1234
2   Test2   1235
3   Test3   1236


Excel:
Date   ID
2/1/11 1
2/1/11 2
2/1/11 3

我希望能够在excel中进行检索(希望本身无需编写任何其他代码-可能只是带有查询的简单Excel ODBC或SQL连接.因此，我的数据最终将在Excel文档中显示:

I want to be able to retrieve, within excel (hopefully without writing any additional code per se - maybe a simply Excel ODBC or SQL connection with a query. So my data would end up as such on the Excel Document:

Excel:
Date   ID  Name    Phone 
2/1/11 1   Test1   1234
2/1/11 2   Test2   1235
2/1/11 3   Test3   1236

我不确定我是否足够清楚地解释自己....

I'm not sure if I'm explaining myself clearly enough....

我正在使用Excel 2007，并且在某个地方也有2010. SQL是SQL Server 2000.

I'm using Excel 2007 and I also have 2010 laying around somewhere. SQL is SQL Server 2000.

谢谢！

推荐答案

恐怕是ADO.

Dim cn As Object
Dim rs As Object
Dim strFile As String
Dim strCon As String
Dim strSQL As String
Dim s As String
Dim i As Integer, j As Integer

''This is not the best way to refer to the workbook
''you want, but it is very convenient for notes
''It is probably best to use the name of the workbook.

strFile = ActiveWorkbook.FullName

''Note that if HDR=No, F1,F2 etc are used for column names,
''if HDR=Yes, the names in the first row of the range
''can be used.
''This is the Jet 4 connection string, you can get more
''here : http://www.connectionstrings.com/excel

strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile _
    & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"

''Late binding, so no reference is needed

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")


cn.Open strCon

strSQL = "SELECT * " _
       & "FROM [Sheet1$] a " _
       & "LEFT JOIN " _
       & "[ODBC;Driver={SQL Server Native Client 10.0};" _
       & "Server=servername;Database=test;" _
       & "Trusted_Connection=yes].tbl b " _
       & "ON a.[Id]=b.[Id] "

rs.Open strSQL, cn, 3, 3


''Pick a suitable empty worksheet for the results
Worksheets("Sheet3").Cells(2, 1).CopyFromRecordset rs

''Tidy up
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing

这篇关于从Excel 2007查询SQL并返回几个值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！