List folders to file

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


0
Student.2 (9)
8/11/2005 2:40:24 PM
excel.misc 78881 articles. 5 followers. Follow

13 Replies
913 Views

Similar Articles

[PageSpeed] 59

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?

0
CycleZen (674)
8/11/2005 3:59:45 PM
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" <CycleZen@yahoo.com> wrote in message 
news:1123775985.514121.79250@f14g2000cwb.googlegroups.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?
> 


0
8/11/2005 4:41:11 PM
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.

0
CycleZen (674)
8/11/2005 5:13:21 PM
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
0
petersod (12004)
8/11/2005 6:24:55 PM
Thanks for that but it stops at

    Set myBaseFolder = FSO.GetFolder(myFolderName)

   Shona


0
Student.2 (9)
8/12/2005 6:55:10 AM
Forget that thanks my mistake put the path wrong!

Thanks again this is great
"SS" <Student.2@uk.bosch.com> wrote in message
news:ddhh4g$6cv$1@ns2.fe.internet.bosch.com...
> Thanks for that but it stops at
>
>     Set myBaseFolder = FSO.GetFolder(myFolderName)
>
>    Shona
>
>


0
Student.2 (9)
8/12/2005 7:06:53 AM
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

--
BizMark
0
8/12/2005 1:43:38 PM
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.


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
0
petersod (12004)
8/12/2005 11:09:42 PM
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

0
8/13/2005 1:42:13 AM
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?action=getinfo&userid=25561
> View this thread: http://www.excelforum.com/showthread.php?threadid=394981

-- 

Dave Peterson
0
petersod (12004)
8/13/2005 11:51:22 AM
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?action=getinfo&userid=25561
> View this thread:
> http://www.excelforum.com/showthread.php?threadid=394981


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

BM


-- 
BizMark
0
8/13/2005 1:06:17 PM
Dave Peterson <petersod@verizonXSPAM.net> wrote in
news:42FB97F7.63D84EF6@verizonXSPAM.net: 

> 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)
0
Dodo
8/13/2005 7:56:10 PM
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

--
TommySzalapsk
-----------------------------------------------------------------------
TommySzalapski's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2556
View this thread: http://www.excelforum.com/showthread.php?threadid=39498

0
8/28/2005 6:44:22 PM
Reply:

Similar Artilces:

pulling a list of hyperlinks from a worksheet
I have a worksheet with over 800 cells. Each cell displays a churches website url. I need to display the actual hyperlink http://www.whatever.com not the Link name. example First Baptist Church Nashville. I need the hyperlink http://fbcnashville.org. displayed in the next cell. 'Here's a short macro: 'Note that this assumes you are not using the function HYPERLINK, but just an 'embedded hyperlink 'To install, right click on sheet tab, view code, paste the following in. Sub ListHyperlinks() i = 1 For Each h In Me.Hyperlinks 'Generates list in column...

Writing Data to an External File
Hello all, I have an Excel file (userfile.xls) that users make a copy and use it to make analysis. I want to track who uses it and the date that uses the file. I need some help with the code, and the code will be part of Workbook_Open code and will execute when the userfile.xls file is opened. I am looking for the code that will put the username in Cell A1 of Sheet1 in the userfile.xls file. Then it will populate the username and the date (e.g. now()) into an external Excel tracking file (trackfile.xls) under columns A and B for username and date, respectively. If C...

Linking to CSV text files is it safe
Hi If I link to a CSV ( comma seperated value) file does access think of this as a table with records and behave the same way. I was wondering what would happen if you had say 5000 records in a text file and each record had 50 fields Would it be slower than if the 5000 records were in an Access Table. Can anybody give me some info on problems or limits on this type of action. An example of potential problem maybe and could be if the computer over writes the text file with updated data while the database is reading records from it. Any comments most welcome Steve - From a land down under ...

How do I make it stop asking "This file was created..."
Hello all, I have a situation where the client is using Office 97. One of our team used 2000 or later to work on the application. I have 97 loaded at home. Every time I save it it warns about the features that may be lost. How do I save it so that when we deliver it it does not continually trouble the client with this message? Thank You, -plh -- I keep hitting "Esc" -- but I'm still here! Don't save a 97. Just SAVE -- Don Guillett SalesAid Software dguillett1@austin.rr.com "plh" <plh_member@newsguy.com> wrote in message news:e2de7l02lhk@drn.newsguy.com....

Emptying Deleted Folder
My deleted folder has over 4000 emails in it, and 400 of them were not opened. I cannot open the folder, and when I select "empty deleted items", I get an error message stating some of the email cannot be deleted, and kicks me out. Thanks Rich Neely <anonymous@discussions.microsoft.com> wrote: > My deleted folder has over 4000 emails in it, and 400 of > them were not opened. I cannot open the folder, and when > I select "empty deleted items", I get an error message > stating some of the email cannot be deleted, and kicks me > out. Thanks If I w...

