Query SQL From Excel 2007 And Return Several Values

Question

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

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....

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

Thanks!

You want to get name and phone from SQL Server using the date and id in Excel, is that correct? How do you feel about ADO? — Fionnuala
– Fionnuala, Commented Feb 15, 2011 at 23:11
Just the ID - The Date is already part of the Excel Record, and there is no matching it to the table. — RogueSpear00
– RogueSpear00, Commented Feb 15, 2011 at 23:23
Didn't answer your last question - I would rather avoid ADO if possible, but I'm willing to try anything really. — RogueSpear00
– RogueSpear00, Commented Feb 15, 2011 at 23:24

Fionnuala · Accepted Answer · 2011-02-16 00:29:45Z

ADO, I am afraid.

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

Jody · Accepted Answer · 2011-02-16 00:50:11Z

-3

Unless there is a good reason to not do this in code, you should just use code instead of sql.

answered Feb 16, 2011 at 0:50

Jody

8,3614 gold badges29 silver badges29 bronze badges

3 Comments

Jody Over a year ago

I thought OP was looking for an answer in tsql. But upon rereading, that isn't the case.

RogueSpear00 Over a year ago

I was originally thinking there could be a solution within Excel I can simply Query out from SQL but remain within Excel. Example - SELECT A, B FROM TABLE WHERE C = WorkSheet.Cell - I was hoping that I could simply get the return value of the query and place it back into Excel without going outside of Excel. That seems to not be the case, from my understanding of what I've read so far.

Tommy O'Dell Over a year ago

That may be fine for a really easy/small worksheet, but generally SQL is going to be MUCH more intuitive for aggregating the data.

Collectives™ on Stack Overflow

Query SQL From Excel 2007 And Return Several Values

2 Answers 2

Comments

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Linked

Related