Hi

We are moving our BI test enviroment to production and we have a lots of Excel documents there.

In the Excel files we use connection file and "Always use connection file".
The thing is that the production name is almost the same as in test, companybidev >> companybi.

E.g.
http://companybidev/Data Connections for PerformancePoint/Bike stores.odc
http://companybi/Data Connections for PerformancePoint/Bike stores.odc

Is it possible to change the connection file syntax with a script or something and do it in multiple documents at the same time?

Regards

If your odc is stored in a folder and the odc is used in multiple xl then you can change the relevant properties of connection in one shot for all file.But as you are moving the odc to diff location I suppose you need to update individually.

1.Open a file

2.Change the SourceConnectionFile to new odc file path.

3.Refresh connection

4.Save

5.Exit from the file

6.Goto step 1

As I wrote, the connection file path for "Always use connection file", not the odc file it self.

To change in odc file you just opens all of them in notepadd++ and then search/replace the source.

This is about how to change the path, and do it in multiple files at the same time.

From: http://companybidev/Data Connections for PerformancePoint/Bike stores.odc
To: http://companybi/Data Connections for PerformancePoint/Bike stores.odc

....and what if the path is another totally not the same as above?

In Excel the connection object has a property SourceConnectionFile which let's you specify the file path and name.That is what I outlined above...

All the best. 

check this link this may help you out http://spreadsheets.about.com/od/tipsandfaqs/f/num_format.htm

Hi,
 
I am writing to see how everything is going with this issue. Is the problem resolved? If there is anything I can do for you, please feel free to let me know.
 
Best Regards, 

Hi...
This is our scenario:
Dev. site --> http://acmebidev
Approx. 50 data connection files (ODC) pointing to different cubes.

In Sharepoint we use excel templates, everyone pointing to each odc.
e.g. http://acmebidev/Data Connections for PerformancePoint/Total Sales.odc

Every report is based on a template --> Connection file - Always use connection file = ODC.

All odc files and excel templates are moving to production site --> http://acmebi
To change the source in odc, we opened them all in Notepad++ and did search/replace, acmebidec >> acmebi.

So, our problem maintains. How do we change the office data connection in multiple excel files at the same time. And of course, they are spread out in Sharepoint. Though, it shouldnt be any problem to just search after xlsx-files.
The name and the place is the same for both dev and prod, therefore it should be simple as changing http://acmebidev --> http://acmebi.

Hi,

The following subroutine can be used to change the connection settings after the workbook being opened. Maybe you can create loop code to open the workbooks and call the subroutine:

Sub Demo()
    Dim blnSuccess As Boolean
    
    blnSuccess = ChangeExternalDataConnectionFile(1, "http://acmebidev", "http://acmebi")
    If blnSuccess Then
        MsgBox "The connection file was successfully changed!", vbInformation, "Message"
    Else
        MsgBox "Failed to change the connection file!", vbCritical, "Error"
End If

    ThisWorkbook.Save
End Sub

Function ChangeExternalDataConnectionFile(ByVal WhichConnection As Integer, _
    ByVal OldString As String, _
    ByVal NewString As String) As Boolean
    
    Dim blnSuccess  As Boolean
    Dim strConnFile As String
    Dim collConn    As Connections
    Dim odbcConn    As ODBCConnection
    Dim wbConn      As WorkbookConnection
    
On Error Resume Next

    Set collConn = ThisWorkbook.Connections
    
    ' /* If the number of objects in the collection is not zero. */
    If collConn.Count <> 0 Then
                
        ' /* If the type of the specified connection is ODBC. */
        If collConn(WhichConnection).Type = xlConnectionTypeODBC Then
            Set odbcConn = collConn(WhichConnection).ODBCConnection
            
            With odbcConn
                ' /* If the connection file is always used to establish connection to the data source. */
                If .AlwaysUseConnectionFile = True Then
                    strConnFile = .SourceConnectionFile
                    strConnFile = Replace(strConnFile, OldString, NewString)
                    .SourceConnectionFile = strConnFile
                    .Refresh
                End If
            End With
        End If
        
        ' /* If there's no error. */
        If Err.Number = 0 Then blnSuccess = True
    Else
        blnSuccess = False
    End If
    
    ' Return the execution result to the function.
    ChangeExternalDataConnectionFile = blnSuccess
    
    ' /* Release memory. */
    If Not (collConn Is Nothing) Then Set collConn = Nothing
    If Not (odbcConn Is Nothing) Then Set odbcConn = Nothing
    If Not (wbConn Is Nothing) Then Set wbConn = Nothing
End Function

Best regards,

Hi,

Just checking in to see if the information was helpful. Please let us know if you would like further assistance.

Best Regards, 

Hi,
 
As I have not heard from you for several days. I will go ahead and close this thread. If you have any questions, please feel free to reply to us and this thread will be re-opened.
 
Best Regards, 

No, I dont get this to work.

I run it like a macro sub routine, and I get this message:

But when I look under connections their's still http://acmebidev/Data Connections for PerformancePoint/..............

Sorry, the screenshot seems to be broken and cannot be viewed. Can you discribe the steps you have tried to use the macro?

Best regards,

Hi
The message box for "MsgBox "The connection file was successfully changed!", vbInformation, "Message" comes up, but nothing has changed.

But anyway, its not very interesting if we had to the procedure for every document.
Right now we try to make a .net program for this, almost done.

Did you ever find a way to do this? We have the same issue. 
When we copy our production environment to the test environment of Sharepoint, the Excel files are continuing to use the odc files on the production environment but it needs to be changed to point to test. 

Hi

We actually made an application to do this. It's 2,5 years ago but I recall it worked really good  :)