Changing Data Source

**guggu** · 09-11-2014, 12:24 AM

Hi,

I have been searching for a long time and can not find anything on this forum or others that can help with my current issue.
We have a PROD and TEST environment and we use System DSN for the ODBC's for this particular application.

Now some users create Excel reports which connect to the System DSN.
This then creates a Data Connection within the Excel file itself which has an embedded SQL statement but points to the PROD database. Within this it also has the username and password of the System DSN.
The PROD and TEST database are on the same server.
We have approx 100 Excel reports all pointing to PROD all in the same directory.

Is there a way I can copy all the files and place then in the TEST environment (Done, easy part).
Then change all the connection to point to the TEST environment easily without having to go into each one and make the change.

Appreciate any help that can be supplied.

**JBeaucaire** · 09-11-2014, 05:16 AM

The general rule is anything you can do by hand can be done in a macro repetitively. Easiest way to test is to turn on the macro recorder, let it record you doing the change you want manually to one of the files in your given folder.... record, open file, make changes, save/close file, turn off recorder.

Do that and post the junk code it creates here and we'll look at turning that one-off into a looper for your entire folder.

**guggu** · 09-15-2014, 01:08 AM

Hi,
Thanks for the reply.
The macro does not help at all.
I just did that and it just places the new connection in there.

I have to manually go in and change the AppPROD to AppUAT
and the INFOPROD to INFOUAT

This needs to be changed in the Data --> Connections --> Properties of the connection --> Definition Tab
Then change both the:

Connection String

Command Text

Hope this helps

**romperstomper** · 09-15-2014, 03:17 AM

Untested but it should be something like this:

Please Login or Register  to view this content.

Note: if you have more than one pivot table connected to the same ODBC connection (rather than OLEDB), this may not work.

**guggu** · 09-15-2014, 07:07 PM

Hi,
That code worked but it created a new connection and renamed the old one to Connection1.
The piviot table automatically changed to connected to the one it was connected to before (now being connection1)

Also if this can be acheived, can I run one VBA macro to change all Excel file in a particular folder at the one time.