This should work for you; the macro will write the inventory results to
the active worksheet in Excel:
Code:
--------------------
Sub InventoryJPGFiles()
' Declare variables...
Dim fs, f, f1, fc, fCategory, fType, catChk, NewCatCount, jpgCount
' Define variables...
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder("C:\Temp\test3")
Set fc = f.Files
' Initialize variables...
catChk = ""
NewCatCount = 0
jpgCount = 0
' Check all files in the folder...
For Each f1 In fc
' Get the first 6 characters (file category) of file name...
fCategory = Left(f1.Name, 6)
' Consider only .jpg files for the inventory...
fType = fs.GetExtensionName(f1.Name)
If fType = "jpg" Then
' Increment jpg inventory counter...
jpgCount = jpgCount + 1
' If first jpg file, add 1st row to Excel to track inventory...
If jpgCount = 1 Then
' Increment the file category counter...
NewCatCount = 1
' Add file category to Excel in Column A....
ActiveSheet.Cells(1, 1).Value = fCategory
' Add inventory count to Excel in Column B...
ActiveSheet.Cells(1, 2).Value = jpgCount
' Skip to next file since first jpg is inventoried...
GoTo NextFile
Else
' If new file category, add to next row of Excel...
If fCategory < catChk Then
NewCatCount = NewCatCount + 1
' Add file category in Column A...
ActiveSheet.Cells(NewCatCount, 1).Value = fCategory
' Add inventory count in Column B...
ActiveSheet.Cells(NewCatCount, 2).Value = 1
Else
' Otherwise add count to existing file category...
CurrCatCount = ActiveSheet.Cells(NewCatCount, 2).Value
' Add inventory count in Column B...
ActiveSheet.Cells(NewCatCount, 2).Value = CurrCatCount + 1
End If
End If
NextFile:
' Set variable used to compare files for new/existing category
catChk = fCategory
End If
Next
' When all files are inventoried, add Inventory Total to worksheet...
ActiveSheet.Cells(NewCatCount + 1, 1).Value = "TOTAL:"
ActiveSheet.Cells(NewCatCount + 1, 2).Value = jpgCount
End Sub
--------------------
Hope this helps,
theDude
--
theDude
------------------------------------------------------------------------
theDude's Profile:
http://www.excelforum.com/member.php...o&userid=16550
View this thread:
http://www.excelforum.com/showthread...hreadid=391110