software

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(quot;Scripting.FileSystemObjectquot;)
Set f = fso.GetFolder(folderspec)
Set fc = f.Files
myrow = 5
For Each f1 In fc
Sheets(quot;sheet1quot;).Range(quot;fquot; amp; myrow) = f1.Name
myrow = myrow 1
Next
End FunctionPublic Sub GetFileNames()
ShowFileList quot;C:\quot;
End SubThis works as it is, but I need it to do moI 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.xlsand the path to the directory I need listed is
c:\projects\project123\parts\I need to be able to list the files in quot;partsquot; directory, but I also
need to use the macro if the path to the spreadsheet isc:\projects\project456\spreadsheet.xlsand the path to the directory I need listed is
c:\projects\project456\parts\The directory name quot;partsquot; 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 quot;projectsquot; and a parent to
quot;partsquot;. Also, the drive may be D or E and quot;projectsquot; may or may not
be right off the root.So what I really need is a relative path from the spreadsheet. I tried

using quot;.\quot; 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 amp; quot;\partsquot;

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;jim9912quot; gt; wrote in message oups.com...
gt; Hi there. I have a problem that is driving me nuts...
gt;
gt; i want to write an excel macro that will extract the filenames from a
gt; given directory and put them in m spreadsheet. I am using the
gt; following:
gt;
gt;
gt; Public Function ShowFileList(folderspec)
gt; Dim fso, f, f1, fc, myrow
gt; Set fso = CreateObject(quot;Scripting.FileSystemObjectquot;)
gt; Set f = fso.GetFolder(folderspec)
gt; Set fc = f.Files
gt; myrow = 5
gt; For Each f1 In fc
gt; Sheets(quot;sheet1quot;).Range(quot;fquot; amp; myrow) = f1.Name
gt; myrow = myrow 1
gt; Next
gt; End Function
gt;
gt;
gt; Public Sub GetFileNames()
gt; ShowFileList quot;C:\quot;
gt; End Sub
gt;
gt;
gt; This works as it is, but I need it to do mo
gt;
gt;
gt; I need to be able to assign the directory to be listed dynamically
gt; instead of having it as a constant. THe wrinkle in that is that I
gt; don't know what the absolute path will be, so I have to assign it
gt; relative to the directory that the spreadsheet is in.
gt;
gt;
gt; For instance, if the path to the spreadsheet is
gt; c:\projects\project123\spreadsheet.xls
gt;
gt;
gt; and the path to the directory I need listed is
gt; c:\projects\project123\parts\
gt;
gt;
gt; I need to be able to list the files in quot;partsquot; directory, but I also
gt; need to use the macro if the path to the spreadsheet is
gt;
gt;
gt; c:\projects\project456\spreadsheet.xls
gt;
gt;
gt; and the path to the directory I need listed is
gt; c:\projects\project456\parts\
gt;
gt;
gt; The directory name quot;partsquot; will always stay the same. It's parent
gt; directory could be anything, and I will not know it's name in advance,
gt; but it will always be a subdirectory of quot;projectsquot; and a parent to
gt; quot;partsquot;. Also, the drive may be D or E and quot;projectsquot; may or may not
gt; be right off the root.
gt;
gt;
gt; So what I really need is a relative path from the spreadsheet. I tried
gt;
gt; using quot;.\quot; as current directory, but excel seems to default to the root
gt;
gt; as current, not where the spreadsheet is.
gt;
gt;
gt; Can this be done?
gt;
This worked for me. Thank you VERY much!