Relative path for "import text file"?

[venablito]

I use the import text file wizard to bring results created by my
statistical package in .txt files into Excel for formatting and
printing. I would like to make the path to the input .txt file
"relative" to the Excel file, but I do not see how to do this in the
wizard.

The problem is the following: suppose I am collaborating with someone
and send him the .xls file and the .txt file imported. If his
directory structure is not exactly the same as mine, Excel cannot find
the .txt file when he wants to refresh, even though the two files are
in the same directory.

For example, suppose I create the file FormattedResults.xls in c:/
venable/research/importantproject, and this imports the file
RawResults.txt, which is saved in the same directory. Now, if I send
both files to my colleague and he saves them to, for example, c:/
research/project on his computer, when he refreshes the data, Excel
cannot find the .txt file.

What I would like to do is make the text import relative to the
current directory, that is, wherever the Excel file is saved, that's
where it will look.

Is this possible?

I apologize if this has been covered before -- I searched but did not
find anything. Also, please let me know if I can clarify what is going
on.

Thanks in advance!

 

[Zone]

I was surprised this wasn't answered. I was sure there would be a worksheet
function to return the workbook's path. However, you can use a user-defined
function to get it. Copy this little function code. Open your workbook,
press Alt-F11 to show the editor, click Insert on the menubar, then Module.
Paste the code in there. Press Alt-F11 to return to spreadsheet view. Pick
an empty cell on the spreadsheet and enter
=mpath()
This will show the workbook's path, then you can refer to the cell to get
the path you want to use. HTH, James

Function mPath() As String
mPath = ThisWorkbook.Path
End Function

 

[Chip Pearson]

In the ThisWorkbook code module (it must be in ThisWorkbook), use

Private Sub Workbook_Open()
ChDrive ThisWorkbook.Path
ChDir ThisWorkbook.Path
End Sub

This will set the default path, as returned by CurDir, to the drive and path
of the workbook.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)