Getting count of objects in excel without opeining folder

[pragnesh_50]

Hi,


I have Shared Drive having different folders for each Retailers.


In each folder every day JPG (images) are loaded....like 500 objects, 400 objects etc...


I want following output in excel sheet column wise without opening such folders:


1. Name of the folder in which images(JPG) Loded.

2. Count of object for respective folder.

2. Time when the images loaded for respective retailer. (Date modaified or date created)....with 10:am...12am etc...


Is it really possible in excel by VBA?


Regards,

Pragnesh

 

[Luke M]

Compiling several macros together. This gives folder name and count of jpg's. Not sure how you want the time since if there's multiple files, there will be multiple times. Note t

[pre]

Option Explicit
Dim myRow As Long
Dim wks As Worksheet
Dim RecordCount As Integer
Sub MainMacro()
'This is the main macro to run
Set wks = ActiveSheet
myRow = 0
'What row do we start records on?
RecordCount = 1
Call FoldersInFolder("C:My Documents") '<-- change this
End Sub
Sub FoldersInFolder(myFolderName As String)

Dim FSO As Object
Dim myBaseFolder As Object
Dim myFolder As Object
Set FSO = CreateObject("scripting.filesystemobject")

Set myBaseFolder = FSO.GetFolder(myFolderName)

For Each myFolder In myBaseFolder.SubFolders
Call ListAllFile(myFolder.Path)

'If you need to include subfolders uncomment this next line
'Call FoldersInFolder(myFolder.Path)

Next myFolder

End Sub

Sub ListAllFile(SearchFolder As String)

Dim MyPath As String, FilesInPath As String
Dim MyFiles() As String
Dim Fnum As Long

MyPath = SearchFolder & ""
'If there are no Excel files in the folder exit the sub
FilesInPath = Dir(MyPath & "*.jpg")
If FilesInPath = "" Then
Exit Sub
End If

'Fill the array(myFiles)with the list of Excel files in the folder
Fnum = 0
Do While FilesInPath <> ""
Fnum = Fnum + 1
ReDim Preserve MyFiles(1 To Fnum)
MyFiles(Fnum) = FilesInPath
FilesInPath = Dir()
Loop
'Print out our info
Cells(RecordCount, "A") = SearchFolder
Cells(RecordCount, "B") = Fnum
RecordCount = RecordCount + 1

End Sub

[/pre]
Credit to:

http://www.ozgrid.com/forum/showthread.php?t=65530

http://www.rondebruin.nl/copy3.htm

 

[pragnesh_50]

Hi,


Grate .....that's worked....Hurra!


If particular folder contains 500 .JPG objects with names like for ex. like P030711_10.26...etc than i would like to reconstruct that folder with object name like for this "p030711.jpg".


That means i like to reconstruct original folder containing objects name with "_" "/" "-" in to plain text object name without special characher....So that i can use that in further process.


Is it possible.


Regards,

Pragnesh

 

[Luke M]

Sure. Just need to change the last sub a little:

[pre]

Sub ListAllFile(SearchFolder As String)

Dim MyPath As String, FilesInPath As String
Dim MyFiles() As String
Dim Fnum As Long
Dim xName

MyPath = SearchFolder & ""
'If there are no Excel files in the folder exit the sub
FilesInPath = Dir(MyPath & "*.jpg")
If FilesInPath = "" Then
Exit Sub
End If

'Fill the array(myFiles)with the list of Excel files in the folder
Fnum = 0
Do While FilesInPath <> ""
'Here's the new bit of code the removes unwanted
'symbols from name
xName = WorksheetFunction.Substitute(WorksheetFunction.Substitute( _
WorksheetFunction.Substitute(FilesInPath, "_", ""), "-", ""), "/", "")
Name MyPath & FilesInPath As MyPath & xName

Fnum = Fnum + 1
ReDim Preserve MyFiles(1 To Fnum)
MyFiles(Fnum) = FilesInPath
FilesInPath = Dir()
Loop
'Print out our info
Cells(RecordCount, "A") = SearchFolder
Cells(RecordCount, "B") = Fnum
RecordCount = RecordCount + 1

End Sub

[/pre]

 

[pragnesh_50]

Hi Luke,


It is really wonderful.


But i want the time details when particular folder is created.


ex. i have main folder for retailer Delhaize.

Now every Wednesday in this folder new folder are created so i need to note the time and date when it was created because I get many such folders in a day.


Regards,

Pragnesh