I have a spreadsheet that refreshes data from a data base. I need to change
the data base name (data source name). The tables and field selects are the
same but I need to connect to a different data source.

I have good news and bad news.

The bad news :You can't change the connection. It is harder than just
changing the connection.

The good new is you can get your old setting and copy it to a new connection.

1) First I would get the old query properties and pste the properties into
Notepad. these are the instructions for Excel 2003. It is different for
excel 2007.

a)click on any cell it the returned query data. then go to menu

Data - Import External Data - Edit Query
b) Press the SQL button
c) Copy SQL data to Notepad

2) Clear entire worksheet where old query was located. Answer question that
you don't wnat to keep old query.

3) Create New connection using menu

Data - Import External Data - New Datbase Query (or any type query).

4) Select any options (at least one table entry). doesn't mater because yo
uare going to change then when you put in the old SQL Statements.

5) Continu going through the menus until you get to Finish. Select Edit
Query and then puh Finish. the qery editor will again appear.

6) Press SQL button. Copy SQL statments from Notepad into SQL window.

7) In Edit Query window go to menu

File - Return Data to Microsoft Excel

"LindaJo" wrote:

> I have a spreadsheet that refreshes data from a data base. I need to change
> the data base name (data source name). The tables and field selects are the
> same but I need to connect to a different data source.

i have the same problem, but when I click on edit for editing the query an
error message comes up telling the server rejected the connection.
My Databse was upgraded over the weekend but the name did not change.
Creating a new query works fine. ???
please help.

gil-

"joel" wrote:

> I have good news and bad news.
>
> The bad news :You can't change the connection. It is harder than just
> changing the connection.
>
> The good new is you can get your old setting and copy it to a new connection.
>
> 1) First I would get the old query properties and pste the properties into
> Notepad. these are the instructions for Excel 2003. It is different for
> excel 2007.
>
> a)click on any cell it the returned query data. then go to menu
>
> Data - Import External Data - Edit Query
> b) Press the SQL button
> c) Copy SQL data to Notepad
>
> 2) Clear entire worksheet where old query was located. Answer question that
> you don't wnat to keep old query.
>
> 3) Create New connection using menu
>
> Data - Import External Data - New Datbase Query (or any type query).
>
> 4) Select any options (at least one table entry). doesn't mater because yo
> uare going to change then when you put in the old SQL Statements.
>
> 5) Continu going through the menus until you get to Finish. Select Edit
> Query and then puh Finish. the qery editor will again appear.
>
> 6) Press SQL button. Copy SQL statments from Notepad into SQL window.
>
> 7) In Edit Query window go to menu
>
> File - Return Data to Microsoft Excel
>
> "LindaJo" wrote:
>
> > I have a spreadsheet that refreshes data from a data base. I need to change
> > the data base name (data source name). The tables and field selects are the
> > same but I need to connect to a different data source.

I was able to duplicate your problem by making a copy of my dattabase and
then create a query to the copy. I then savved my excel spreadsheet and quit
excel..I then went and deleted the copy of my database and opened the excel
spreadsheet. When I went to edit the query I got a message box asking me to
login to the database. here is what I did

1) Press the DATABASE button on the message box. Then slected another
database. You can select your original database
2) I continued and got anotehr error message saying i couldn't edit theh
query. I ignored the error message an d got into the edit query window.
3) Press SQL and got my SQL statements.

"gil" wrote:

> i have the same problem, but when I click on edit for editing the query an
> error message comes up telling the server rejected the connection.
> My Databse was upgraded over the weekend but the name did not change.
> Creating a new query works fine. ???
> please help.
>
> gil-
>
> "joel" wrote:
>
> > I have good news and bad news.
> >
> > The bad news :You can't change the connection. It is harder than just
> > changing the connection.
> >
> > The good new is you can get your old setting and copy it to a new connection.
> >
> > 1) First I would get the old query properties and pste the properties into
> > Notepad. these are the instructions for Excel 2003. It is different for
> > excel 2007.
> >
> > a)click on any cell it the returned query data. then go to menu
> >
> > Data - Import External Data - Edit Query
> > b) Press the SQL button
> > c) Copy SQL data to Notepad
> >
> > 2) Clear entire worksheet where old query was located. Answer question that
> > you don't wnat to keep old query.
> >
> > 3) Create New connection using menu
> >
> > Data - Import External Data - New Datbase Query (or any type query).
> >
> > 4) Select any options (at least one table entry). doesn't mater because yo
> > uare going to change then when you put in the old SQL Statements.
> >
> > 5) Continu going through the menus until you get to Finish. Select Edit
> > Query and then puh Finish. the qery editor will again appear.
> >
> > 6) Press SQL button. Copy SQL statments from Notepad into SQL window.
> >
> > 7) In Edit Query window go to menu
> >
> > File - Return Data to Microsoft Excel
> >
> > "LindaJo" wrote:
> >
> > > I have a spreadsheet that refreshes data from a data base. I need to change
> > > the data base name (data source name). The tables and field selects are the
> > > same but I need to connect to a different data source.

