Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > List all files in a sub directory
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 15
Default List all files in a sub directory
Hoping someone can help me - it seems that Application.FileSearch no
longer works in Excel 2007. I used to have a routine (below) which
listed all the files in a directory (and possibly sub directory. I
had a dig around on the group last night & I think I need to use fso,
but I'm not exactly sure what that means, or how to implement it. Can
someone help me?

My knowledge of VBA is poor-moderate.

Thanks,
Kate

Sub ListFiles()
Set fs = Application.FileSearch

With fs
.LookIn = ActiveWorkbook.Path
.SearchSubFolders = True
.FileName = "*.*"
If .Execute() 0 Then
'MsgBox "There were " & .FoundFiles.Count & _
' " file(s) found."
'For I = 1 To .FoundFiles.Count
' MsgBox .FoundFiles(I)
'Next I
Else
MsgBox "There were no files found."
End If

'write headings

Range("A1").Value = "Size (MB)"
Range("B1").Value = "File"
Range("C1").Value = "Date"

For i = 1 To .FoundFiles.Count

Range("B" & i + 1).Value = .FoundFiles(i)
Range("A" & i + 1).Value = FileLen(.FoundFiles(i)) / 1024 ^ 2
Range("C" & i + 1).Value = FileDateTime(.FoundFiles(i))

Next

End With

End Sub
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default List all files in a sub directory
Try this

Dim RowNumber
Sub GetFiles()

Range("A1").Value = "Size (MB)"
Range("B1").Value = "File"
Range("C1").Value = "Date"


strFolder = ActiveWorkbook.Path
RowNumber = 1

Set fso = CreateObject _
("Scripting.FileSystemObject")
Set folder = _
fso.GetFolder(strFolder)

RowNumber = RowNumber + RowNumber

Call GetSubFolderSize(strFolder + "\")
End Sub

Sub GetSubFolderSize(strFolder)
Set fso = CreateObject _
("Scripting.FileSystemObject")

Set folder = _
fso.GetFolder(strFolder)

If folder.subfolders.Count 0 Then
For Each sf In folder.subfolders
On Error GoTo 100
Call GetSubFolderSize(strFolder + sf.Name + "\")
100 Next sf
End If
'folder size in bytes
On Error GoTo 200

For Each MyFile In folder.Files
Range("B" & i + 1).Value = MyFile.Path
Range("A" & i + 1).Value = MyFile.Size / 1024 ^ 2
Range("C" & i + 1).Value = MyFile.Datelastmodified
a = 1
Next MyFile
200 On Error GoTo 0

End Sub






"KateB" wrote:

Hoping someone can help me - it seems that Application.FileSearch no
longer works in Excel 2007. I used to have a routine (below) which
listed all the files in a directory (and possibly sub directory. I
had a dig around on the group last night & I think I need to use fso,
but I'm not exactly sure what that means, or how to implement it. Can
someone help me?

My knowledge of VBA is poor-moderate.

Thanks,
Kate

Sub ListFiles()
Set fs = Application.FileSearch

With fs
.LookIn = ActiveWorkbook.Path
.SearchSubFolders = True
.FileName = "*.*"
If .Execute() 0 Then
'MsgBox "There were " & .FoundFiles.Count & _
' " file(s) found."
'For I = 1 To .FoundFiles.Count
' MsgBox .FoundFiles(I)
'Next I
Else
MsgBox "There were no files found."
End If

'write headings

Range("A1").Value = "Size (MB)"
Range("B1").Value = "File"
Range("C1").Value = "Date"

For i = 1 To .FoundFiles.Count

Range("B" & i + 1).Value = .FoundFiles(i)
Range("A" & i + 1).Value = FileLen(.FoundFiles(i)) / 1024 ^ 2
Range("C" & i + 1).Value = FileDateTime(.FoundFiles(i))

Next

End With

End Sub

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default List all files in a sub directory
I forgot to increment the row number.

Dim RowNumber
Sub GetFiles()

Range("A1").Value = "Size (MB)"
Range("B1").Value = "File"
Range("C1").Value = "Date"


strFolder = ActiveWorkbook.Path
RowNumber = 2

Set fso = CreateObject _
("Scripting.FileSystemObject")
Set folder = _
fso.GetFolder(strFolder)

RowNumber = RowNumber + RowNumber

Call GetSubFolderSize(strFolder + "\")
End Sub

Sub GetSubFolderSize(strFolder)
Set fso = CreateObject _
("Scripting.FileSystemObject")

Set folder = _
fso.GetFolder(strFolder)

If folder.subfolders.Count 0 Then
For Each sf In folder.subfolders
On Error GoTo 100
Call GetSubFolderSize(strFolder + sf.Name + "\")
100 Next sf
End If
'folder size in bytes
On Error GoTo 200

For Each MyFile In folder.Files
Range("B" & RowNumber).Value = MyFile.Path
Range("A" & RowNumber).Value = MyFile.Size / 1024 ^ 2
Range("C" & RowNumber).Value = MyFile.Datelastmodified
RowNumber = RowNumber + 1
Next MyFile
200 On Error GoTo 0

End Sub






"KateB" wrote:

Hoping someone can help me - it seems that Application.FileSearch no
longer works in Excel 2007. I used to have a routine (below) which
listed all the files in a directory (and possibly sub directory. I
had a dig around on the group last night & I think I need to use fso,
but I'm not exactly sure what that means, or how to implement it. Can
someone help me?

My knowledge of VBA is poor-moderate.

Thanks,
Kate

Sub ListFiles()
Set fs = Application.FileSearch

With fs
.LookIn = ActiveWorkbook.Path
.SearchSubFolders = True
.FileName = "*.*"
If .Execute() 0 Then
'MsgBox "There were " & .FoundFiles.Count & _
' " file(s) found."
'For I = 1 To .FoundFiles.Count
' MsgBox .FoundFiles(I)
'Next I
Else
MsgBox "There were no files found."
End If

'write headings

Range("A1").Value = "Size (MB)"
Range("B1").Value = "File"
Range("C1").Value = "Date"

For i = 1 To .FoundFiles.Count

Range("B" & i + 1).Value = .FoundFiles(i)
Range("A" & i + 1).Value = FileLen(.FoundFiles(i)) / 1024 ^ 2
Range("C" & i + 1).Value = FileDateTime(.FoundFiles(i))

Next

End With

End Sub

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 15
Default List all files in a sub directory
Perfect, thanks!


On Mar 5, 10:56*pm, Joel wrote:
I forgot to increment the row number.

Dim RowNumber
Sub GetFiles()

* *Range("A1").Value = "Size (MB)"
* *Range("B1").Value = "File"
* *Range("C1").Value = "Date"

* *strFolder = ActiveWorkbook.Path
* *RowNumber = 2

* *Set fso = CreateObject _
* * * ("Scripting.FileSystemObject")
* *Set folder = _
* * * fso.GetFolder(strFolder)

* *RowNumber = RowNumber + RowNumber

* *Call GetSubFolderSize(strFolder + "\")
End Sub

Sub GetSubFolderSize(strFolder)
* *Set fso = CreateObject _
* * * ("Scripting.FileSystemObject")

* *Set folder = _
* * * fso.GetFolder(strFolder)

* * * If folder.subfolders.Count 0 Then
* * * * *For Each sf In folder.subfolders
* * * * * * On Error GoTo 100
* * * * * * Call GetSubFolderSize(strFolder + sf.Name + "\")
100 * * *Next sf
* * * End If
* *'folder size in bytes
* *On Error GoTo 200

* *For Each MyFile In folder.Files
* * * Range("B" & RowNumber).Value = MyFile.Path
* * * Range("A" & RowNumber).Value = MyFile.Size / 1024 ^ 2
* * * Range("C" & RowNumber).Value = MyFile.Datelastmodified
* * RowNumber = RowNumber + 1
* *Next MyFile
200 * On Error GoTo 0

End Sub

"KateB" wrote:
Hoping someone can help me - it seems that Application.FileSearch no
longer works in Excel 2007. I used to have a routine (below) which
listed all the files in a directory (and possibly sub directory. *I
had a dig around on the group last night & I think I need to use fso,
but I'm not exactly sure what that means, or how to implement it. *Can
someone help me?

My knowledge of VBA is poor-moderate.

Thanks,
Kate

Sub ListFiles()
Set fs = Application.FileSearch

With fs
* * .LookIn = ActiveWorkbook.Path
* * .SearchSubFolders = True
* * .FileName = "*.*"
* * If .Execute() 0 Then
* * * * 'MsgBox "There were " & .FoundFiles.Count & _
* * * * ' " file(s) found."
* * * * 'For I = 1 To .FoundFiles.Count
* * * * ' * *MsgBox .FoundFiles(I)
* * * * 'Next I
* * Else
* * * * MsgBox "There were no files found."
* * End If

* * 'write headings

* * Range("A1").Value = "Size (MB)"
* * Range("B1").Value = "File"
* * Range("C1").Value = "Date"

* * For i = 1 To .FoundFiles.Count

* * * * Range("B" & i + 1).Value = .FoundFiles(i)
* * * * Range("A" & i + 1).Value = FileLen(.FoundFiles(i)) / 1024 ^ 2
* * * * Range("C" & i + 1).Value = FileDateTime(.FoundFiles(i))

Next

End With

End Sub



Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default List all files in a sub directory
On 5 Mar, 00:50, KateB wrote:
Hoping someone can help me - it seems that Application.FileSearch no
longer works in Excel 2007. I used to have a routine (below) which
listed all thefilesin adirectory(and possibly subdirectory. *I
had a dig around on the group last night & I think I need to use fso,
but I'm not exactly sure what that means, or how to implement it. *Can
someone help me?

My knowledge ofVBAis poor-moderate.

Thanks,
Kate

Sub ListFiles()
Set fs = Application.FileSearch

With fs
* * .LookIn = ActiveWorkbook.Path
* * .SearchSubFolders = True
* * .FileName = "*.*"
* * If .Execute() 0 Then
* * * * 'MsgBox "There were " & .FoundFiles.Count & _
* * * * ' " file(s) found."
* * * * 'For I = 1 To .FoundFiles.Count
* * * * ' * *MsgBox .FoundFiles(I)
* * * * 'Next I
* * Else
* * * * MsgBox "There were nofilesfound."
* * End If

* * 'write headings

* * Range("A1").Value = "Size (MB)"
* * Range("B1").Value = "File"
* * Range("C1").Value = "Date"

* * For i = 1 To .FoundFiles.Count

* * * * Range("B" & i + 1).Value = .FoundFiles(i)
* * * * Range("A" & i + 1).Value = FileLen(.FoundFiles(i)) / 1024 ^ 2
* * * * Range("C" & i + 1).Value = FileDateTime(.FoundFiles(i))

Next

End With

End Sub

Here is a simple program that I use to list files in a certain
directory!
http://vbaexcel.eu/vba-macro-code/li...s-in-directory
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
How do i create a list of files per directory? Costis Excel Programming 4 April 23rd 07 12:16 PM
List of Files in A Directory JaneC Excel Discussion (Misc queries) 2 February 18th 06 12:11 PM
List Files in a Directory [email protected] Excel Programming 3 December 18th 05 12:13 AM
make a list of files in a directory GFN Excel Programming 2 July 29th 05 07:49 AM
Directory List of Excel Files No Name Excel Programming 6 May 18th 04 05:22 PM


All times are GMT +1. The time now is 11:44 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement