displaying contents of a user defined directory
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 |
displaying contents of a user defined directory
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 |
All times are GMT +1. The time now is 07:20 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com