Home |
Search |
Today's Posts |
#1
![]() |
|||
|
|||
![]()
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
![]() |
|||
|
|||
![]()
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
![]() |
|||
|
|||
![]()
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
![]() |
|||
|
|||
![]()
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Help with data not getting plotted | Charts and Charting in Excel | |||
Fetching External Data from Excel | Excel Discussion (Misc queries) | |||
Pivot Tables referring to external data query | Excel Discussion (Misc queries) | |||
DSUM and other functions on External data | Excel Discussion (Misc queries) | |||
Charting data ranges that change | Charts and Charting in Excel |