Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to generate list of folders
Hi.
I have following requirement: I have a folder with several folders and files inside it. I want to write a code whihc will generate a list of folders and / or files in this main folder. Means I want to list all the folder/file names in one excel file. The code should ask me two thing: 1. Do you want the list of folders only, files only or both? 2. Do you want to create Hyperlink for the list created? This hyperlink should open the corresponing folder/file automatically. Is this possible? Thank you Regards, prm |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to generate list of folders
try this
Sub findfile() 'directory to start searching strFolder = "c:\temp" RowCount = 1 Do Mode = InputBox("What type of search do you want to perform?" & vbCrLf & _ "1: list of folders only" & vbCrLf & _ "2: list of files only" & vbCrLf & _ "3: list of files and folders only") Loop While Mode < 1 Or Mode 3 If Mode = 2 Or Mode = 3 Then Addlinks = MsgBox("Do you want to include Hyperlinks?", vbYesNo, _ Title:=Hyperlinks) Else Hyperlinks = vbNo End If Set fso = CreateObject _ ("Scripting.FileSystemObject") Set folder = _ fso.GetFolder(strFolder) Call GetWorksheetsSubFolder(strFolder + "\", Mode, Addlinks, RowCount) End Sub Sub GetWorksheetsSubFolder(strFolder, Mode, Addlinks, ByRef RowCount) Set fso = CreateObject _ ("Scripting.FileSystemObject") Set folder = _ fso.GetFolder(strFolder) If Mode = 1 Or Mode = 3 Then Range("A" & RowCount) = strFolder RowCount = RowCount + 1 End If If folder.subfolders.Count 0 Then For Each sf In folder.subfolders On Error GoTo 100 Call GetWorksheetsSubFolder(strFolder + sf.Name + "\", Mode, Addlinks, RowCount) 100 Next sf End If 'folder size in bytes On Error GoTo 200 If Mode = 2 Or Mode = 3 Then For Each fl In folder.Files If Addlinks = vbYes Then With ActiveSheet .Hyperlinks.Add Anchor:=.Range("A" & RowCount), Address:=fl.Path, _ TextToDisplay:=fl.Path End With Else Range("A" & RowCount) = fl End If RowCount = RowCount + 1 Next fl End If 200 On Error GoTo 0 End Sub "Pawan" wrote: Hi. I have following requirement: I have a folder with several folders and files inside it. I want to write a code whihc will generate a list of folders and / or files in this main folder. Means I want to list all the folder/file names in one excel file. The code should ask me two thing: 1. Do you want the list of folders only, files only or both? 2. Do you want to create Hyperlink for the list created? This hyperlink should open the corresponing folder/file automatically. Is this possible? Thank you Regards, prm |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to generate list of folders
You can do all that and much more with my DirTree add-in available for
free at http://www.cpearson.com/Excel/foldertree.aspx. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Fri, 20 Mar 2009 23:51:01 -0700, Pawan wrote: Hi. I have following requirement: I have a folder with several folders and files inside it. I want to write a code whihc will generate a list of folders and / or files in this main folder. Means I want to list all the folder/file names in one excel file. The code should ask me two thing: 1. Do you want the list of folders only, files only or both? 2. Do you want to create Hyperlink for the list created? This hyperlink should open the corresponing folder/file automatically. Is this possible? Thank you Regards, prm |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to generate list of folders
Joel and Chip, amazing, simply amazing!!
Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Chip Pearson" wrote: You can do all that and much more with my DirTree add-in available for free at http://www.cpearson.com/Excel/foldertree.aspx. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Fri, 20 Mar 2009 23:51:01 -0700, Pawan wrote: Hi. I have following requirement: I have a folder with several folders and files inside it. I want to write a code whihc will generate a list of folders and / or files in this main folder. Means I want to list all the folder/file names in one excel file. The code should ask me two thing: 1. Do you want the list of folders only, files only or both? 2. Do you want to create Hyperlink for the list created? This hyperlink should open the corresponing folder/file automatically. Is this possible? Thank you Regards, prm |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to create a macro in excel so that it can generate a list ofunique records using all permutations and combinations of the data in eachrow ad column | Excel Discussion (Misc queries) | |||
Create folders from list vb/macro? | Excel Discussion (Misc queries) | |||
macro to list folders only, not files | Excel Worksheet Functions | |||
Generate document folders for Excel list. | Excel Programming | |||
Need macro to generate list | Excel Programming |