[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 453
  • Last Modified:

trying to get a list of files and their sizes

I have a folder with about 4000 files. They have names like
homes_01-07-12_01-00-02.csv
homes_01-07-12_05-00-02.csv
homes_01-07-12_09-00-02.csv
homes_01-07-12_13-00-02.csv
homes_01-07-12_17-00-02.csv
homes_01-07-12_21-00-02.csv
homes_02-07-12_01-00-02.csv
homes_02-07-12_05-00-02.csv
homes_02-07-12_09-00-02.csv
homes_02-07-12_13-00-02.csv
homes_02-07-12_17-00-02.csv
homes_02-07-12_21-00-02.csv

The numbers there are dates and times. So the above files would have 6 for July 1 2012 and 6 for July 2nd. I was starting by using a macro I found online but I realized it won't truncate the names of the files so that I would get just the dates (and not the times and not the text 'homes') in the list below. Can any suggest a macro for this?

I want to generate a list like

dates      mb
7/1/2012      27217.853
7/1/2012      28168.047
7/1/2012      41171.635
7/1/2012      35745.411
7/1/2012      24420.961
7/1/2012      21502.624
7/2/2012      21201.785
7/2/2012      22540.027
7/2/2012      40807.439
7/2/2012      42141.273
7/2/2012      24477.951
7/2/2012      23296.789
7/3/2012      28573.014
7/3/2012      30432.085
7/3/2012      46762.471
7/3/2012      43764.891
7/3/2012      25950.057
7/3/2012      24627.604
7/4/2012      28138.032

The macro I found is here. Also, I don't understand the use of arguments in macros and when I try to run this macro it brings up the list of all macros in my workbook which is strange.
Function GetDirOrFileSize(strFolder As String, Optional strFile As Variant) As Long

'Call Sequence: GetDirOrFileSize("drive\path"[,"filename.ext"])
   
   Dim lngFSize As Long, lngDSize As Long
   Dim oFO As Object
   Dim oFD As Object
   Dim OFS As Object
   
   lngFSize = 0
   Set OFS = CreateObject("Scripting.FileSystemObject")

   If strFolder = "" Then strFolder = ActiveWorkbook.Path
   If Right(strFolder, 1) <> "\" Then strFolder = strFolder & "\"
   
   If OFS.FolderExists(strFolder) Then
     If Not IsMissing(strFile) Then
       
       If OFS.FileExists(strFolder & strFile) Then
         Set oFO = OFS.GetFile(strFolder & strFile)
         GetDirOrFileSize = oFO.Size
       End If
       
       Else
        Set oFD = OFS.GetFolder(strFolder)
        GetDirOrFileSize = oFD.Size
       End If
   
   End If
   
End Function   '*** GetDirOrFileSize ***

Open in new window

0
willie108
Asked:
willie108
  • 2
  • 2
1 Solution
 
krishnakrkcCommented:
Hi

try this one.

Sub kTest()
    
    Dim objFSO      As Object
    Dim MyFolder    As String
    Dim FName       As String
    Dim i           As Long
    Dim Fyle, f()
    
    MyFolder = "C:\Test"            '<< adjust to suit
    
    If Right$(MyFolder, 1) <> "\" Then MyFolder = MyFolder & "\"
    
    If Len(Dir(MyFolder, vbDirectory)) Then
        FName = Dir(MyFolder & "*.csv")
        If Len(FName) Then
            
            Set objFSO = CreateObject("scripting.filesystemobject")
            ReDim f(1 To objFSO.getfolder(MyFolder).Files.Count, 1 To 2)
            
            For Each Fyle In objFSO.getfolder(MyFolder).Files
                i = i + 1
                f(i, 1) = Mid(Fyle.Name, 7, 8)
                f(i, 2) = Fyle.Size / 1024
            Next
            
        End If
        
        If i Then
            Range("a1:b1") = [{"Dates", "Mb"}]
            Range("a2").Resize(i, 2) = f
        End If
    End If

End Sub

Open in new window


Kris
0
 
willie108Author Commented:
Wow Kris. No idea what you did but it works!
Cannot follow almost all of it but I will just ask one question. Why don't you need to declare the data type here
 Dim Fyle, f()
Is this just a way to declare an object data type?
0
 
willie108Author Commented:
Thanks!
0
 
krishnakrkcCommented:
Hi

Fyle should have been Object. f() is a variant data type since it holds numbers as well as string.

Kris
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now