PDA

View Full Version : VBA to Change Connection String of all query's in a workbook



richardSmith
05-22-2013, 07:39 AM
Just like the title states what would be VBA to change a connection string of all the query's in a workbook?

SamT
05-22-2013, 08:24 AM
That depends on whether the strings are in code or cells.

richardSmith
05-22-2013, 11:12 AM
I am going to go with cells. They are access query's that are imported into the spreadsheet.

SamT
05-22-2013, 01:56 PM
Select a Cell with a connection string to be replaced. Press Ctrl+H, then click the Advanced button.

Jan Karel Pieterse
05-23-2013, 01:28 AM
SOmething like this:

Sub ChangeAllConnections(sNewConn As String)
Dim oLo As ListObject
Dim oSh As Worksheet
Dim oQt As QueryTable
For Each oSh In Worksheets
'connections can be in a listobject (table)
For Each oLo In oSh.ListObjects
oLo.QueryTable.Connection = sNewConn
Next
'but also outside a listobject (querues created in Excel 2003 or before; web queries)
For Each oQt In oSh.QueryTables
oQt.Connection = sNewConn
Next
Next
End Sub

snb
05-23-2013, 02:35 AM
or ?

Sub M_snb()
For Each cn In ThisWorkbook.Connections
cn.OLEDBConnection.Connection = Replace(cn.OLEDBConnection.Connection, "G:\OF\", "G:\")
Next
End Sub

Aflatoon
05-23-2013, 03:42 AM
That assumes they are all OLEDB and not ODBC, of course. ;)