Relative instead of Absolute paths in Excel VBA

Question

I have written an Excel VBA macro which imports data from a HTML file (stored locally) before performing calculations on the data.

At the moment the HTML file is referred to with an absolute path:

Workbooks.Open FileName:="C:\Documents and Settings\Senior Caterer\My Documents\Endurance Calculation\TRICATEndurance Summary.html"

However I want to use a relative path to refer to it as opposed to absolute (this is because I want to distribute the spreadsheet to colleagues who might not use the same folder structure). As the html file and the excel spreadsheet sit in the same folder I would not have thought this would be difficult, however I am just completely unable to do it. I have searched on the web and the suggested solutions have all appeared very complicated.

I am using Excel 2000 and 2002 at work, but as I plan to distribute it I would want it to work with as many versions of Excel as possible.

Any suggestions gratefully received.

Answer 1

You could use one of these for the relative path root:

ActiveWorkbook.Path
ThisWorkbook.Path
App.Path

Answer 2

I think the problem is that opening the file without a path will only work if your "current directory" is set correctly.

Try typing "Debug.Print CurDir" in the Immediate Window - that should show the location for your default files as set in Tools...Options.

I'm not sure I'm completely happy with it, perhaps because it's somewhat of a legacy VB command, but you could do this:

ChDir ThisWorkbook.Path

I think I'd prefer to use ThisWorkbook.Path to construct a path to the HTML file. I'm a big fan of the FileSystemObject in the Scripting Runtime (which always seems to be installed), so I'd be happier to do something like this (after setting a reference to Microsoft Scripting Runtime):

Const HTML_FILE_NAME As String = "my_input.html"

With New FileSystemObject
    With .OpenTextFile(.BuildPath(ThisWorkbook.Path, HTML_FILE_NAME), ForReading)
        ' Now we have a TextStream object that we can use to read the file
    End With
End With

Answer 3

Just to clarify what yalestar said, this will give you the relative path:

Workbooks.Open FileName:= ThisWorkbook.Path & "\TRICATEndurance Summary.html"