Relative instead of Absolute paths in Excel VBA

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.

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

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

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

ActiveWorkbook.Path
ThisWorkbook.Path
App.Path

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

if current directory of the operating system is the path of the workbook you are using, Workbooks.Open FileName:= "TRICATEndurance Summary.html" would suffice. if you are making calculations with the path, you can refer to current directory as . and then \ to tell the file is in that dir, and in case you have to change the os's current directory to your workbook's path, you can use ChDrive and ChDir to do so.

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