trying to get a list of files and their sizes

Posted on 2012-08-27
Last Modified: 2012-08-28
I have a folder with about 4000 files. They have names like

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
        Set oFD = OFS.GetFolder(strFolder)
        GetDirOrFileSize = oFD.Size
       End If
   End If
End Function   '*** GetDirOrFileSize ***

Open in new window

Question by:willie108
    LVL 18

    Accepted Solution


    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
            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


    Author Comment

    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?

    Author Closing Comment

    LVL 18

    Expert Comment


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


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
    This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
    This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now