Replace Query Database

[jo15765]

How in Excel 2007/Excel 2010 can you use VBA to replace the database of all queries on the worksheet?

[westconn1]

not enough information

[jo15765]

not enough information
Let's say there is an imported Access Query into Excel and the query text is (SQL behind the query)

Select * from qry_Uno
FROM c:\AlphaDawg\12345.qry_Uno qry_Uno


How can I change the 12345 (database name) to 84321

[jonh]

Sub test()

Const oldpath As String = "C:\old"
Const newpath As String = "C:\new"
Const db As String = "\mydb.mdb"

Dim c As WorkbookConnection
For Each c In ActiveWorkbook.Connections
Select Case c.Type
Case 1
c.OLEDBConnection.Connection = _
"OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & newpath & db
c.OLEDBConnection.SourceDataFile = newpath & db
Case 2
c.ODBCConnection.Connection = _
Replace(c.ODBCConnection.Connection, oldpath, newpath)
c.ODBCConnection.CommandText = _
Replace(c.ODBCConnection.CommandText, oldpath, newpath)
End Select

c.Refresh
Next
End Sub

[jo15765]

What are the 2 different cases account for?

[jonh]

If I remember correctly, the first case is for tables/queries linked directly, the second is for queries written using the query builder thing.
Judging by the sql you posted above you would be using the second case.