Hi,

how can I get the file size of an file?

I would like to run through many folders and list all files that are under
30k. Can this be done easily in vba?

Thanks
Jos Vens

Jos,
I prefer the Scripting Runtime FileSystemObject.
Use it in Excel just like vba.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware

Option Compare Text
Sub ListFileNamesAndSizes()
'Jim Cone - San Francisco, USA
'Requires a project reference to "Microsoft Scripting Runtime" (scrrun.dll)
'Lists - specific files that exceed a specific size - on the active sheet.

Dim objFSO As Scripting.FileSystemObject
Dim objFolder As Scripting.Folder
Dim objFile As Scripting.File
Dim strPath As String
Dim strName As String
Dim lngNum As Long

'Specify the folder...
strPath = "C:\Documents and Settings"
'Specify the file type to look for...
strName = "*.xls"
Set objFSO = New Scripting.FileSystemObject
Set objFolder = objFSO.GetFolder(strPath)
lngNum = 2

For Each objFile In objFolder.Files
If objFile.Name Like strName Then
If objFile.Size < 30000 Then
Cells(lngNum, 2).Value = objFile.Name
Cells(lngNum, 3).Value = objFile.Size
lngNum = lngNum + 1
End If
End If
Next 'objFile
Set objFile = Nothing

'Call recursive function
DoTheSubFolders objFolder.SubFolders, lngNum, strName

Set objFSO = Nothing
Set objFolder = Nothing
End Sub
'------------------------

Function DoTheSubFolders(ByRef objFolders As Scripting.Folders, _
ByRef lngN As Long, ByRef strTitle As String)
Dim scrFolder As Scripting.Folder
Dim scrFile As Scripting.File
Dim lngCnt As Long

For Each scrFolder In objFolders
For Each scrFile In scrFolder.Files
If scrFile.Name Like strTitle Then
If scrFile.Size < 30000 Then
Cells(lngN, 2).Value = scrFile.Name
Cells(lngN, 3).Value = scrFile.Size
lngN = lngN + 1
End If
End If
Next 'scrFile

'If there are more sub folders then go back and run function again.
If scrFolder.SubFolders.Count 0 Then
DoTheSubFolders scrFolder.SubFolders, lngN, strTitle
End If
Next 'scrFolder

Set scrFile = Nothing
Set scrFolder = Nothing
End Function
'------------------


"Jos Vens"
wrote in message
Hi,
how can I get the file size of an file?
I would like to run through many folders and list all files that are under
30k. Can this be done easily in vba?
Thanks
Jos Vens

Thanks a lot Jim!

Jos


"Jim Cone" schreef in bericht
...
Jos,
I prefer the Scripting Runtime FileSystemObject.
Use it in Excel just like vba.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware

Option Compare Text
Sub ListFileNamesAndSizes()
'Jim Cone - San Francisco, USA
'Requires a project reference to "Microsoft Scripting Runtime"
(scrrun.dll)
'Lists - specific files that exceed a specific size - on the active
sheet.

Dim objFSO As Scripting.FileSystemObject
Dim objFolder As Scripting.Folder
Dim objFile As Scripting.File
Dim strPath As String
Dim strName As String
Dim lngNum As Long

'Specify the folder...
strPath = "C:\Documents and Settings"
'Specify the file type to look for...
strName = "*.xls"
Set objFSO = New Scripting.FileSystemObject
Set objFolder = objFSO.GetFolder(strPath)
lngNum = 2

For Each objFile In objFolder.Files
If objFile.Name Like strName Then
If objFile.Size < 30000 Then
Cells(lngNum, 2).Value = objFile.Name
Cells(lngNum, 3).Value = objFile.Size
lngNum = lngNum + 1
End If
End If
Next 'objFile
Set objFile = Nothing

'Call recursive function
DoTheSubFolders objFolder.SubFolders, lngNum, strName

Set objFSO = Nothing
Set objFolder = Nothing
End Sub
'------------------------

Function DoTheSubFolders(ByRef objFolders As Scripting.Folders, _
ByRef lngN As Long, ByRef strTitle As String)
Dim scrFolder As Scripting.Folder
Dim scrFile As Scripting.File
Dim lngCnt As Long

For Each scrFolder In objFolders
For Each scrFile In scrFolder.Files
If scrFile.Name Like strTitle Then
If scrFile.Size < 30000 Then
Cells(lngN, 2).Value = scrFile.Name
Cells(lngN, 3).Value = scrFile.Size
lngN = lngN + 1
End If
End If
Next 'scrFile

'If there are more sub folders then go back and run function again.
If scrFolder.SubFolders.Count 0 Then
DoTheSubFolders scrFolder.SubFolders, lngN, strTitle
End If
Next 'scrFolder

Set scrFile = Nothing
Set scrFolder = Nothing
End Function
'------------------


"Jos Vens"
wrote in message
Hi,
how can I get the file size of an file?
I would like to run through many folders and list all files that are under
30k. Can this be done easily in vba?
Thanks
Jos Vens