1

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,

  • For the first case, I can use FileLen which returns a Long value specifying the length of a file in bytes.
  • For the second case: I am not getting how to calculate the file size when user is editing the CSV file using macro.

Need help, Thanks in advance.

NJMR
  • 1,886
  • 1
  • 27
  • 46
  • Could you use [FileWatcher](https://social.msdn.microsoft.com/Forums/vstudio/en-US/391dd00a-61a7-4d28-8fd1-88a0047513b7/use-filesystemwatcher-in-access?forum=vsto)? And as idea: the large csv file could be splittted to smaller ones e.g. each 1000 lines goes to separate file. Then the original file will be closed and the processing starts with each of the small files. – Daniel Dušek Feb 21 '18 at 09:44

2 Answers2

1

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.

Michał Turczyn
  • 32,028
  • 14
  • 47
  • 69
  • 2
    Sorry forgot to mention, the file contains unicode chars also. – NJMR Feb 21 '18 at 09:47
  • Ok, so for handling unicode characters you can refer to [this SO post](https://stackoverflow.com/questions/5290182/how-many-bytes-does-one-unicode-character-take). – Michał Turczyn Feb 21 '18 at 09:51
1

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
NJMR
  • 1,886
  • 1
  • 27
  • 46
Vinayaka V
  • 41
  • 2