Pivot Table Drop-down list
I have written a spreadsheet which queries external data and places it in a worksheet list which also contains various extra formulas. This query includes a criteria that states that the data must match a particular year-period (e.g. period 4) A Pivottable looks at this worksheet list and displays the data. I have recently copied the workbook and altered the query so that only data from period 5 is listed. However, the Pivottable dropdown still contains the datalist from period 4 as well. Is there any way I can just display the data called by the query, as even for period 5 the list run...

Public Folder Problem #4
We have a public folder set up on our Exchange 5.5 server that handles all of our web requests off of our website. There are a few users who go through these emails and either delete them because they are garbage or forward them to the appropriate user who needs to see the email. The problem lies in the fact that when an email from this public folder is forwarded or repied to, you do not get the normal mail icon that shows that the email was forwarded or replied to. Also, many of the email come in as read even though they were not. This applies to using a preview pane and also not us...

553 sorry, that domain isn't in my list of allowed
553 sorry, that domain isn't in my list of allowed rcpthosts (#5.7.1) What does that mean and how do I fix it? I can send mail to myself but no one else. I just recently changed back from AOL dial up to Broadband. Did I do something with my Outlook settings when I was using AOL? Check your account settings - you may need to authenticate to your outbound, SMTP server. Your ISP can verify that for you. -- PATRICK REED [Outlook - MVP]~~~~~~ -Microsoft Certified Professional (MCP) -Have you checked http://www.slipstick.com? -Please post your Outlook version! "Bobbie"...

How to Printi Font List?
I want to print a list of fonts from Publisher that I can have for a resource when creating literature so that choosing the right font won't be so time consuming. Can anyone tell me how to print a font list from Publisher? I am running out of time for several projects. plzzzzzzzzz help!!! Chinastar <Chinastar@discussions.microsoft.com> was very recently heard to utter: > I want to print a list of fonts from Publisher that I can have for a > resource when creating literature so that choosing the right font > won't be so time consuming. Can anyone tell me how to pr...

How do I change the default # of copies to 1 in a specific file?
I have a file created by another user that is defaulting to print 7 copies instead of 1. How can I change this default back for this file only? Thank you! You might find something under Page Setup, Options. This varies with the printer driver but in my case I found the copies controlled under Advanced options for my driver. -- Jim "Brenda" <Brenda@discussions.microsoft.com> wrote in message news:844371DA-E283-4D3D-ADFB-8A53B098C496@microsoft.com... |I have a file created by another user that is defaulting to print 7 copies | instead of 1. How can I change this default...

External Global Address List (LDAP) access
We have some Macintosh Entourage users here that would like to be able to access the Global Address List from off site. From what I've read and understand, this requires opening up port 3268 to a server with a Global Catalog (i.e. a DC). This seems like a Bad Idea. Is there any other way of doing this (short of a full VPN)? It is a bad idea. You are opening your AD environment to the world. There are 3rd party products that could expose this via the web. http://www.webactivedirectory.com/ Nue "Aaron" <Aaron.Smith@kzoo.edu> wrote in message news:1146163391.410609.13...

Can't access global access list
Hi, We are in the process of migrating from exchange 5.5 to 2003. We installed the exchange 2003 onto the same 5.5 site and are manually moving the users mailbox from 5.5 to 2003. for some reason, the people who are on the exchange 2003 can not access the global access list. Does anyone know the solutions to this? what do you mean by "cannot access"? when they select it in Outlook, what happens? do they just not see any entries, or is there an error? do you have the ADC installed and running? "It" <It@discussions.microsoft.com> wrote in message news:C5E9...

Public Folders lookup issue
Hello, we have exchnage 2003 env that has 3 dedicated public folders. When the public folders server that all the mailstores point becomes available, users freeze when they open outlook or get prompted to open outlook in safe mode. Is there a way to load balance this or maybe tweak the time outlook clients takes to lookup the first public folder server? Thanks in advance ...

problems with the .bsc file
Hi, I have a project that has a strange behavior. I turned on both options "generate browse info" and "build browse info file" for this project. If I hit the F12 key for "Go to definition" over a variable, I get a dialog box telling me that "Browse information is not available for this project. Do you want your build setting altered (if necessary) and your project rebuilt to generate browse info?". After hitting "yes", sometimes another dialog box says "the project's .bsc ... cannot open file. File not found". And it ...

