VBA to Change Connection String of all query's in a workbook

[richardSmith]

Just like the title states what would be VBA to change a connection string of all the query's in a workbook?

[SamT]

That depends on whether the strings are in code or cells.

[richardSmith]

I am going to go with cells. They are access query's that are imported into the spreadsheet.

[SamT]

Select a Cell with a connection string to be replaced. Press Ctrl+H, then click the Advanced button.

[Jan Karel Pieterse]

SOmething like this:

[VBA]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
[/VBA]

[snb]

or ?
[VBA]
Sub M_snb()
For Each cn In ThisWorkbook.Connections
cn.OLEDBConnection.Connection = Replace(cn.OLEDBConnection.Connection, "G:\OF\", "G:\")
Next
End Sub
[/VBA]

[Aflatoon]

That assumes they are all OLEDB and not ODBC, of course.