Sql Query a Excel spreadsheet

[ClulessChris]

Can anybod give me some pointers on using Sql to search an Excel spreadsheet?
I'm familiar with using DAO / ADO to query a database, but in this instance I'd like to query a spreadsheet from within the same workbook (e.g. Sheet1!A1 = "SELECT 'column C' FROM Sheet2 WHERE 'Column B' = 123").

Is it even worth doing this with VB or are there inbuilt Functions for this action?

Everybody body is somebodys Nutter.

 

[indupriya9]

Hi Clulesschris

May be you could use the vlookup function in excel

ip

 

[SkipVought]

Hi,

This can be done without any VBA, using native Excel functionality: Data>Get External Data>New Database Query...

This adds a QueryTable to the sheet that can, subsequently, be Data>Refresh to retunr a fresh recultset.

Skip,

_[red][/red]

 

[SkipVought]

FYI

faq68-5829

Skip,

_[red][/red]

 

[ClulessChris]

Many thanks for you responces.

Everybody body is somebodys Nutter.

 

[ClulessChris]

F.y.i

I used the following connection string :

Set db = Opendatabase(FileName, False, True, "Excel 8.0;HDR=Yes;")

Opens Excel with DAO just fine.

Everybody body is somebodys Nutter.