May 8, 2007 at 5:18 pm
Please can someone help me. There was no box for Excel so I used this one. I have an existing Excel (2003) spreadsheet that uses data via a stored procedure from a SQL Server database. I need to change the connection string for the location of the database. I have searched the help but the options I am trying to get to are greyed out. When I select an empty cell I can get to the options and select another data source but it keeps telling me to select a table. I don't want a table I want to run a stored procedure. When I try to edit the query it gives me an error saying I can't edit the query and opens a new one. When I set up a whole new query it wants me to save it but where do I save it to so others in the team can update the spreadsheet if I am not here... I have spent days on this and am now so frustrated. All I want to do is change the connection string to be from the new server and leave everything else the same... It sounds so simple but causes many a grey hair and added aggression...
May 9, 2007 at 11:11 am
Did you check the Macros?...
May 9, 2007 at 3:07 pm
Yep no macros. It runs a stored proc (with a parameter) which I guess is called via the query and that is all ok... I don't need to change that, I only need to change the datasource.
May 12, 2007 at 3:31 pm
Julie, try going into Control Panel ~ Admin Tools ~ Data Sources (ODBC) and then change the info on the connection from there.
May 13, 2007 at 2:53 pm
Yep done that too.. I don't think there was nothing I didn't try... except one. I was working on Saturday and trying to figure this out as this was one of the last things not moved to SQL Server 2005. It suddenly dawned on me maybe I'm trying to do this the wrong way. Maybe instead of trying how to change the datasource, deattach the database so Excel can't make a connection. What would that do? Well it prompted me for a datasource. I entered the new one and wulla.. SUCCESS.. something so bloody simple turned out to be one of my worst nighmares. Thanks for your time and suggestions
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply