Ok, so you want to migrate servers, eh? Well - all your business users pry have a couple thousand Pivot Reports lying around that point to the old server. We need to point all references to the old server at the new server.
There are two places these references exist (afaik) - The My Data Sources folder (Windows XP / Excel 2007) which holds all of the odc objects for the logged in user, and the real pain - Embedded data sources in Excel Workbooks.
For the odc objects - you can just loop through the My Data Sources folder files and do a little find/replace action.
For the Excel files - we need to first find all the files, then loop through them and hunt for embedded Connections. Once we find a connection, we can just blindly replace occurrences of the old server name with the new server name. The first thing we need here is a function we can call recursively to go hunt down all the excel files - then we loop through and play with the connections (when found).
Let me know how it goes! Here's the full version:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
| Set regEx = New RegExp
regEx.IgnoreCase = True
regEx.Global = True
Set WshShell = Wscript.CreateObject("Wscript.Shell")
strMyDocsPath = WshShell.RegRead("HKCU\Software\Microsoft\Windows\CurrentVersion\Explorer\Shell Folders\Personal")
strDataSourceDir = strMyDocsPath & "\My Data Sources"
'======================== Fix all the Data Sources
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set Folder = objFSO.GetFolder(strDataSourceDir)
Set Files = Folder.Files
For Each File In Files
If LCase(Right(File.Name, 4)) = ".odc" Then
Set objReadFile = objFSO.OpenTextFile(File.Path, 1)
strContents = ""
Do While Not objReadFile.AtEndOfStream
strContents = strContents & objReadFile.ReadLine & vbCrLf
Loop
objReadFile.Close
regEx.Pattern = "(?=.*Data Source=OldCrappyServerName.*)(<odc:ConnectionString>.+</odc:ConnectionString>)"
If regEx.Test(strContents) Then
Set colMatches = regEx.Execute(strContents)
For Each objMatch in colMatches
strOldConnectionString = objMatch.SubMatches(0)
regEx.Pattern = "Data Source=OldCrappyServerName"
strNewConnectionString = regEx.Replace(strOldConnectionString, "Data Source=NewAwesomeServerName")
regEx.Pattern = strOldConnectionString
strContents = regEx.Replace(strContents, strNewConnectionString)
Next
Set objWriteFile = objFSO.CreateTextFile(File.Path, True)
objWriteFile.Write strContents
objWriteFile.Close
End If
End If
Next
'================================ Fix all the embeded connections (yikes)
Set arrFileList = CreateObject( "System.Collections.ArrayList" )
Set objExcel = CreateObject("Excel.Application")
objExcel.DisplayAlerts = False
populateExcelFileList(strMyDocsPath)
For Each strFileName In arrFileList
objExcel.Workbooks.Open(strFileName)
Set objWorkbook = objExcel.Workbooks(1)
For Each objWorkbook in objExcel.Workbooks
If objWorkbook.Connections.Count > 0 Then
WScript.Echo strFileName
For Each objConnection in objWorkbook.Connections
strOldConnection = objConnection.OLEDBConnection.Connection
regEx.Pattern = "OldCrappyServerName"
strNewConnection = regEx.Replace(strOldConnection, "NewAwesomeServerName")
objConnection.OLEDBConnection.Connection = strNewConnection
Next
End If
objWorkbook.Close True
Next
objExcel.Quit
Next
Function populateExcelFileList(path)
Set pathFolder = objFSO.GetFolder(path)
Set pathFiles = pathFolder.Files
For Each pathSubFolder in pathFolder.SubFolders
populateExcelFileList(pathSubFolder.Path)
Next
For Each pathFile in pathFiles
If (LCase(Right(pathFile.Name, 5)) = ".xlsx" _
Or LCase(Right(pathFile.Name, 4)) = ".xls") _
And Left(pathFile.Name, 1) <> "~" Then
arrFileList.Add pathFile.Path
End If
Next
End Function |
Update
I added in support for .xls files - doing this against .xls files can (and usually will) break backwards compatibility for SSAS Pivot Tables.
November 13th, 2008 - 10:11
Seems very interesting! But how do you execute this script?
Thanks!
November 13th, 2008 - 10:20
You cut and paste it in to a file called something like updateExcelConnections.vbs and then run the VBS file (it will execute using your default script host – which is usually wscript.exe). We ran this across various shared drives and against a SharePoint repository as well (all the Excel documents MUST be checked in).
The only place we had to manually update connection strings was for Excel documents stored in SSRS.
July 13th, 2011 - 14:10
Thanks Tim nice handy bit of code saved changing the 4.5k connections inside my Excel document. The only issue I had with this was the connection type. The Excel document that I was working with had ODBC connections so I had to change from OLEDBConnection to ODBCConnection. Maybe this could be an addition to work out the connection type objConnection.Type?