Hello,

How should a macro that read a user defined directory which is entered in
excel.
What I mean is:

1. In the mainsheet (Excel), user will enter the directory for the
main_folder.

2.Then , once user click on the "Search" button, it will list all the
subfolder (from the main_folder directory) in the mainsheet at a specified
row and column.Let say row 4, column "C".

-help needed ;(

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200606/1

Hi

Public Function GetSubfolder(MyFolder As String, FolderNum As Integer,
Optional MyTime As Date)
Dim fs, f, f1, s, sf
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder(MyFolder)
Set sf = f.SubFolders
i = 0
For Each f1 In sf
i = i + 1
If i = FolderNum Then GetSubfolder = f1.Name
Next
End Function

Copy this UDF into workbook module.

On sheet MainSheet, I assume you have the folder string in cell p.e. A1
(like C:\Documents and Settings\MyProfile\My Documents\)
Into A2, enter the formula
=IF(ISERROR(GetSubfolder($A$1,ROW()-1)),"",GetSubfolder($A$1,ROW()-1))
, and copy it down for some amount of rows.

(You can make the formula volatile, including TODAY() or NOW() as 3rd
parameter for 1st of GetSubFolder functions in formula)


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )


"cyzax7 via OfficeKB.com" <u22797@uwe wrote in message
news:618077d9b23a4@uwe...
Hello,

How should a macro that read a user defined directory which is entered in
excel.
What I mean is:

1. In the mainsheet (Excel), user will enter the directory for the
main_folder.

2.Then , once user click on the "Search" button, it will list all the
subfolder (from the main_folder directory) in the mainsheet at a specified
row and column.Let say row 4, column "C".

-help needed ;(

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200606/1