List folders to file

S

SS

Hi

Does anyone have a macro that would list all the folders (with path) and
subfolders to an excel sheet but not the files?

Thanks
Shona
 
D

Dave O

Shona-
There's a function called CELL() that should return your filename (or
any of a number of parameters) but I can't get it to work properly on
my machine, so I can't make it work for you. The syntax is
=CELL("filename") which ought to return the path and filename of the
current file, and which you could then trim down to show just the path.

Can anyone provide some guidance here?
 
S

Steve Yandl

Dave,

If it isn't working for you, it probably means that you've not saved the
file yet.

I read Shona's question a bit differently. I think the goal is to populate
a worksheet with the folder structure of some drive, similar to a Windows
Explorer type display without listing files.

Steve
 
D

Dave O

Thanks, Steve, you were right about not saving the file.

I looked into the DIR function in VB Help, which indicates that DIR may
not be called recursively. So I'm fresh out of answers.
 
D

Dave Peterson

One way:

Option Explicit
Dim myRow As Long
Dim wks As Worksheet
Sub testme()
Set wks = Worksheets.Add
myRow = 0
Call FoldersInFolder("C:\my documents") '<-- change this
End Sub
Sub FoldersInFolder(myFolderName As String)

' Dim FSO As Scripting.FileSystemObject
' Dim myBaseFolder As Scripting.Folder
' Dim myFolder As Scripting.Folder
' Set FSO = New Scripting.FileSystemObject

Dim FSO As Object
Dim myBaseFolder As Object
Dim myFolder As Object
Set FSO = CreateObject("scripting.filesystemobject")

Set myBaseFolder = FSO.GetFolder(myFolderName)

For Each myFolder In myBaseFolder.SubFolders
myRow = myRow + 1
wks.Cells(myRow, "A").Value = myFolder.Path
Call FoldersInFolder(myFolder.Path)
Next myFolder

End Sub

I commented out some specific Dim statements. If you want to use those (instead
of the As Object lines), you'll have to set a reference to "microsoft scripting
library" via tools|References.

Using the references makes coding/debugging easier--you get that nice
intellisense feature from the VBE.
 
B

BizMark

Dave,

That's great - I've been looking for a way to do this for a long tim
too (I have been wanting to write a routine which recurses through
chunk of folders and print all the files in each one *in order* - al
that remains now is seeing if I can access the standard Windows 'File
Print' function or DDE message using SHELL or some such).

Does anybody else dread trying to find out the methods and propertie
available in the Scripting.FileSystemObject? I've never found a way t
browse the methods of referenced objects or display help on them.

Is there a way, or is it in some optional help file that's not include
in Typical Install? Or does it entail buying an expensive referenc
book from Microsoft Press?

BizMar
 
D

Dave Peterson

There's lots of free info at MS.

VBScript User's Guide
http://msdn.microsoft.com/scripting/vbscript/doc/vbstutor.htm
http://msdn.microsoft.com/scripting/vbscript/download/vbsdoc.exe
http://msdn.microsoft.com/scripting/jscript/download/jsdoc.exe
http://msdn.microsoft.com/scripting/windowshost/wshdoc.exe
http://msdn.microsoft.com/scripting/scriptlets/wscdoc.exe

(saved from a long time ago.)

And from another post I've kept:

WSH 2.0 Tutorial
http://msdn.microsoft.com/scripting/windowshost/doc/wsTutorialTOC.htm
WSH Documentation
http://msdn.microsoft.com/scripting/windowshost/docs/reference/default.htm
http://msdn.microsoft.com/scripting/windowshost/wshdoc.exe

VBScript User's Guide
http://msdn.microsoft.com/scripting/vbscript/doc/vbstutor.htm
VBScript Documentation
http://msdn.microsoft.com/scripting/vbscript/techinfo/vbsdocs.htm
http://msdn.microsoft.com/scripting/vbscript/download/vbsdoc.exe

FileSystemObject User's Guide
http://msdn.microsoft.com/scripting/vbscript/doc/jsFSOTutor.htm
VBScript Run-Time Library Reference [FileSystemObject/Dictionary]
http://msdn.microsoft.com/scripting/vbscript/doc/VBSFSOTOC.htm

JScript User's Guide
http://msdn.microsoft.com/scripting/jscript/doc/jsconJScriptUsersGuide.htm
JScript Documentation
http://msdn.microsoft.com/scripting/jscript/techinfo/jsdocs.htm
http://msdn.microsoft.com/scripting/jscript/download/jsdoc.exe

WSC Tutorial
http://msdn.microsoft.com/scripting/scriptlets/doc/lettitle.htm
WSC Documentation
http://msdn.microsoft.com/scripting/scriptlets/serverdocs.htm
http://msdn.microsoft.com/scripting/scriptlets/wscdoc.exe


In fact, there are newsgroups that are devoted to scripting. You could search
google for common questions and post questions when you can't find answers.
 
T

TommySzalapski

Wow, guys, just use the DOS command prompt.

from the C:\> prompt

dir/s/b > directory.txt 'This will put all the folders and filenames in
a txt file (which Excel reads just fine)
dir/s/b/ad > directory.txt 'This will send just the folders and not the
filenames.
Type dir /? for even more options.
A single > overwrites anything that is in the file.
A double >> appends the new data to the end of the file (directory.txt
is just an arbitrary file name you can call it anything you want)

Sure this is not done from excel, but it is much simpler.

Szalapski
 
D

Dave Peterson

I think "simpler" depends on how often you have to do it and who's gonna do the
work. And if it's part of a larger mechanized routine, then you'd have to
start, stop, do manual effort and restart.

Once you set up the macro, you could be done--just rerun it when you want.

You won't have to get to the command prompt or import the data.
 
B

BizMark

TommySzalapski said:
Wow, guys, just use the DOS command prompt.

from the C:\ prompt

dir/s/b directory.txt 'This will put all the folders and filenames in
a txt file (which Excel reads just fine)
dir/s/b/ad directory.txt 'This will send just the folders and not the
filenames.
Type dir /? for even more options.
A single overwrites anything that is in the file.
A double appends the new data to the end of the file (directory.txt
is just an arbitrary file name you can call it anything you want)

Sure this is not done from excel, but it is much simpler.

Szalapski


--
TommySzalapski
------------------------------------------------------------------------
TommySzalapski's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=25561
View this thread:
http://www.excelforum.com/showthread.php?threadid=394981
Click to expand...


I think the point was, Tommy, that a method was needed that IS done
from Excel!!!

BM
 
T

TommySzalapski

Alright, use (in Excel) the VBA command SHELL. You can then run th
command prompt script from excel and have the best of both worlds.

Szalapsk
 
Top