How in Excel 2007/Excel 2010 can you use VBA to replace the database of all queries on the worksheet?
How in Excel 2007/Excel 2010 can you use VBA to replace the database of all queries on the worksheet?
Go to Heaven for the climate, Hell for the company.
~~Mark Twain
not enough information
Go to Heaven for the climate, Hell for the company.
~~Mark Twain
[vba]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
[/vba]
What are the 2 different cases account for?
Go to Heaven for the climate, Hell for the company.
~~Mark Twain
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.