Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question
    
You can reference external data in a formula, is that what you're looking to do? You can reference the absolute path to a file. You're looking to reference a relative path to a file? link here you can see how to reference both absolutely and relatively –  Raystafarian Feb 2 '12 at 21:09
    
I'm neither referencing an external Excel file, neither using a formula (just importing the data, using the data Tab. –  gregseth Feb 2 '12 at 23:08
    
So you want a macro to import the data from the csv? –  Raystafarian Feb 2 '12 at 23:24
    
No, not really. I did that with the Excel native function: Data>From text>... and configured my CSV and all. What I want is to be able to change just the path of the file of the already existing datasource. –  gregseth Feb 3 '12 at 7:58
    
Which version of excel are you using? Also can't you go to data -> existing connections -> select connection -> right click edit connection properties -> definition tab -> browse This would enable you to change it, are you looking to automate that process such that you can type in which one you want into say, cell A1 and it would run the macro in reference to what you entered? –  Raystafarian Feb 3 '12 at 11:45

1 Answer 1

up vote 1 down vote accepted

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

share|improve this answer
    
Ok, great! Can't check that now, I'll look into it later on. Thanks. –  gregseth Feb 3 '12 at 14:50

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.