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
969 Views

Similar Articles

[PageSpeed] 12

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 (12005)
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 (12005)
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 (12005)
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:

Distribution Files for MFC in VS .net 2003
I have an app that was developed under Visual Studio VC++ 6.0 & MFC and deployed at our customer site. I have since installed Visual Studio .NET 2003 and rebuilt the application under the new development environment. I wanted to do a quick test of the executable and copied it up to a test machine and tried to run it. I got a series of "unable to find xxx.dll" errors. I realized that none of the new DLLs were installed on the test machine. Does anyone have a pointer to a good document on what distribution files are required? I know about the obvious ones like MFC71.DLL...

Save formatted text from RichEdit control to rtf-file
Hi , How can I save the text from Rich edit control (2.0) to *.rtf , *.txt , *.doc I tried to get the buffer and putting the buffer to file, then saving the file but the text in the file is something different. Please let me know what to do? Here is the Code I ma using: mFile.Seek( 0, CFile::begin ); CString cBuffer2; int iTotalTextLength = m_oChatMessageControl.GetWindowTextLength(); HWND focusWnd = ::GetFocus(); m_oChatMessageControl.HideSelection(TRUE, TRUE); m_oChatMessageControl.SetSel(iTotalTextLength, iTotalTextLength); cBuffer2 = m_oChatMessageControl.GetSelText(); LPTSTR...

Team Folders migration from 5.5 to 2003
Are there any known issues with migrating Exchange/Outlook Team Folders from Exchange 5.5 to Exchange 2003 using PFMigrate? We have many Team Folders on our older Exchange 5.5 server that we MUST migrate over to Exchange 2003 with full functionality Thanks, FastEddie These are Team Folders created with the Team Folders Wizard? I don't know whether PFMigrate will migrate the folder home page setting and the custom form that a couple of the folders use. You may need to handle those manually. -- Sue Mosher, Outlook MVP Author of Microsoft Outlook Programming - Jumpstart for ...

Wrong unread count in inbox (1 is list but no there is no messages)
I'm trying to figure out why a user has this problem. The unread inbox count shows 1, even if there are no unread messages in the Inbox. Any ideas? Thanks in advance! Check your view (View, Current View, Customize Current View) and make sure there are no filters set. -- Patricia Cardoza Outlook MVP www.cardozasolutions.com Author, Special Edition Using Microsoft Outlook 2003 Author, Absolute Beginner's Guide to OneNote 2003 ***Please post all replies to the newsgroups*** "mr_gustav" <randy.riauka@saultc.on.ca> wrote in message news:17b1b01c418c3$e7e4aff0$a10128...

