Change connection source path/filename

[DannyPt]

Hi guys,

I am trying to create a macro that change the connection direction after searching for a file that starts with "filename", for example, and the xtension for it is .xlsx and also the path will change because it goes into the C:\Users"username"\SharePoint\ folder and the username is always changing, since this is for more than one person.

I have tryed something like this:

Private Sub FileSearch()
Dim qPath As String

qPath = Environ("Sharepoint") & "Folder\Subfolder\filename" & "*.xlsx"
'strPath = Application.GetOpenFilename(Title:=qPath, FileFilter:=".xlsx")
ActiveWorkbook.Connections.Item(1).OLEDBConnection.SourceConnectionFile = qPath

End Sub

It gives me no error, but i know that it isn't updating the connection because i tryed to change the file to filename1.xlsx and the connection is still going to filename.xlsx

I don't know if i am beeing cleary here because i know that is a little bit confuse.

Thanks

 

[DannyPt]

I tryed to edit this post so i could do a little update but i dodnt found the "Edit" option

I noticed that the previous code would change the "Connection File" on the Connection Properties, but in the "Connection string" there isstill the previous file.

Also, the file that appear in the "Connection File" ont he Connection Properties it is "Folder\Subfolder\filename*.xlsx", so i would say that it is doing a simple print of the file to the connection file but, since that file doesn't exist, it doesn't change the Connection String to the one i want.

So, for the folder issue, i used the ThisWoorkbook.Path and then add the subfolder, since the woorkbook is in the main folder

 

[mole999]

Edit is limited by length of time on the site, and then on 10 mins max.