File listing macro for excel

[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?

 

[Jezebel]

Why not display BrowseForFolder to select the folder you want? Then your
macro is completely independent of the spreadsheet location.

 

[jim9912]

Well, the short answer is - because I'm a novice and I don't know how
to. Can you give me the details ?

 

[Helmut Weber]

Hi Jim,

first you are in a Word group,
but anyway, you need the active workbook's fullname, like:

sPath = ActiveWorkbook.path
then you append "\parts\" to the path.

If you need help on how to do that, ask again.

Or is there something I don't get?

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

 

[jim9912]

Oops. So sorry for cross posting...

If you can tell me how to do the append, that would be fantastic.

 

[Helmut Weber]

Hi Jim,

sPath = ActiveWorkbook.path
sPath = sPath & "\parts\"

though this method is more appropriate
when building very long strings successively.

Nevertheless, works with short strings as well.

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

 

[jim9912]

Helmut,

THank you for being so patient. i think I may have inserted this into
my code wrong somewhere: I get an error when I do this:

Public Function ShowClearanceSheets1(folderspec)
Dim fso, f, f1, fc, myrow
Set fso = CreateObject("Scripting.FileSystemObject")
Set f = fso.GetFolder(folderspec)
Set fc = f.Files
Target = ActiveWorkbook.Path
Target = sPath & "\parts\"
myrow = 5
For Each f1 In fc
Sheets("Clearance_Sheets").Range("l" & myrow) = f1.Path
myrow = myrow + 1
Next
End Function




Public Sub GetClearanceSheets1()

ShowClearanceSheets1 Target

End Sub

 

[jim9912]

I found the errors and fixed it, thank you for your help, now it works