Trouble making a report of all Files within a Folder and all Subfolders?
 

Hi,

Would it be possible for Excel 2000 to compile a report of all files (with an option to include hidden and system files) that reside within a particular directory (and all subfolders within that directory) that reports their Path, File Name, Size, and Date Modified?

I can get the "Browse for Folder" dialog box to work (thanks Tom Ogilvy and Bob Phillips), and a textbox on my userform ("txtMainFolder") stores the path entered.

All the code for the Userform resides within the Userform Private Modules, but I'd like to write the program in a standard code module by transferring across the txtMainFolder value. However, this is causing a lot of problems as Excel keeps saying that it can't set the object (please see below)...however, if the code below resides in the Userform Module, then it runs fine!

For example, I'd like to get a listing of all files within C:\MyFiles (and the txtMainFolder="C:\MyFiles"). My current code is as below:

----------------------------------------------------

' * The Code in the Userform Private Module:

Private Sub OK_Click()

........

Call GetFile(txtMainDir.value)

End Sub


Sub GetFile(MainDir as String)

Dim FSO as Object
Dim Folder as Object
Dim Files as Object
Dim File as Object

Set Folder=FSO.GetFolder(MainDir) €˜ <<<< Error Here

Set Files=Folder.Files

End Sub

-------------------------------------------------------

Could you pleas help?

Thankyou!

SuperJas.

Trouble making a report of all Files within a Folder and all Subfolders?
 

Hi,

Here is a function that will return an array with the details that you want.

Yours errored as you didn't create FSO.

This function takes optional boolean argumenst of Hidden and System as
required.

Function GetFile(MainDir As String, _
Optional Hidden As Boolean = False, _
Optional System As Boolean = True)
Dim FSO As Object
Dim oFolder As Object
Dim oFiles As Object
Dim oFile As Object
Dim i As Long
Dim aFiles

Set FSO = CreateObject("Scripting.FileSystemObject")
Set oFolder = FSO.GetFolder(MainDir)

Set oFiles = oFolder.Files

ReDim aFiles(oFiles.Count, 3)
For Each oFile In oFiles
If (oFile.Attributes And 2) And Hidden Then
'2 is hidden
ElseIf (oFile.Attributes And 4) And Not System Then
'4 is system
Else
aFiles(i, 0) = oFile.Path
aFiles(i, 1) = oFile.Name
aFiles(i, 2) = oFile.Size
aFiles(i, 3) = oFile.DateLastModified
i = i + 1
End If
Next oFile

End Function

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"SuperJas" wrote in message
...
Hi,

Would it be possible for Excel 2000 to compile a report of all files (with
an option to include hidden and system files) that reside within a
particular directory (and all subfolders within that directory) that reports
their Path, File Name, Size, and Date Modified?

I can get the "Browse for Folder" dialog box to work (thanks Tom Ogilvy
and Bob Phillips), and a textbox on my userform ("txtMainFolder") stores the
path entered.

All the code for the Userform resides within the Userform Private Modules,
but I'd like to write the program in a standard code module by transferring
across the txtMainFolder value. However, this is causing a lot of problems
as Excel keeps saying that it can't set the object (please see
below)...however, if the code below resides in the Userform Module, then it
runs fine!

For example, I'd like to get a listing of all files within C:\MyFiles (and
the txtMainFolder="C:\MyFiles"). My current code is as below:

----------------------------------------------------

' * The Code in the Userform Private Module:

Private Sub OK_Click()

.......

Call GetFile(txtMainDir.value)

End Sub


Sub GetFile(MainDir as String)

Dim FSO as Object
Dim Folder as Object
Dim Files as Object
Dim File as Object

Set Folder=FSO.GetFolder(MainDir) ' <<<< Error Here

Set Files=Folder.Files

End Sub

-------------------------------------------------------

Could you pleas help?

Thankyou!

SuperJas.

Trouble making a report of all Files within a Folder and all Subfolders?
 

Thanks Heaps Bob- It works great! =

SuperJas.