Changing the Datasource within Datarange

  • Thread starter Thread starter Bill Gates
  • Start date Start date
B

Bill Gates

Hello All,

I have a requirement to produce a series of reports based on data in a SQL
database. The SQL Database has a number of different databases within it,
all databases have the same tables, views, stored procedures etc yet all
contain different data.

I need to produce a single spreadsheet containing all of the reports which
is capable of access all of the different databases depending on a field
value on one of the worksheets.

All of the reports will only access one database at any one time.

What I think I need to do is manually (through code/macro) modify each
QueryTable's connection string?

Does anyone out there know if this is the correct way to do this, or if
there is any easier way. also has anyone already done it!!!

All help will be appreciated.

Regards
Bill
 
Hi Bill,

What if each Worksheet in your MS-Excel file was a report for one particular
database.

And, what if you used MS-Excel's "Data | Import External Data | New Database
Query ..." on each Worksheet for the connection to a particular database.

And then, what if you had a "master" Worksheet in the MS-Excel file that
contained a Cell that would drive the data connections on each Worksheet to
return your required data.

Ron
 
Thanks for the reply,

I managed to do what I wanted to do by having a sheet that contained the sql
statements for the dataranges in my workbook and the when I carry out a
refresh passing the connection string of the SQL server I want to use and
the SQL statement to each QueryTable.

The connection string is built up from selecting a few parameters on a
switchboard style sheet.

Coooooool eh!
 
Back
Top