Associate
Hi
I am having a terrible time trying to get my head round this problem. As a result of my naivety, I have written probably the worst bit of code of my life!!
(Although it does work, I am totally embarrassed by it).
The problem :
To enter details of every .jpg, .bmp, .gif, etc. on my hard disk drive, into an excel spreadsheet.
I really only want to know the name and path, but have also included date and size.
My solution :
starting with the root, search all files in every folder and sub-folder until every file has been analysed. If it's a picture, enter the details.
I worked out there were a maximum of 17 sub-folders, but my problem is, I cannot seem to make an array for SubFolderName.
I want to write something like :
For Each SubFolderName(CurrentLevel) in SubFolder(CurrentLevel)
but when I try this, I get the error message "For control variable already in use"
I'm probably going about this problem all wrong anyway.
This is my original code :
On Error GoTo FolderError
' Enter here the location of the folder, and the cell you want the results to start from.
FolderName = "N:\"
RowNum = 2
ColNum = 2
' List the headings
ActiveSheet.Cells(RowNum, ColNum) = "Filename"
ActiveSheet.Cells(RowNum, ColNum + 1) = "Date"
ActiveSheet.Cells(RowNum, ColNum + 2) = "Size"
ActiveSheet.Cells(RowNum, ColNum + 3) = "Path"
RowNum = 3
Set FileSysObj1 = CreateObject("Scripting.FileSystemObject")
CurrentLevel = 1
Set Folder1 = FileSysObj1.GetFolder(FolderName)
Set SubFolder1 = Folder1.subfolders
DoFolder = True
m = Folder1.subfolders.Count ' This triggers an error if folder is protected.
If DoFolder = True Then
' Now do the file bit.
Set FileColl = Folder1.Files
For Each FFileName In FileColl
If Format(Right(FFileName.Name, 3), ">") = "JPG" Or Format(Right(FFileName.Name, 3), ">") = "BMP" Or Format(Right(FFileName.Name, 3), ">") =
"GIF" Or Format(Right(FFileName.Name, 3), ">") = "TIF" Or Format(Right(FFileName.Name, 3), ">") = "PNG" Then
ActiveSheet.Cells(RowNum, ColNum) = FFileName.Name
ActiveSheet.Cells(RowNum, ColNum + 1) = FileDateTime(FFileName.Path)
ActiveSheet.Cells(RowNum, ColNum + 2) = Format(FFileName.Size / 1024, "0")
ActiveSheet.Cells(RowNum, ColNum + 3) = FFileName.Path
RowNum = RowNum + 1
If RowNum > 65000 Then
RowNum = 2
ColNum = ColNum + 8
End If
End If
Next
For Each SubFolderName1 In SubFolder1
CurrentLevel = 2
Set Folder2 = FileSysObj1.GetFolder(SubFolderName1)
Set SubFolder2 = Folder2.subfolders
DoFolder = True
m = Folder2.subfolders.Count ' This triggers an error if folder is protected.
If DoFolder = True Then
' Now do the file bit
Set FileColl = Folder2.Files
For Each FFileName In FileColl
If Format(Right(FFileName.Name, 3), ">") = "JPG" Or Format(Right(FFileName.Name, 3), ">") = "BMP" Or Format(Right(FFileName.Name, 3),
">") = "GIF" Or Format(Right(FFileName.Name, 3), ">") = "TIF" Or Format(Right(FFileName.Name, 3), ">") = "PNG" Then
ActiveSheet.Cells(RowNum, ColNum) = FFileName.Name
ActiveSheet.Cells(RowNum, ColNum + 1) = FileDateTime(FFileName.Path)
ActiveSheet.Cells(RowNum, ColNum + 2) = Format(FFileName.Size / 1024, "0")
ActiveSheet.Cells(RowNum, ColNum + 3) = FFileName.Path
RowNum = RowNum + 1
If RowNum > 65000 Then
RowNum = 2
ColNum = ColNum + 8
End If
End If
Next
For Each SubFolderName2 In SubFolder2
CurrentLevel = 3
Set folder3 = FileSysObj1.GetFolder(SubFolderName2)
Set SubFolder3 = folder3.subfolders
DoFolder = True
m = folder3.subfolders.Count ' This triggers an error if folder is protected.
If DoFolder = True Then
' Now do the file bit
Set FileColl = folder3.Files
For Each FFileName In FileColl
If Format(Right(FFileName.Name, 3), ">") = "JPG" Or Format(Right(FFileName.Name, 3), ">") = "BMP" Or Format(Right(FFileName.Name,
3), ">") = "GIF" Or Format(Right(FFileName.Name, 3), ">") = "TIF" Or Format(Right(FFileName.Name, 3), ">") = "PNG" Then
ActiveSheet.Cells(RowNum, ColNum) = FFileName.Name
ActiveSheet.Cells(RowNum, ColNum + 1) = FileDateTime(FFileName.Path)
ActiveSheet.Cells(RowNum, ColNum + 2) = Format(FFileName.Size / 1024, "0")
ActiveSheet.Cells(RowNum, ColNum + 3) = FFileName.Path
RowNum = RowNum + 1
If RowNum > 65000 Then
RowNum = 2
ColNum = ColNum + 8
End If
End If
Next
For Each subfoldername3 In SubFolder3
... And so it continues, to a 'depth' of 17 sub-folders!!!
As you can see, it's dying to be simplified. There's gotta be a better, quicker solution than this mess - please help me get out of this nightmare!!
I am having a terrible time trying to get my head round this problem. As a result of my naivety, I have written probably the worst bit of code of my life!!
(Although it does work, I am totally embarrassed by it).
The problem :
To enter details of every .jpg, .bmp, .gif, etc. on my hard disk drive, into an excel spreadsheet.
I really only want to know the name and path, but have also included date and size.
My solution :
starting with the root, search all files in every folder and sub-folder until every file has been analysed. If it's a picture, enter the details.
I worked out there were a maximum of 17 sub-folders, but my problem is, I cannot seem to make an array for SubFolderName.
I want to write something like :
For Each SubFolderName(CurrentLevel) in SubFolder(CurrentLevel)
but when I try this, I get the error message "For control variable already in use"
I'm probably going about this problem all wrong anyway.
This is my original code :
On Error GoTo FolderError
' Enter here the location of the folder, and the cell you want the results to start from.
FolderName = "N:\"
RowNum = 2
ColNum = 2
' List the headings
ActiveSheet.Cells(RowNum, ColNum) = "Filename"
ActiveSheet.Cells(RowNum, ColNum + 1) = "Date"
ActiveSheet.Cells(RowNum, ColNum + 2) = "Size"
ActiveSheet.Cells(RowNum, ColNum + 3) = "Path"
RowNum = 3
Set FileSysObj1 = CreateObject("Scripting.FileSystemObject")
CurrentLevel = 1
Set Folder1 = FileSysObj1.GetFolder(FolderName)
Set SubFolder1 = Folder1.subfolders
DoFolder = True
m = Folder1.subfolders.Count ' This triggers an error if folder is protected.
If DoFolder = True Then
' Now do the file bit.
Set FileColl = Folder1.Files
For Each FFileName In FileColl
If Format(Right(FFileName.Name, 3), ">") = "JPG" Or Format(Right(FFileName.Name, 3), ">") = "BMP" Or Format(Right(FFileName.Name, 3), ">") =
"GIF" Or Format(Right(FFileName.Name, 3), ">") = "TIF" Or Format(Right(FFileName.Name, 3), ">") = "PNG" Then
ActiveSheet.Cells(RowNum, ColNum) = FFileName.Name
ActiveSheet.Cells(RowNum, ColNum + 1) = FileDateTime(FFileName.Path)
ActiveSheet.Cells(RowNum, ColNum + 2) = Format(FFileName.Size / 1024, "0")
ActiveSheet.Cells(RowNum, ColNum + 3) = FFileName.Path
RowNum = RowNum + 1
If RowNum > 65000 Then
RowNum = 2
ColNum = ColNum + 8
End If
End If
Next
For Each SubFolderName1 In SubFolder1
CurrentLevel = 2
Set Folder2 = FileSysObj1.GetFolder(SubFolderName1)
Set SubFolder2 = Folder2.subfolders
DoFolder = True
m = Folder2.subfolders.Count ' This triggers an error if folder is protected.
If DoFolder = True Then
' Now do the file bit
Set FileColl = Folder2.Files
For Each FFileName In FileColl
If Format(Right(FFileName.Name, 3), ">") = "JPG" Or Format(Right(FFileName.Name, 3), ">") = "BMP" Or Format(Right(FFileName.Name, 3),
">") = "GIF" Or Format(Right(FFileName.Name, 3), ">") = "TIF" Or Format(Right(FFileName.Name, 3), ">") = "PNG" Then
ActiveSheet.Cells(RowNum, ColNum) = FFileName.Name
ActiveSheet.Cells(RowNum, ColNum + 1) = FileDateTime(FFileName.Path)
ActiveSheet.Cells(RowNum, ColNum + 2) = Format(FFileName.Size / 1024, "0")
ActiveSheet.Cells(RowNum, ColNum + 3) = FFileName.Path
RowNum = RowNum + 1
If RowNum > 65000 Then
RowNum = 2
ColNum = ColNum + 8
End If
End If
Next
For Each SubFolderName2 In SubFolder2
CurrentLevel = 3
Set folder3 = FileSysObj1.GetFolder(SubFolderName2)
Set SubFolder3 = folder3.subfolders
DoFolder = True
m = folder3.subfolders.Count ' This triggers an error if folder is protected.
If DoFolder = True Then
' Now do the file bit
Set FileColl = folder3.Files
For Each FFileName In FileColl
If Format(Right(FFileName.Name, 3), ">") = "JPG" Or Format(Right(FFileName.Name, 3), ">") = "BMP" Or Format(Right(FFileName.Name,
3), ">") = "GIF" Or Format(Right(FFileName.Name, 3), ">") = "TIF" Or Format(Right(FFileName.Name, 3), ">") = "PNG" Then
ActiveSheet.Cells(RowNum, ColNum) = FFileName.Name
ActiveSheet.Cells(RowNum, ColNum + 1) = FileDateTime(FFileName.Path)
ActiveSheet.Cells(RowNum, ColNum + 2) = Format(FFileName.Size / 1024, "0")
ActiveSheet.Cells(RowNum, ColNum + 3) = FFileName.Path
RowNum = RowNum + 1
If RowNum > 65000 Then
RowNum = 2
ColNum = ColNum + 8
End If
End If
Next
For Each subfoldername3 In SubFolder3
... And so it continues, to a 'depth' of 17 sub-folders!!!
As you can see, it's dying to be simplified. There's gotta be a better, quicker solution than this mess - please help me get out of this nightmare!!
