Follow along with the video below to see how to install our site as a web app on your home screen.
Note: This feature may not be available in some browsers.
Sub MapCurrentConnections_Default()
'
' LOOK IN THE QUERY DESCRIPTION TO DETERMINE PREVIOUS PATH, WHICH WAS PLACED THERE FOR THIS PURPOSE
' REPLACE PORTION OF SQL STATEMENT, CONNECTION PARAMETERS, AND DESCRIPTION WITH NEW PATH (BASED ON LOCATION OF THIS FILE)
' THIS PROVIDES THE MECHANICS FOR EACH QUERY TO CHANGE PARAMETERS BASED ON LOCATION OF XL FILE
Dim sQuery As WorkbookConnection
TargetDirectory = ThisWorkbook.Path & "\" ' TARGET FULL PATH
For Each sQuery In ActiveWorkbook.Connections
If InStr(sQuery.Name, "PivotTable") = False Then ' ONLY PROCESS NON-PIVOT TABLE CONNECTIONS
DoEvents: Application.StatusBar = "Query : " & sQuery.Description & " - " & sQuery.Name
rString = sQuery.Description ' CONNECTION DESCRIPTION - USED AS REPOSITORY FOR SEARCH/REPLACE STRING
sQuery.Description = Replace(sQuery.Description, rString, TargetDirectory) ' CONNECTION DESCRIPTION FOR NEXT TIME
sQuery.ODBCConnection.Connection = Replace(sQuery.ODBCConnection.Connection, rString, TargetDirectory) ' CONNECTION PARAMETERS
sQuery.ODBCConnection.CommandText = Replace(sQuery.ODBCConnection.CommandText, rString, TargetDirectory) ' SQL STATEMENT
End If
Next sQuery
Application.StatusBar = Empty: Set sQuery = Nothing
End Sub