Relative file paths for Excel Data Connections

Question

I have 7 csv files and one xls file. In the xls file I have 7 sheets (one for each csv file). I can create a data connection that will read each csv file into a given sheet. However, when I zip up the xls, and 7 csv files and send them to someone they are not able to open see the data in the xls file becuase it is trying to access the files on my computer (C:/Desktop/MyComputerName/file.csv). Is it possible to make this link relative? Is there a different way to access the csv file's content without doing it cell by cell?

Answer 1

According to further research it's not possible without writing a VBA/macro script using the workbook path.

Answer 2

Create a macro enabled excel workbook(.xlsm), save it in a location and add the following macro:

Sub Auto_Open()
    ' Clean current connections and query tables
    ' https://stackoverflow.com/a/49135238/213871
    Dim cn
    Dim qt As QueryTable
    Dim ws As Worksheet
    For Each cn In ThisWorkbook.Connections
        cn.Delete
    Next
    For Each ws In ThisWorkbook.Worksheets
        For Each qt In ws.QueryTables
            qt.Delete
        Next
    Next ws

    ' Clear all contents except header row
    Rows("2:" & Rows.Count).ClearContents

    ' Add the connection to the csv file in the same folder
    ' Inspired from https://stackoverflow.com/a/40536980/213871

    Dim csvFileName As String
    csvFileName = "DatabaseView.csv"

    Dim filePath As String
    filePath = ActiveWorkbook.Path

    Dim conString As String
    conString = "TEXT;" & filePath & "\" & csvFileName

    ' Add the connection to DataBaseView.csv
    With ActiveSheet.QueryTables.Add(Connection:= _
        conString _
        , Destination:=Range("$A$2"))
        .Name = "DatabaseView"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = False
        .RefreshPeriod = 1
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 850
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = True
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

Tweak csv file, range (currently it's "$A$2") and delimiters options (you can record a macro while you add import from UI to get a template). Auto_Open() macro will cause it to load it at file startup. Tested with Excel 2010.