> Well, don't use Excel then ;-)
Unfortunately, that's not an option. Our clients almost universally
have MS Office installed and have semi-professional office workers that
would like to cut their own reports to our database. I'm basically
setting up example templates to show what the server can do. Why MS did
not set up sql-passthrough directly into excel completely blows my mind.
Excel 2003 Pro allows you to edit the spreadsheet properties but most of
our users are around the 2000/2002 level.
What's really surprising me is how much trouble I'm having getting
decent information on binding excel to a database. I would have thought
this a more common approach to presenting data in an office.
> You could just do the whole thing in code within
> Excel, and avoid MS Query altogether. ADO is my
> preferred choice for this. Parameters could be
> captured by a dialog box that could run on opening the
That's will probably work, I'll take a look. I wanted to avoid using a
coding approach to things but this be the only way.
> Alternatively you could try interposing MS Access,
> i.e. define your queries in an Access database, and
> use those queries as the datasource for the Excel
> report. Note that I haven't done this myself, I just
> know that it's doable, and I suspect that the tight
> integration of MS Office products will allow you to
> avoid using MS Query altogether. But you may find
> yourself having to use pass-through queries, with the
> need to code the capturing of parameters, so that may
> not be a win in the end.
Another good idea. Actually, I prefer this to cutting VB code into the
spreadsheet. However, I'll still run into parameterization problems in
Excel, namely when using ~* and ilike in parameterized queries.
Another tricky solution would be to redefine some operators on the
server to expand what can be done inside ms query. Maybe using
set-returning functions might also work. My queries are generally
already wrapped in views so my problems are mostly with the various
operators.
Merlin
