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

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?

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


"Dave O" wrote in message
ups.com...
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?

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.

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.

SS wrote:

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

--

Dave Peterson

Thanks for that but it stops at

Set myBaseFolder = FSO.GetFolder(myFolderName)

Shona

Forget that thanks my mistake put the path wrong!

Thanks again this is great
"SS" wrote in message
...
Thanks for that but it stops at

Set myBaseFolder = FSO.GetFolder(myFolderName)

Shona

Dave,

That's great - I've been looking for a way to do this for a long time too (I have been wanting to write a routine which recurses through a chunk of folders and print all the files in each one *in order* - all 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 properties available in the Scripting.FileSystemObject? I've never found a way to 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 included in Typical Install? Or does it entail buying an expensive reference book from Microsoft Press?

BizMark

There's lots of free info at MS.

VBScript User's Guide
http://msdn.microsoft.com/scripting/...c/vbstutor.htm
http://msdn.microsoft.com/scripting/...oad/vbsdoc.exe
http://msdn.microsoft.com/scripting/...load/jsdoc.exe
http://msdn.microsoft.com/scripting/...ost/wshdoc.exe
http://msdn.microsoft.com/scripting/...ets/wscdoc.exe

(saved from a long time ago.)

And from another post I've kept:

WSH 2.0 Tutorial
http://msdn.microsoft.com/scripting/...utorialTOC.htm
WSH Documentation
http://msdn.microsoft.com/scripting/...ce/default.htm
http://msdn.microsoft.com/scripting/...ost/wshdoc.exe

VBScript User's Guide
http://msdn.microsoft.com/scripting/...c/vbstutor.htm
VBScript Documentation
http://msdn.microsoft.com/scripting/...fo/vbsdocs.htm
http://msdn.microsoft.com/scripting/...oad/vbsdoc.exe

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

JScript User's Guide
http://msdn.microsoft.com/scripting/...UsersGuide.htm
JScript Documentation
http://msdn.microsoft.com/scripting/...nfo/jsdocs.htm
http://msdn.microsoft.com/scripting/...load/jsdoc.exe

WSC Tutorial
http://msdn.microsoft.com/scripting/...c/lettitle.htm
WSC Documentation
http://msdn.microsoft.com/scripting/...serverdocs.htm
http://msdn.microsoft.com/scripting/...ets/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.


BizMark wrote:

Dave,

That's great - I've been looking for a way to do this for a long time
too (I have been wanting to write a routine which recurses through a
chunk of folders and print all the files in each one *in order* - all
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 properties
available in the Scripting.FileSystemObject? I've never found a way to
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 included
in Typical Install? Or does it entail buying an expensive reference
book from Microsoft Press?

BizMark

--
BizMark

--

Dave Peterson

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...o&userid=25561
View this thread: http://www.excelforum.com/showthread...hreadid=394981

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.

TommySzalapski wrote:

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...o&userid=25561
View this thread: http://www.excelforum.com/showthread...hreadid=394981

--

Dave Peterson

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

BM

Dave Peterson wrote in
:

One way:



SS wrote:

Hi

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


Another way would be through the ASAP utilities:

http://www.asap-utilities.com/

Import/Insert files

Oops! That's just doing the filenames.
Anyhow, plenty of useful additions, isn't it?


--

It is I, DeauDeau
(Free after monsieur Leclerc in 'Allo, 'allo)

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

Szalapski


--
TommySzalapski
------------------------------------------------------------------------
TommySzalapski's Profile: http://www.excelforum.com/member.php...o&userid=25561
View this thread: http://www.excelforum.com/showthread...hreadid=394981