How to have one Excel 2007 workbook use one data connection with a different SQL query for each worksheet?

[dhodgman]

Hi all,

I'm trying to build a workbook (2007) with multiple worksheets where each worksheet corresponds to a different query/dataset pulling data from the one database.

I would have expected that you could define the one data connection object and then specify the SQL that each worksheet table will invoke. The apparent structure of having the query coupled to the connection object is confounding me. I'm therefore trying to set up a separate connection for each of the worksheets and Excel is getting tied up in knots.

Is there a correct / viable way of doing this?

David.

 

[anvg]

Hi.
You can create an adodb connection to your workbook using a connection string Excel connection strings - ConnectionStrings.com
and query any sheet with
Dim rs As Object
Set rs = Connection.Execute "Select * From [sheetname$]"
for example Office Space: Using ADO to Query an Excel Spreadsheet
regards,

 

[dhodgman]

Thanks for that. I've used and adapted those scripts with some success. Much appreciated.