Excel: change data source with macro

Question

I'm using a CSV file (generated by a thing party program) as an external datasource, and I'd like to change its path from a macro... How can I do that ?

Another acceptable answer to my problem would be a way to specify a path relative to the excel file—haven't found how—given that in my case, both the csv and the xls are in the same directory.

Answer 1

Sub Import() ' ' Import Macro '

Dim FilePath As String
FilePath = "TEXT;C:\directory\" & Cells(1, 1).Value
ActiveWorkbook.Worksheets.Add
With ActiveSheet.QueryTables.Add(Connection:=FilePath, Destination:=Range("$A$1"))
    .Name = "Book1"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .TextFilePromptOnRefresh = False
    .TextFilePlatform = 437
    .TextFileStartRow = 1
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimiter = False
    .TextFileTabDelimiter = False
    .TextFileSemicolonDelimiter = False
    .TextFileCommaDelimiter = True
    .TextFileSpaceDelimiter = False
    .TextFileColumnDataTypes = Array(1, 1)
    .TextFileTrailingMinusNumbers = True
    .Refresh BackgroundQuery:=False
End With

End Sub

This will create a new sheet with the data when you put the file (book3.csv for instance) in sheet1 A1; you change C:\directory to the directory in which your input exists and you can change the range of output in line 5 "Range"

Additionally, line 3 "Cells(1,1) indicates the input cell, so you can change that from Sheet1 A1 if you'd like. You need to indicate the file extension in the reference cell otherwise it won't know what file to import. Just run this macro and you'll get a new sheet with the target data. If you need a new workbook or for it to insert it into an existing sheet, let me know.

Also, if you look at the properties this is a comma delimited file, you can change that, or just record your own macro (developer tab -> record macro) and insert the FilePath type commands at the top and change the connection command