Relative paths to external data?

  • Thread starter Christopher Blue
  • Start date
C

Christopher Blue

I know how to import external data from a .csv, but how can I have the path
be relative so I can move the directory around without having to update the
..csv file location manually?
 
D

Dave Peterson

I'm not quite sure what you're doing.

But if you're doing this from a macro, maybe you could just use
application.getopenfilename. It would allow you to browse for your file.

Or if the .csv file is in the same location as the workbook that contains the
macro, you could find the path of that workbook by using

thisworkbook.path

So you could use...

dim myCSVFileName as string
mycsvfilename = thisworkbook.path & "\filename.csv"
 
C

Christopher Blue

I am importing external data by using Data \ Import External Data \ Import
Data...

I then pick the .csv file from the file selection dialog that appears.
However, the location is absoute instead of relative like I would like.
Also, I cannot seem to edit the location of the external data directly (Excel
only gives me another file selection dialog).

Basically if the location of the .csv was relative I could move the
directory where I have the workbook and the .csv anywhere I want. As it
stands, moving the directory breaks the link the workbook has to the .csv.

And the reason I use external data is because I have my own pet program edit
the .csv directly (it's really easy to understand). I have no idea how to
edit an Excel worksheet directly nor would I want to so I use externally
linked data.
 
D

Dave Peterson

I selected a cell in the imported range.
I clicked on Data|Import external data
and I was given the option for "edit text import"

In my simple testing, it looked like xl2002 remembered where I originally got
the data. I'm not sure you can change that behavior.

But maybe you could build a macro that you could run whenever you wanted to
refresh your data.

Just record it while you do it manually.

I got something like:

Option Explicit
Sub Macro1()
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\My Documents\excel\book1.csv", Destination:=Range("A1"))
.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 = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub

You could modify it slightly:

Option Explicit
Sub Macro1A()

Dim MyFileName as string
myfilename = thisworkbook.path & "\my.csv"

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & MyFileName, Destination:=Range("A1"))

'rest of recorded code.
end sub
 
Top