Two object exist for picking data from SQL-S to Excel, ie pivottable and
The connection property is reachable by code in VBA with something like:
in the debug window reached in VBA by Alt/F11 in Excel if you clcik on the
sheet containing the data.
The connection property could also be changed in the Excel interface:
If a querytable right/click in the table and select Edit Query to enter yhe
wizard for building the query. If a pivottable right/click and select wizard.
All connections are based on ODBC so to get it reachable for many users could
be a bit tricky since the ODBC-connection is either a file or an entry in the
registry in the user's machine....
Rickard Olsson, Sweden
"Michael MacGregor" wrote:
> We have an Excel spreadsheet that was created by someone who left the
> company and who did not document what they had done. This spreadsheet is
> supposed to be connect to a SQL Server to extract data for a report, however
> the server on which the data resides was changed, by the same person who
> created the spreadshseet and who subsequently forgot to update the
> spreadsheet, and this is probably only the first of many that will surface
> over time.
> After it opens and attempts to refresh, it causes an error:
> "Connection failed:
> SQLState: '08004'
> SQL Server Error: 4060
> Server rejected the connetion; Access to selected database has been denied"
> Clicking on "OK" results in a SQL Server Login dialog box appearing. In the
> Server drop-down list it has the name of the server on which the data used
> to reside, so it's hardly surprising that access was denied because the
> database doesn't even exist there.
> It also has Use Trusted Connection checked.
> Now how do I edit the connection parameters so that it automatically
> connects to the right server rather than raise an error and then prompt for
> the connection details? I can't figure out where this is defined.
> Michael MacGregor
> Database Architect (not an Excel expert at all!)