getting filenames

[jim9912]

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?

 

[Bob Phillips]

See response in excel.misc

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[George]

Jim,

I just had something like this and "K Dales" posted back to me saying I
could try this:

ThisWorkbook.Path

This will give you the directory of the current workbook. It's a useful
place to start.

Hope this helps
George

 

[jim9912]

THank you for your help - works fine