title: Set Excel data connection (csv) with macro
body: <p>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 <code>thisworkbook.path</code> before first use. </p>
  
  <p>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. </p>
  
  <p>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)</p>
  
  <pre><code>filepath = thisworkbook.path
  cons = thisworkbook.connections
  for each cons
     filename = cons.filename
     newpath = filepath &amp; filename
  end for
  </code></pre>