List folders to file

[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

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

[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


"Dave O" <[email protected]> wrote in message
news:[email protected]...
> 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 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.

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

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

[SS]

Thanks for that but it stops at

Set myBaseFolder = FSO.GetFolder(myFolderName)

Shona

[SS]

Forget that thanks my mistake put the path wrong!

Thanks again this is great
"SS" <[email protected]> wrote in message
news:[email protected]...
> Thanks for that but it stops at
>
> Set myBaseFolder = FSO.GetFolder(myFolderName)
>
> Shona
>
>

[BizMark]

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]

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

[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

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

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

[BizMark]

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


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

BM


--
BizMark

[Dodo]

Dave Peterson <[email protected]> wrote in
news:[email protected]:

> 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)

[TommySzalapski]

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

[sjgidman1982]

i've used the macro code shown and it worked great, now can anyone tell me how would use code to just display the first layer of subdirectory folders and not the second, third etc

e.g
Start directory is c:\

GOOD
c:\program files
c:\windows
c:\documents

BAD
c:\program files\nero
c:\windows\system
c:\documents\administrator


And how do i do the reverse, from a list in excel lets say a list of 1000 separate values create a folder for each value.

[VBA Noob]

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.