Set Excel data connection (csv) with macro
excel, vba
Solution
I know this is an old question but I've been searching for the same thing now and I just finally figured this out. Maybe someone else has said the same, but I haven't found it by searching the Googles...
Lets say you already have these conditions:
- You already have a data connection set up in a work book (let say it's name is MyData in the connection manager
- The destination of the data connection is already defined and is some location in Sheet1
- You have a cell (Say A1 of Sheet2) that has the file name you want to connect to
- You just need to change the path that the connection is looking so that it follows the path of the work book
If this is the case, something like this should do the trick.
Dim fileLoc As String
Dim fileName As String
fileLoc = ThisWorkbook.Path
fileName = Sheet2.Range("A1").Value
Dim conString As String
conString = "TEXT;" & fileLoc & "\" & fileName
Sheet1.QueryTables.Item("MyData").Connection = conString
Feel free to modify or tweek that as your case necessitates.
Problem
I have been searching for a solution to the following problem, but haven't found anything that was really helpful: I have an excel sheet with data connections to a number of csv. Sadly, excel does save the connection as absolute paths. Ideally I would be able to set the path as relative paths, but I would settle for a macro that would allow the user to update the connections depending on `thisworkbook.path` before first use. The project is in a folder d:\project with the excel sheet in d:\project\excel and the csv in d:\project\results. If I would send the project as a zip to some user, and he unzips into c:\my documents\project he will have to reconnect the 10 or so csv. My general idea would be to write a macro along the lines of (no real code, since I'm new to vba, and if I knew the code, I wouldn't have to ask) ``` filepath = thisworkbook.path cons = thisworkbook.connections for each cons filename = cons.filename newpath = filepath & filename end for ```