Set Excel Data Connection (csv) with macro

Question

I was looking for a solution to the following problem, but did not find anything useful: I have an excel sheet with data connection to the csv series. Unfortunately, excel really saves the connection as absolute paths. Ideally, I could set the path as relative paths, but I would agree to a macro that will allow the user to update connections depending on thisworkbook.path before first use.

The project is located in the d: \ project folder with the excel sheet in d: \ project \ excel and csv in d: \ project \ results. If I send the project as a zip to some user and it decompresses into c: \ my documents \ project, he will have to connect 10 or so csv.

My general idea would be to write a macro line by line (no real code, since I am new to vba, and if I knew the code, I would not ask)

 filepath = thisworkbook.path cons = thisworkbook.connections for each cons filename = cons.filename newpath = filepath & filename end for 

Answer 1

I know this is an old question, but now I was looking for the same thing, and I finally figured it out. Maybe someone said the same thing, but I did not find it by doing a search on Google ...

Suppose you already have the following conditions:

• You already have a data connection set up in the workbook (let's say this is the name MyData in Connection Manager
• The purpose of the data connection is already defined and located in Sheet 1
• You have a cell (Say A1 of Sheet2) that has the name of the file you want to connect to
• You just need to change the path that the connection is looking for so that it follows the workbook path

If so, 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 fake what is necessary for your case.

Answer 2

You can access a connection path like this

 Sub UpdateConnections() Dim con As WorkbookConnection Dim ConString As String For Each con In ThisWorkbook.Connections ConString = con.Ranges.Item(1).QueryTable.Connection ' Path update code here Next End Sub 

For a text data source, a string of the type "TEXT;C:\My\Path\Documents\FileName.csv" is returned

During testing, I found that changing the path also affected some other properties, so you probably need to reset the set of properties after changing the path.

Answer 3

Thanks for the help, in the end I came to the following:

 Sub UpdateAllConnections() For Each cn In ThisWorkbook.Connections cn.Delete Next cn Dim arrConNames(1) As String Dim arrSheetNames(1) As String arrConNames(0) = "test1.csv" arrConNames(1) = "test2.csv" arrSheetNames(0) = "test1" arrSheetNames(1) = "test2" Dim indCon As Integer For indCon = LBound(arrSheetNames) To UBound(arrSheetNames) UpdateConnections arrConNames(indCon), arrSheetNames(indCon) Next End Sub Sub UpdateConnections(ConName As String, SheetName As String) FilePath = ThisWorkbook.Path ResultPath = Replace(FilePath, "Excel-Shell", "Results") ThisWorkbook.Worksheets(SheetName).Select ActiveSheet.Cells.Clear With ActiveSheet.QueryTables.Add(Connection:= _ "TEXT;" & ResultPath & "\" & ConName, Destination:=Range( _ "$A$1")) .Name = ConName .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .TextFilePromptOnRefresh = False .TextFilePlatform = 850 .TextFileStartRow = 1 .TextFileParseType = xlDelimited .TextFileTextQualifier = xlTextQualifierDoubleQuote .TextFileConsecutiveDelimiter = False .TextFileTabDelimiter = False .TextFileSemicolonDelimiter = True .TextFileCommaDelimiter = False .TextFileSpaceDelimiter = False .TextFileColumnDataTypes = Array(1, 1) .TextFileTrailingMinusNumbers = True .Refresh BackgroundQuery:=False End With End Sub