file cloning
I was wondering. How come it is possible to clone a file (using right click copy/paste file), but not possible to do this for other documents (apps and clip-art etc)?. Could it be possible to have an add-in in excel to prevent people from copying documents on their desktop?? -- shnim1 You can copy files that way (rightclick|copy, rightclick|paste). But most windows applications are no longer just simple .exe files (like back in the old DOS days). They usually have tons of other stuff that gets installed with them--and that stuff gets scattered all over your harddrive (windows folder, wi...

Outlook 2003: Most messages are moved to junk folder
Guys, I am getting a lot of spam recently, at least looking at the numbers of mails in my junk folder. However, it turns out that these messages are not spam at all, but just regular messages (even from the same domain), that should remain in the regular inbox. Even when I identify a message as 'Not spam', then subsequent messages from that sender keep popping up in the junk folder. Obviously I have changed the junk mail settings to 'No automatic filtering' but Outlook keeps thinking it is smarter than I am. Any idea? Googled the internet already but couldn't find anoth...

seperate accounts to seperate folders?
Hi I just had a quick question... I am currently using Outlook 2003, and I have 2 pop3 email accounts se up in my outlook.....one of them is my personal company email and th other is the general comapny email address (which i am responsible fo checking). What i am trying to find out how to do is, is it possibl to have the general company email messages go directly to a seperat folder besides the inbox? I still want my personal company email t come to the Inbox folder, but i want the general company email to go t a folder called "SCS." Any help would be greatly appreciated! Thank...

how to build the netsample ipconfig to the exe file?
C:\WINCE500\public\common\oak\drivers\netsamp\ipconfig\ipconfig.cpp i want to make ipconfig.exe. and i could found the sample code. but it source code builded to the lib file. in ipconfig sources files, TARGETNAME=ipconfig TARGETTYPE=LIBRARY SOURCES= \ $(TARGETNAME).cpp the project is .lib file. but the source code has a _tmain() funciton in the source. it's looks possible to compile to the exe file. how can it compile to the exe file? You can add the SYSGEN_NETUTILS and you will have the ipconfig.exe integrated to your OS. Search ipconfig in the cat...

How do I export Lotus Approach files into an Excel spreadsheet?
I need to export data from Lotus Approach to Excel; please help. I am using an old version of Lotus SmartSuite 9.5 and I have Microsoft Office 2003 Basic. Well, I don't know Approach at all but is there a common file format that both use e.g. comma delimited. If so , save in that format from Approach and import into Excel. "LEWOLF" wrote: > I need to export data from Lotus Approach to Excel; please help. I am using > an old version of Lotus SmartSuite 9.5 and I have Microsoft Office 2003 Basic. ...

outlook can't receive exe files
A guy here at work can't get exe files through his outlook. Is there a check to uncheck somewhere to allow it to do this? He can receive normal attachments. ...

Importing spam list
Hi, I have a long list of spam email addresses that I want to import into outlook. How do I do this? Do they have to be separated by commas? Thaks, Paul Where is this spam list generated? What version of Outlook? Where do you propose to import them? --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert latest virus name here) virus, all mail sent to my personal account will be deleted without reading. After searching google.groups.com and finding no answer, Paul Ellis asked: | Hi, | I have a long list of spam email addresses...

Deleted items folder in o2k3
The big boss refuses to empty or archive his deleted items, now holding some 25,000 messages and approaching 3 gigs in size for the deleted items folder alone. Does Outlook treat messages in the deleted items folder any differently than messages in other folders? Would it be better for him to never delete messages since he doesn't really want to delete them? It would be better to not delete them since Deleted Items... is for things you want to get rid of. "Wowbagger" <none> wrote in message news:uKyAPby2GHA.3656@TK2MSFTNGP04.phx.gbl... > The big boss refuses ...

retrieving folders.old file
probably been posted before, but need some help. i was getting the "MSIMN has caused an error in directdb.dll" i found the solution by renaming the folders.dbx file to folders.old. here's the problem, i opened express back up and my sent folder was empty. my question is, how or can i retrieve that old sent message list?? ...

Sorting Attendee Lists in Outlook 2003
Is there any way to sort attendees by either name or attendee status in Outlook 2003? I don't know how Microsoft could be so oblivious to the need for this feature. You can't even click on the column headers to sort, let alone print out a list of people sorted by accept and decline. Microsoft's online help system recommends Alt + PrntScreen???? How archaic is that? Is there anyone out there that can help me??? Thanks! ...

Need code snippet to read offline PST file
Hi friends, I have a PST file in my local hard disk and have requirement to read PST file and parse through all folders and then each message item in all folders and then segregate them to different folders based on subject line. Please kindly send the code for the above requirement. Thanks & Regards Ramesh -- ramserp You're going to have to write your own code. Do you know anything about Outlook programming at all? You can start out by looking at information and code samples at www.outlookcode.com. -- Ken Slovak [MVP - Outlook] http://www.slo...

Public folders #9
I'm looking for a tool or a methode that can write all the properties of the public folders to a file Now I'm doing ik manual, but it isn time-consuming Can anyone help m thanks "cisca" <anonymous@discussions.microsoft.com> wrote: >I'm looking for a tool or a methode that can write all the properties of the public folders to a file. >Now I'm doing ik manual, but it isn time-consuming. >Can anyone help me PFADMIN or PFINFO can do this. -- Rich Matheisen MCSE+I, Exchange MVP MS Exchange FAQ at http://www.swinc.com/resource/exch_faq.htm ...

