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]

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]

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

 

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

 

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

 

[Dodo]

One way:

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?

 

[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