[RESOLVED] Query a spreadsheet?

[mikeyc1204]

I put together a reporting app in Excel similar to some you may have seen before. You define criteria for different rows and columns and the intersection of these rows and columns are populated with summary data from an outside database at runtime. You can drill down to the detailed data as you would with a pivot table.

The database is completely out of my control and the connection to it is less than reliable.

As a workaround to getting the detail from the DB I am replicating the relevant tables into worksheets. Size isn't an issue since the tables are never more than a few thousand lines or so. Instead or requerying the db when more detail is needed, I'd like the program to query this backup sheet. I know there are about 100 different ways to pull filtered data from a spreadsheet, but other than this piece, I'm done with the app and I'd rather not recreate the wheel.

Is there a way I can query a spreadsheet with standard SQL? The backup data will be in the open workbook calling the query. Is it necessary to create an ODBC connection to the workbook? If so, are there access issues involved since the user will always be in the workbook being queried?

[RobDog888]

Yes, you can use ADO and query the Excel sheet the same as a database table. I have some code on the forums already so a search should bring them up. You can also check http://www.connectionstrings.com for the conn string needed.

[mikeyc1204]

Wow.. this forum is littered with answers. I guess searching for ADO instead of DAO would have helped. Thanks.

For anyone waiting with baited breath...
http://www.vbforums.com/showthread.p...DO+excel+sheet

[cssriraman]

You can try this also:

http://www.vbforums.com/showthread.p...ed#post2347116

[RobDog888]

"Littered" lol I would say "populated with valuable information".