T
Tom Edelbrok
Using MS Query and Excel 97 I was able to build a connection to a SQL
server, include parameters (ie: for the 'start date' and 'end date'), and
save the Connection directly into the Workbook.
However, now that I've converted my Workbooks to Excel 2007 I can't edit the
queries (I get the message "This query cannot be edited by the query
wizard"). Yet the query still works, and I've been able to replace the SQL
statement in it with a call to a Stored Procedure with '?' symbols for
parameters. For example, 'exec sp_PILESPRAYS_HighMastShiftReport ? , ?'.
When I ask for external data to be refreshed I am prompted for a start date
and and end date, which is what I expect and want. But it seems I've
retained some sort of a legacy query. Ideally I'd like to replace this with
the Office 2007 equivalent.
But when I create a new external data connection I'm always forced to save
it in an ODC file. I really want to save it in my workbook directly, like
I've always done before. Secondly, when the 'Parameters' button is grayed
out, so I guess it has to be an ODBC Query, but when I try that it tells me
I can't use parameters in a query that "can't be represented graphically".
So what can I do? I have an ancient Query that works, but I can't reproduce
it with Office 2007 - all I can do is maintain this old query in two
spreadsheets where it is already defined. If I have to create new
spreadsheets with parameters I'm hooped.
Any ideas?
Tom
server, include parameters (ie: for the 'start date' and 'end date'), and
save the Connection directly into the Workbook.
However, now that I've converted my Workbooks to Excel 2007 I can't edit the
queries (I get the message "This query cannot be edited by the query
wizard"). Yet the query still works, and I've been able to replace the SQL
statement in it with a call to a Stored Procedure with '?' symbols for
parameters. For example, 'exec sp_PILESPRAYS_HighMastShiftReport ? , ?'.
When I ask for external data to be refreshed I am prompted for a start date
and and end date, which is what I expect and want. But it seems I've
retained some sort of a legacy query. Ideally I'd like to replace this with
the Office 2007 equivalent.
But when I create a new external data connection I'm always forced to save
it in an ODC file. I really want to save it in my workbook directly, like
I've always done before. Secondly, when the 'Parameters' button is grayed
out, so I guess it has to be an ODBC Query, but when I try that it tells me
I can't use parameters in a query that "can't be represented graphically".
So what can I do? I have an ancient Query that works, but I can't reproduce
it with Office 2007 - all I can do is maintain this old query in two
spreadsheets where it is already defined. If I have to create new
spreadsheets with parameters I'm hooped.
Any ideas?
Tom