You mention that these instructions are different for 2007. I'm trying to
change the data source for a pivot table over SSAS. Can you help? Do you
know how to do this in this case? Our desire is for all the spreadsheets to
use a central odc so that if something changes, we only have to change one
spot, but the developer developed with a personal odc. I can't get it to
redirect to a shared and your instructions below don't quite match up with
2007. Help, please.

"joel" wrote:

> I have good news and bad news.
>
> The bad news :You can't change the connection. It is harder than just
> changing the connection.
>
> The good new is you can get your old setting and copy it to a new connection.
>
> 1) First I would get the old query properties and pste the properties into
> Notepad. these are the instructions for Excel 2003. It is different for
> excel 2007.
>
> a)click on any cell it the returned query data. then go to menu
>
> Data - Import External Data - Edit Query
> b) Press the SQL button
> c) Copy SQL data to Notepad
>
> 2) Clear entire worksheet where old query was located. Answer question that
> you don't wnat to keep old query.
>
> 3) Create New connection using menu
>
> Data - Import External Data - New Datbase Query (or any type query).
>
> 4) Select any options (at least one table entry). doesn't mater because yo
> uare going to change then when you put in the old SQL Statements.
>
> 5) Continu going through the menus until you get to Finish. Select Edit
> Query and then puh Finish. the qery editor will again appear.
>
> 6) Press SQL button. Copy SQL statments from Notepad into SQL window.
>
> 7) In Edit Query window go to menu
>
> File - Return Data to Microsoft Excel

The laptop I use isn't running so I don't have access to 2007. I just read
that the Edit query is on the External Data toolbar in 2007. I think that is
under the Data menu. The edit query tool is the same in 2007 as 2003. I
also read you couldn't edit a webquery.

A Pivot tasble is not like a query table with a connection. Yo can use the
code below to get the sopurce data for the pivot table. Then edit the data
in the spreadsheet to change where the new source is located. Then use 2nd
macro that takes the data in the spreadsheet and put it back into the pivot
table

Sub gettable()
Set a = ActiveSheet.PivotTables

RowCount = 1
With Sheets("Sheet1")
For Each tbl In ActiveSheet.PivotTables
For Each src In tbl.SourceData
.Range("A" & RowCount) = src
RowCount = RowCount + 1
Next src
Next tbl
End With

End Sub

Sub Puttable()
Set a = ActiveSheet.PivotTables

RowCount = 1
With Sheets("Sheet1")
For Each tbl In ActiveSheet.PivotTables
For Each src In tbl.SourceData
src = .Range("A" & RowCount)
RowCount = RowCount + 1
Next src
Next tbl
End With

End Sub


"AudKen" wrote:

> You mention that these instructions are different for 2007. I'm trying to
> change the data source for a pivot table over SSAS. Can you help? Do you
> know how to do this in this case? Our desire is for all the spreadsheets to
> use a central odc so that if something changes, we only have to change one
> spot, but the developer developed with a personal odc. I can't get it to
> redirect to a shared and your instructions below don't quite match up with
> 2007. Help, please.
>
> "joel" wrote:
>
> > I have good news and bad news.
> >
> > The bad news :You can't change the connection. It is harder than just
> > changing the connection.
> >
> > The good new is you can get your old setting and copy it to a new connection.
> >
> > 1) First I would get the old query properties and pste the properties into
> > Notepad. these are the instructions for Excel 2003. It is different for
> > excel 2007.
> >
> > a)click on any cell it the returned query data. then go to menu
> >
> > Data - Import External Data - Edit Query
> > b) Press the SQL button
> > c) Copy SQL data to Notepad
> >
> > 2) Clear entire worksheet where old query was located. Answer question that
> > you don't wnat to keep old query.
> >
> > 3) Create New connection using menu
> >
> > Data - Import External Data - New Datbase Query (or any type query).
> >
> > 4) Select any options (at least one table entry). doesn't mater because yo
> > uare going to change then when you put in the old SQL Statements.
> >
> > 5) Continu going through the menus until you get to Finish. Select Edit
> > Query and then puh Finish. the qery editor will again appear.
> >
> > 6) Press SQL button. Copy SQL statments from Notepad into SQL window.
> >
> > 7) In Edit Query window go to menu
> >
> > File - Return Data to Microsoft Excel
>