A Dynamic Query Worksheet?

I

Indra7

I have the unfortunate task of converting a SQL Server database into
stand-alone Excel file that will store all the data, as well as act a
a "front-end' for browsing the data.

I'd like to have an opening page of many different hyperlinks whic
would send the user to a second worksheet and also pass a variable.
The second worksheet would list the results of a query that would us
the variable as a parameter and would get its data from a huge table o
a 3rd Spreadsheet.

Is this idea possible? If so, I'd appreciate any ideas, tips, hints o
how to get this done.

Thanks
 
O

opeel

Second time I've posted this in as many minutes! From the Excel help o
Hyperlinks:-

The following example creates a hyperlink to cell F10 on the workshee
named Annual in the workbook Budget Report.xls, which is stored on th
Internet at the location named www.business.com/report. The cell on th
worksheet that contains the hyperlink displays the contents of cell D
as the jump text:

HYPERLINK("[http://www.business.com/report/budge
report.xls]Annual!F10", D1)

As for passing the data, simply link the target spreadsheet to the on
you're jumping from. i.e. have both sheets open. Select the targe
cell. Type =. Select the source sheet and the cell where the sourc
data is. Now when you open the target sheet you will be prompted wit
something like " This worksheet has data linked to another worksheet
do you wish to update it" or something like that
 
P

Paul Falla

Dear Indra

We use SQL Server at work as our Data Warehouse, and MS
Excel as our reporting tool (Much cheaper than buying
Crystal reports or Business Objects). If your parameters
are unlikely to change it is probably better to build a
view (query) in SQL Server and link to it from Excel by
using <Data/Get external data/New database query> then
following the steps in the wizard to select your data
source. This in turn will open MS Query where you can
select the view from SQL Server and then drop the fields
into MS Query. After you are happy that you have all the
fields return the data to Excel and finish off the
instructions from the wizard. Depending on how you want to
work it, you may want to set the spreadsheet to refresh on
open, but this could be time consuming if you have large
volumes of data. It is probably easier to set a button
that the user can click to refresh the data on the
individual sheet.

If your parameters will change depending on the the user,
you can prompt the user for a value by going through the
same process above, but linking to the SQL Server database
table in MS Query, and then adding a parameter criteria in
the criteria area of the relevant field (will look like ---
[Please enter the month you want to view] ---), and once
again finishing off the wizard.

The same process is also possible when wanting to provide
users with pivot tables to view, just use the pivot table
wizard in the data menu.

Obviously your front sheet can then hyperlink to the pages
that it needs to.

Sorry if I have been a bit wordy, but it is probably
better to get the process right before you start (The
woeful voice of experience!!)

Hope this helps

Paul Falla
 
I

Indra7

Paul,
Thank you for the reply,
but unfortunately, I need this to be purely stand-alone, 100% in Excel
with the assumption that there will be no access to the network.

But, I appreciate the response
 
J

Jamie Collins

Indra7 wrote ...
unfortunately, I need this to be purely stand-alone, 100% in Excel,
with the assumption that there will be no access to the network.
Click to expand...

Can you bend the rules a little? I'm thinking you could convert to a
local file-based database e.g. a Jet .mdb file: data and schema is
wrapped up in one file and you get at least some attempt at DRI and
reasonable SQL-92 compliance for queries. Even having the data in a
separate .xls file would mean you could query the data using ADO and
SQL using the Jet provider i.e. no DRI but the same reasonable SQL-92
syntax (you can't query an open workbook due to the memory leak bug).
If you are compelled to store the data in the same .xls, could you get
away with dynamically creating a (closed) copy of the workbook at
run-time when you need to query the data?

Using data in workbook and no other file will mean you cannot reuse
any of your existing SQL queries. I'm sure your client will be
interested in the savings associated with code reuse rather than a
total re-engineering.

Jamie.

--
 
Top