Data Validation List not showing
I'm using Excel 2003. My data validation lists have stopped working on one sheet in my workbook. It is working on all other sheets. I have googled the problem and found the following advice: 1. Make sure freeze panes is off.... check. 2. Select "Show All" under Tools->Options->View->Objects ... check. The problem remains. Any ideas? "Stopped working" doesn't do much to describe your problem. When you select one of the "stopped working" Data Validation cells, do you see the drop-down arrow to the right of the cell? When you select t...

Excel 2000 vs. Excel 2002
I am having troubles with a workbook that I created that is havin problems opening. I created it in 2002, and it opens fine in Excel 2002 for other people However, when I send it to someone who has Excel 2000, it takes over a hour to open. Now I also made a very similar report that works just fine whe trasferred to excel 2000. Here are a couple of stats on the workbook that is having problems: 1.5mb 500+ externel links 500+ subtotals 200+ simple calculations (a1+b1; a1/b1;etc..) 1 Worksheet in the book. 2 columns with conditional formatting Thanks, Joh -- Message posted from http://ww...

selected row count of list box
A2k Is there a way to get the selected row count of a list box dynamically as the user selects rows? Delphi has an event called "OnSelectionChanged" but Access is much more limited. I don't want the user to have to exit the list box or click a button or anything manual in order to see the # of rows he's selected. How can this be done? I know about "lstCusts.ItemsSelected.Count" but not sure what event to use it in to accomplish what I need. Thanks, Keith Never mind. I did this and it handles both mouse and keyboard selections: Private Sub lstCusts_AfterUpdat...

Any FREAKIN' way to import DBX files into Outlook 2003
I've tried: Importing via Outlook | Import from another Program or File Importing via Outlook | Import Internet Mail and Addresses Exporting from Outlook Express Tried Many, many times... Can Microsucks make this any more complicated... It's a FREAKIN' DBX file collection NO - No other Application has it Open. YES - The Files ARE there YES - the Internet Account IS there Yes - I've wasted more of my time IMPORTING into Outlook Express in VISTA just to RE-EXPORT back to Outlook. What a bunch of freakin' idiots... Another 2 hours wasted - because one Microsoft applica...

Replicating folders
I am having a strange issue with tasks replicating for no apparent reason. Outlook 2002 on Windows XP. Anyone run across something like this before? Deleted 34 blank tasks and the next day there were 14 new ones. ...

manufacturing scrap not calculating on pick list and serial #'s no
I have entered my percentage of scrap on a MFG BOM and is seems to be calculating properly in the setup. When I release the components and add them to a pick list the scrap is not calculating. I also choose serial numbers and they do not carry thorugh and print on the pick list. Is there some setting that I have missed? -- Paula ...

Opening .prn files in XL2000
I am using a software that does not save data/reports in .csv or .xls formats; only in printed versions. Is there a way to save the printed report in a file and open the file in XL2000? If there is, how is the print file produced, where is it saved, etc? A friend suggested setting up a generic printer but didn't know how to go about it. You may want to give that other software just one more chance--look under File and see if there is a SaveAs option. You may find something upon further review. But if you want to add a generic printer, I think it'll depend on your version of win...

Import reg files into Registry, without UAC
Hi, I've got a .reg file to be imported silently in a batch. The file contains only entries in HKEY_CURRENT_USER, therefore can be imported without elevation. This works well with the regedit /s switch on limited accounts, however on admin accounts, elevation UAC prompt is still shown, even though it's not needed. How can I prevent this? Thanks, Jens Jens M�ller wrote: >Hi, > >I've got a .reg file to be imported silently in a batch. The file contains >only entries in HKEY_CURRENT_USER, therefore can be imported without >elevation. >...

Duplicate personal folders problem
Hello all, I have a problem with Outlook 2003 and I hope someone can help me resolve it I imported a PST file from Outlook XP that was on a different machine, and now I have two versions of Personal Folders in my All Mail Folders list. They both have Deleted Items, Drafts, Inbox, Junk E-mail, Outbox, Sent Items and Search Folders in. One of them has an icon of a piece of paper with a clock to the top left and a house to the top right. The other one has an icon like a stack of files (like from a filing cabinet) They are clearly referencing the same thing, as the Deleted Items both have al...