+ Reply to Thread
Results 1 to 4 of 4

Relative paths to external data?

  1. #1
    Christopher Blue
    Guest

    Relative paths to external data?

    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?

  2. #2
    Dave Peterson
    Guest

    Re: Relative paths to external data?

    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"



    Christopher Blue wrote:
    >
    > 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?


    --

    Dave Peterson

  3. #3
    Christopher Blue
    Guest

    Re: Relative paths to external data?

    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.

    "Dave Peterson" wrote:

    > 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"
    >
    >
    >
    > Christopher Blue wrote:
    > >
    > > 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?

    >
    > --
    >
    > Dave Peterson
    >


  4. #4
    Dave Peterson
    Guest

    Re: Relative paths to external data?

    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


    Christopher Blue wrote:
    >
    > 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.
    >
    > "Dave Peterson" wrote:
    >
    > > 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"
    > >
    > >
    > >
    > > Christopher Blue wrote:
    > > >
    > > > 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?

    > >
    > > --
    > >
    > > Dave Peterson
    > >


    --

    Dave Peterson

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1