bringing a Publisher 2000 file into 2003
I brought in a file created in Publisher 2000 and the charts (pie, bar, etc.) now have values that are printing on top of each other, stretch as if justified (they aren't), and just look different. Same printer, font, size as before. Tried inserting as picture, same results. What do I need to do to get them looking like they were in 2000? I'm sure there is an explanation but I can't find it in help. Thanks in advance for any and all help. How large is the file? Can you send it to me so I can take a look? Is the text wrapping correctly? Select a picture, right-click, format p...

Removing an Individual Folder from the Folder list
How do you remove Personal Folders that appear in my OL2000 folder list, if they do not appear in the Exchange server properties list? The following message appears, when I try to delete the folders: The file G:\personal folders\personal folders 2003.pst could not be found. I would like to remove the folder from the folder list. Can you see the folder in the Personal Folder or Inbox drop down lists when you open Outlook? If so, click on it, then hit the Delete key. ----- lisa wrote: ----- How do you remove Personal Folders that appear in my OL2000 folder list...

Save & Save As features in file menu of Excel
The save button and both "save" & "save as" options in file dropdown menu are not highlighted in Excel 2003. How do i restore the capability to save my work in Excel. Only way I can do it now is to close the program and wait for a prompt to save the file. I double checked to see if any features were inadvertently disabled on the "About Microsoft Excel" help tab, nothing was listed. Hi see your other post -- Regards Frank Kabel Frankfurt, Germany Blue wrote: > The save button and both "save" & "save as" options in file dro...

Permissions set on Word files differ from other permissions
We have a peer-to-peer network in Windows XP Pro. On ONE of our computers, Word documents do not get the same permissions for sharing that all other files get, or that all files on all other computers get. As a result, we can't copy Word files from this one computer. That computer is set to give "Everyone" "Full Control"; yet the individual Word files keep getting set to NOT give "Everyone" "Full Control". Why would Word override the other security settings, and how can I make it stop? ...

restore public folders
When the recovered deleted items option is not set in Exchange 2000, what is the best way to restore a particular public folder contents? Will we have to build another Exchange server on a fake forest and restore our back up to that, then empty what we need to a .pst and then manually load that info back into our normal Exchange environment? Any help is appreciated. Thank you. Unless you have a backup system with brick-layer support, that is indeed the method to follow. Jason Morrow wrote: > When the recovered deleted items option is not set in Exchange 2000, what is > the b...

Public Folders #5
While in Looking at the Inbox I know how many New Messeges are there by the (#) Indicator to the right of the Inbox. How do i Show Items in the Public folder to do the Same thing? ...

File details etc.
Hello, In windows explorer (XP) you can rightclick the columnbar for fileproperties and display things like: title, subject,owner etc. Which function can i use to write or read those file values ? I looked at MSDN and googled a lot , but could not find anything usefull. Please help. here's an article that'll help you. http://www.codeproject.com/file/SummInfoPropSetFile.asp -SM On 30 sep, 21:11, Seetharam <smi...@gmail.com> wrote: > here's an article that'll help you. > > http://www.codeproject.com/file/SummInfoPropSetFile.asp > > -SM Thanks! It w...

How do I make a new mail list from sent mail?
I've read through many of the posts possibly related to this subject o OutlookForum. I read through my outlook manual and asked everyone know. I'm using Outlook 2000 and I'm not sure if it is even possible Does anyone know how to do this? Again, I'm trying to make a ne mailing list from my sent items. I'm attmepting to do a mail- out t all the people I've e-mailed over the past 3 months, about 700 member of my site. Any help would be greatly appreciated ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post use...

open pdf files in publisher
I have sent someone a zipped pdf document which I produced and which has been sent to others without problems. A new person however is receiving the message 'cannot open file.Not a publisher file.' The file I sent unzipped and they could read it, but I need to send a lot of larger files so need to zip them. They are using aol - which usually unzips automatically. What can be the problem Hope someone can help Hillobeans A small child turns to Ed, and exclaims: "Look! Look! A post from Hillobeans <Hillobeans@discussions.microsoft.com>!"... > I have sent ...

!!!!error adding file to note, 80004005
Hi, currently we are getting an error trying to add and read a file from notes in every area in CRM. The errro was hard to find, but as it appeared the code of an error is 0x80004005. Trying to download on attach a file to note ends with information: "There was a problem trying to connect the CRM serwer. Serwer can be unavailable. Try again later or contact your administrator.". There is no problem in running a query on AnnotationBase, what we firstly though was the problem. Also files size is in upload limit of CRM. It seems that CRM has no access to some library, which is used to o...

Convert multiple XLS files to TXT
I need to convert hundreds of XLS files to TXT. I am aware of File/Save-as and I also tried written a macro. Neither solution is workable even the number of files I have to convert. Does Excel have a mass convert utility? One possibility is to loop through all the files and convert the extension from .xls to .txt. Try: Sub ChangeXLStoTXT() 'Based on some old code from me 'with modifications from Dave Peterson Dim MyFolder As String Dim NewName As String Dim i As Long MyFolder = "C:\Program Files\ztest" '<----Change Application.ScreenUpdating = False With Appli...