从 Excel 2007 查询 SQL 并返回多个值

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

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 文档中这样显示：

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

我不确定我是否解释得足够清楚......

我使用的是 Excel 2007，并且我也有 2010 版本。 SQL 是 SQL Server 2000。

Thanks!

阿杜，恐怕。

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