GetDetailsOf File Size (converting numbers)

[wjvghost]

Hello,

I have a module that pulls the metadata for certain fields from files in a folder and displayed into Excel.

I am having some difficulty understanding how to 'standardize' the file size of all files listed when Excel points to a directory.

This module is being used in conjunction with others in a spreadsheet to screen image files in a folder to determine if they meet the criteria to be usable.

I am having difficulty comparing the sizes of these files against the criteria that is used because they are being displayed in byte, kilobytes, and megabytes.

For clarification, these are being returned by the values shown below using objFolder.GetDetailsOf(strFileName, i) while one of these loops returns the file size.

  [TABLE]
<tbody>[TR]
[TD="class: xl63"][B]Size[/B][/TD]
[/TR]
[TR]
[TD]53 bytes[/TD]
[/TR]
[TR]
[TD]846 KB[/TD]
[/TR]
[TR]
[TD]345 KB[/TD]
[/TR]
[TR]
[TD]261 KB[/TD]
[/TR]
[TR]
[TD]1.52 MB[/TD]
[/TR]
[TR]
[TD]294 KB[/TD]
[/TR]
[TR]
[TD]1.63 MB[/TD]
[/TR]
[TR]
[TD]319 KB[/TD]
[/TR]
[TR]
[TD]222 KB[/TD]
[/TR]
[TR]
[TD]300 KB[/TD]
[/TR]
</tbody>[/TABLE]

I am using a separate module for comparing the file sizes to what I need them to be, so the only I need this to do is display all loaded files in a kb format. Is there a way to convert any values of this column in bytes or megabytes INTO kilobytes after the files have been loaded?

 

[Tetra201]

Try using the native VBA FileLen function -- it returns file size in bytes, which is easily convertible to kilobytes.

 

[Leith Ross]

Hello wjvghost,

Here is macro to convert the file size text in column "A" into kilobytes in column "B".

Sub ConvertToKB()


    Dim ByteID  As String
    Dim Cell    As Range
    Dim n       As Double
    Dim Rng     As Range
    Dim RngEnd  As Range
    Dim Size    As Double
    Dim Wks     As Worksheet
    
        Set Wks = Worksheets("Sheet2")
        
        Set Rng = Wks.Range("A1")
        Set RngEnd = Wks.Cells(Rows.Count, "A").End(xlUp)
        
        If RngEnd.Row < Rng.Row Then Exit Sub
        If RngEnd.Row > Rng.Row Then Set Rng = Wks.Range(Rng, RngEnd)
        
        For Each Cell In Rng
            n = InStrRev(Cell, " ")
            ByteID = Right(Cell, Len(Cell) - n)
            Size = Val(Left(Cell, n - 1))
                Select Case ByteID
                    Case Is = "bytes": n = 0.001
                    Case Is = "KB": n = 1
                    Case Is = "MB": n = 1000
                    Case Is = "GB": n = 1000000
                End Select
            Size = Size * n
            Cell.Offset(0, 1) = Size
        Next Cell
            
End Sub

 

[wjvghost]

Hello thank you for the replies!

Unfortunately I am busy for the rest of this week through the weekend, so I won't be able to test these out until sometime next week.

However, from the looks of it - this is what I was needing.

 

[wjvghost]

Hello wjvghost,

Here is macro to convert the file size text in column "A" into kilobytes in column "B".

Sub ConvertToKB()


    Dim ByteID  As String
    Dim Cell    As Range
    Dim n       As Double
    Dim Rng     As Range
    Dim RngEnd  As Range
    Dim Size    As Double
    Dim Wks     As Worksheet
    
        Set Wks = Worksheets("Sheet2")
        
        Set Rng = Wks.Range("A1")
        Set RngEnd = Wks.Cells(Rows.Count, "A").End(xlUp)
        
        If RngEnd.Row < Rng.Row Then Exit Sub
        If RngEnd.Row > Rng.Row Then Set Rng = Wks.Range(Rng, RngEnd)
        
        For Each Cell In Rng
            n = InStrRev(Cell, " ")
            ByteID = Right(Cell, Len(Cell) - n)
            Size = Val(Left(Cell, n - 1))
                Select Case ByteID
                    Case Is = "bytes": n = 0.001
                    Case Is = "KB": n = 1
                    Case Is = "MB": n = 1000
                    Case Is = "GB": n = 1000000
                End Select
            Size = Size * n
            Cell.Offset(0, 1) = Size
        Next Cell
            
End Sub

This worked perfectly for what I needed!

Thank you much

 

[Leith Ross]

Hello wjvghost,

You're welcome.