convert filesize to kb mb gb etc in VBA

[AkaTrouble]

hi

i have a line in my VBA that currently converts the default bit file size to MB

Cells(NextRow, "D").Value = Format((objFile.Size / 1024 / 1024), "000") & " MB"

has anyone a way to do same thing but using KB, MB, GB, options

so if small file it shows in KB, medium medium file in MB and large file in GB

i thought about CF but as the entire sheet is created from the VBA routine I would like to do it as part of the loop


thanks for reading

 

[jasonb75]

Maybe something like

Select Case objFile.Size
    Case 0 To 1023
        Cells(nextrow, "D").Value = Format(objFile.Size, "000") & "B"
    Case 1024 To 104875
        Cells(nextrow, "D").Value = Format(ojbjFile.Size / 1024, "000") & "KB"
    Case 104876 To 1073741823
        Cells(nextrow, "D").Value = Format(ojbjFile.Size / 104876, "000") & "MB"
End Select

Add more lines as needed.

 

[RoryA]

It will depend on your definitions of small, medium and large - but you can use a Select Case statement like this:

Select case objFile.Size
Case 0 to 1000000
Cells(NextRow, "D").Value = Format((objFile.Size / 1024), "000") & " KB"
case 1000000 to 1000000000
Cells(NextRow, "D").Value = Format((objFile.Size / 1024 / 1024), "000") & " MB"
case else
Cells(NextRow, "D").Value = Format((objFile.Size / 1024 / 1024 / 1024), "000") & " GB"
End Select

 

[AkaTrouble]

thanks both options are good i slightly tweeked code so far looks ok

Select Case objFile.Size
        Case 0 To 1023
            Cells(NextRow, "D").Value = Format(objFile.Size, "0") & "B"
        Case 1024 To 104875
            Cells(NextRow, "D").Value = Format(objFile.Size / 1024, "0") & "KB"
        Case 104876 To 1073741823
            Cells(NextRow, "D").Value = Format(objFile.Size / 104876, "0") & "MB"
        Case 1073741824 To 1.11111111111074E+20
            Cells(NextRow, "D").Value = Format(objFile.Size / 1073741823, "0.00") & "GB"
    End Select

so thanks to both of you

 

[jasonb75]

I just noticed that I had some missing digits in the code which will give incorrect results for MB file sizes, corrected version below (including your tweaks).

Select Case objFile.Size
        Case 0 To 1023
            Cells(NextRow, "D").Value = Format(objFile.Size, "0") & "B"
        Case 1024 To 1048575
            Cells(NextRow, "D").Value = Format(objFile.Size / 1024, "0") & "KB"
        Case 1048576 To 1073741823
            Cells(NextRow, "D").Value = Format(objFile.Size / 1048576, "0") & "MB"
        Case 1073741824 To 1.11111111111074E+20
            Cells(NextRow, "D").Value = Format(objFile.Size / 1073741823, "0.00") & "GB"
    End Select

Would decimal results be preferable if the output is in KB / MB and say less than 100?

 

[AkaTrouble]

Would decimal results be preferable if the output is in KB / MB and say less than 100?

glad you came back i was scratching head as to what was happening

i thought about decimal options for lower cases but wondered if any need as cant see in this day of GB and TB that it would impact much