Hi Mike,
Mike said:
I have a workbook that has a database connection for
refreshing the data. It was initialy set up using a DSN.
Now we have moved the database and I changed the DSN to
point to the new Server but the sheet still goes to the
old server. Does excel keep the connection info
internally somewhere after the initial setup and not use
the DSN. I have about 500 workbooks this way and would
like to write a quick applet to go through all the files
and update the connection info.
I'm not sure why this is happening. AFAIK, the old connection info is not
cached, but I don't know for sure.
Here's some (untested) code that may help you convert the connection string
for all query tables that match a given connection string in a folder of
Excel files. To use it, you must set a connection to Microsoft Scripting
Runtime in Tools | Options in the VBE.
Private Sub mConvertCxStr(rsWBPath As String, _
rsOldCxStr As String, rsNewCxStr As String)
Dim ws As Worksheet
Dim qt As QueryTable
Dim wbTarget As Workbook
On Error GoTo ErrHandler
Set wbTarget = Workbooks.Open(rsWBPath)
For Each ws In wbTarget.Worksheets
For Each qt In ws.QueryTables
If StrComp(qt.Connection, rsOldCxStr, _
vbTextCompare) = 0 Then
qt.Connection = rsNewCxStr
End If
Next qt
Next ws
ExitRoutine:
If Not wbTarget Is Nothing Then
wbTarget.Close False
Set wbTarget = Nothing
End If
Exit Sub
ErrHandler:
MsgBox Err.Number & ": " & Err.Description, _
vbExclamation, "ERROR"
Resume ExitRoutine
End Sub
Public Sub BatchConvertCxStr(rsFolder As String)
Dim fso As Scripting.FileSystemObject
Dim fil As Scripting.File
Set fso = New Scripting.FileSystemObject
If fso.FolderExists(rsFolder) Then
For Each fil In fso.GetFolder(rsFolder).Files
If fil.Type = "Microsoft Excel Worksheet" Then
mConvertCxStr fil.Path, "OldCXString", _
"NewCXString"
End If
Next fil
End If
End Sub
--
Regards,
Jake Marx
MS MVP - Excel
www.longhead.com
[please keep replies in the newsgroup - email address unmonitored]
