CODEWITHSUNDEEP
vbaexcel
NJMR
NJMR

Reputation: 1926

Calculate the file size - VBA macro

I am working on a VBA macro which will process a CSV file by creating a temporary file. I am using Microsoft Excel 2010 32-bit. The 32-bit versions have the 2 Gigabyte memory limits.

The size of CSV file that is being processed using macro is > 1GB. As stated previously, while processing the CSV file we are saving it to a temporary *xls file. So the total size while processing the CSV file will be the size of CSV file + size of the temp xls file that we are creating during the processing. The size of both the files together is beyond the limit of 2GB and hence excel is crashing.

We have 2 scenarios:

  1. When the user opens a CSV file using macro, I show a message box to the user if the user opens a CSV file whose size is > 1GB and close the excel.
  2. When the user opens a CSV file using macro, and the CSV file size < 1GB and then user adds some data to it which will make its size > 1GB, I have to show a message box to the user as soon as the file size reaches 1GB and close the excel without losing any data.

My question,

Need help, Thanks in advance.

Upvotes: 1

Views: 3962

Answers (2)

Vinayaka V
Vinayaka V

Reputation: 41

The below code might help you, in the below code i have used 1 MB as reference. you can change it for 1GB or based on your attributes. 

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Call FileSize
End Sub

Sub FileSize()
    Dim LResult As Long
    strFileFullName = ActiveWorkbook.FullName
    LResult = FileLen(strFileFullName)
    If LResult > 1000 Then
        MsgBox "FileSize is large " & (LResult)
        ActiveWorkbook.Close (savechanges)
    End If
End Sub

Upvotes: 1

Michał Turczyn
Michał Turczyn

Reputation: 37367

One character = 1 byte. So when user modifies CSV you can track chnges in the following manner:

On cell-value-changed event you add amount of characters in cell + 1 (for comma or any other separator) as amount of bytes to size of file at the beginning. This way you control the size during modifying file.

Keep in mind, if cell already had "something" in it, you have to subtract this value (amount of characters/bytes), as it will be overwritten.

Upvotes: 1

Related Questions