Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old August 2nd 05, 01:23 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2005
Posts: 1
Default 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
Search this Thread:

Advanced Search
Display Modes

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
Count files in a folder Wanna Learn Excel Discussion (Misc queries) 4 July 9th 08 05:46 PM
how can I specific a folder with wildcard criteria and excel will import all the correct files in that folder? Raven Excel Discussion (Misc queries) 1 January 24th 06 04:28 PM
Count files in folder gmunro Excel Programming 3 May 18th 05 10:44 PM
Count files in a Folder! SpeeD72 Excel Programming 1 September 11th 03 07:18 PM
Count files in a Folder! Bob Phillips[_5_] Excel Programming 0 September 11th 03 06:38 PM


All times are GMT +1. The time now is 07:00 AM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017