Change a connection string using vba within excel

Hi there,

I currently have a workbook located in a fixed folder "C:\Program Files\Analysis", which has the following connection string within excel
DSN=FashionPlus;DBQ=C:\Program Files\Analysis"\Reports.MDB;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;

This then means that my database is situated above and a normal refresh finds the data and the information and refreshes. This all works correctly.

Fixed paths were also set within access to open up excel and this has also worked correctly. With the ever changing systems, we have found that using "C:\Program Files" may not be ideal anymore. This meant that all fixed paths had to be removed. This was easy to do within access and was successful.

The problem comes in when now trying to automate the path to the access database. This path "C:\Program Files\Analysis"\Reports.MDB" is now no more valid as the access database will no more be located within these folders. There will however be a folder called analysis within the folder that holds the access database. How can I point to that specific database within the connection string. The path to the database is not a fixed path and may change according to the user. I need this to be automated though in that as soon as the excel workbook is opened, the user can just click refresh to get to the correct database and refresh accordingly. Any help would be greatly appreciated
Vineshan

Hi there, many thanks for your help...your response is much appreciated. I have however found some code that I have managed to manipulate to work for me
Private Sub Workbook_Open()

Dim conn As WorkbookConnection
Dim sOldConnection As String, sNewConnection As String
Dim sOldPath As String
Dim sNewPath As String
Dim sLength As Integer
Dim sOldPath1 As String
Dim intDsnLen As Integer
Dim intDsnStart As Integer
Dim intDsnEnd As Integer

'I first check if there is a folder called "Analysis" as this is what we generally use to store our analysis tools.
If InStr(1, Application.ThisWorkbook.Path, "Analysis") > 0 Then
'I then remove that from the path that I require
sLength = Len(Application.ThisWorkbook.Path) - 9
'I set the new path to the workbook path minus the analysis folder and the "\"
sNewPath = Left(Application.ThisWorkbook.Path, sLength)
Else
'If the folder doesn't exist, I then set the new part to the excel workbooks path
sLength = Len(Application.ThisWorkbook.Path)
sNewPath = Left(Application.ThisWorkbook.Path, sLength)
End If

For Each conn In ActiveWorkbook.Connections
With conn
If .Type = xlConnectionTypeODBC Then
sOldConnection = .ODBCConnection.Connection

'I used these steps to find my specific connection string....not the best way to use it but I was getting
'a bit irritated with the fact that instr doesn't remove all that I don't require.
'In my case, this will always work...may not for others but you can always play around to find your best fit.
intDsnStart = InStr(1, sOldConnection, "DBQ") + 3
intDsnEnd = InStr(intDsnStart, sOldConnection, "Reports.MDB")
intDsnLen = intDsnEnd - intDsnStart
'I get my old path in my old connection..I need this specifically as I want to replace it
sOldPath = Mid(sOldConnection, intDsnStart + 1, intDsnLen - 2)

'Here, I replace the old path with the new path, irrespective of whether it is the same or not in my new connection
sNewConnection = Replace(sOldConnection, _
sOldPath, sNewPath, Compare:=vbTextCompare)
'I set the odbc connection to my new built connection and then refresh
.ODBCConnection.Connection = sNewConnection
.Refresh

End If
End With
Next conn

Set conn = Nothing

'THIS HAS WORKED PERFECTLY FOR ME AND I AM NOW IN THE TESTING PHASE. Hopefully this can make someone else's life
'a little easier if trying to do what I have done. Many thanks for all the assistance received

End Sub

once again...thank you for everything...now to get this code into xlsx. back to researching