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 mo


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?

Use

Activeworkbook.Path & "\parts"

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"jim9912" wrote in message
oups.com...
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 mo


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?

This worked for me. Thank you VERY much!