VBA LoadFromFile crashes on large files-CodePudding

I try to load chunks of a (really) large file in VBA:

Set dataStream = CreateObject("ADODB.Stream")

dataStream.Type = adTypeBinary
dataStream.Open
dataStream.LoadFromFile localDirectory & "\" & objFile.Name

byteBuffer = dataStream.Read(bufferSize)

If I understand correctly, the only amount of memory needed at a given time is bufferSize. Still, Access crashes at the LoadFromFile statement.

Is there a more robust way to read chunks from large files in VBA than ADODB.Stream?

I already tried How to Transfer Large File from MS Word Add-In (VBA) to Web Server? (but that has problems with large files, too. Get fails unpredictably with Error 63).

CodePudding user response：

Command dataStream.LoadFromFile tries to load the entire file into RAM. You can check it yourself through the Task Manager. Simple methods give more opportunities, so I suggest it be simpler. Below is the code that I applied to a 50GB file – and my computer didn't experience any problems, except that processing such a file will take a lot of time. But nothing freezes, and the process can be controlled as you like.

Dim fname As String
Dim fNo As Integer
Const bufferSize = 1024
Dim nextBytes(bufferSize) As Byte
Dim offset As Variant

fname = "C:\Path\BinaryFile.vdi"
fNo = FreeFile()
Open fname For Binary Access Read As #fNo
offset = 1
Do
    Get #fNo, offset, nextBytes
    offset = offset   bufferSize
    ' Do Something
    If EOF(fNo) Then Exit Do
Loop
Close #fNo

CodePudding user response：

Ok, here's how I solved it

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(localDirectory)
Set sourceFile = objFSO.OpenTextFile(localDirectory & "\" & objFile.Name)
strChunk = sourceFile.Read(bufferSize)

and then convert the string to a byte array:

Function StringToByteArray(str As String) As Byte()
Dim i As Long
Dim b() As Byte
ReDim b(Len(str) - 1) As Byte
For i = 1 To Len(str)
    b(i - 1) = Asc(Mid(str, i, 1))
Next i
StringToByteArray = b
End Function

The usual StrConv method does not work correctly! Therefore the method.