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

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

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

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