Hi there.  I have a problem that is driving me nuts...

i want to write an excel macro that will extract the filenames from a
given directory and put them in m spreadsheet.  I am using the
following:

Public Function ShowFileList(folderspec)
Dim fso, f, f1, fc, myrow
Set fso = CreateObject("Scripting.FileSystemObject")
Set f = fso.GetFolder(folderspec)
Set fc = f.Files
myrow = 5
For Each f1 In fc
Sheets("sheet1").Range("f" & myrow) = f1.Name
myrow = myrow + 1
Next
End Function

Public Sub GetFileNames()
ShowFileList "C:\"
End Sub

This works as it is, but I need it to do more:

I need to be able to assign the directory to be listed dynamically
instead of having it as a constant.  THe wrinkle in that is that I
don't know what the absolute path will be, so I have to assign it
relative to the directory that the spreadsheet is in.

For instance, if the path to the spreadsheet is
c:\projects\project123\spreadsheet.xls

and the path to the directory I need listed is
c:\projects\project123\parts\

I need to be able to list the files in "parts" directory, but I also
need to use the macro if the path to the spreadsheet is

c:\projects\project456\spreadsheet.xls

and the path to the directory I need listed is
c:\projects\project456\parts\

The directory name "parts" will always stay the same.  It's parent
directory could be anything, and I will not know it's name in advance,
but it will always be a subdirectory of "projects" and a parent to
"parts".  Also, the drive may be D or E and "projects" may or may not
be right off the root.

So what I really need is a relative path from the spreadsheet.  I tried
using ".\" as current directory, but excel seems to default to the root
as current, not where the spreadsheet is.

Can this be done?