Hi,
My existing Excel 2007 spreadsheet gets external data from MSSQL. I created the connection in a DSN.
By using the external 'Connection type' of Database Query (by originally choosing to insert an External Data source from MS Query), Excel allows you to pass parameters through to a stored procedure by entering question marks into the EXEC command.
e.g. exec ReadData ?, ?
Within Excel's Connection Properties dialogue box, you're then able to choose which cells are the input values to those parameters. In the example above, it'll prompt you twice to choose the cells for parameters.
Here is the question ...
I want to change the connection to OLEDB and eradicate the requirement to have a DSN on the client machine. Excel allows you to create an OLEDB connection to MSSQL. However, it does not allow you to choose the input cells to act as the inputs to the stored procedure.
The Parameters... button is greyed out within the Connection Properties..Definition tab. As a consequence re-running the query, Excel errors up "no value given for one or more required parameters".
Is there any way of passing Excel call values into an stored procedure where the DB connection is OLEDB ?
I'm hoping someone at least understands this question, let alone answering it (!).
If *anyone* can help, I'd really appreciate it.