Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Folder Inventory & Count for like named files
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count files in a folder | Excel Discussion (Misc queries) | |||
how can I specific a folder with wildcard criteria and excel will import all the correct files in that folder? | Excel Discussion (Misc queries) | |||
Count files in folder | Excel Programming | |||
Count files in a Folder! | Excel Programming | |||
Count files in a Folder! | Excel Programming |