how can I read a binary file using VBA?

Question

I have a binary file that resulted from a program written in Compaq Visual Fortran. How can I read specific lines and save them in an Excel sheet?

How does a binary file have "lines"? – Ryann Graham Mar 27 '09 at 20:11 — Ryann Graham, Mar 27 '09 at 20:11

score 10 · Answer 1 · edited Apr 03 '18 at 20:44

If you want to read the entire file into one big array, you can use the following code:

Dim byteArr() As Byte
Dim fileInt As Integer: fileInt = FreeFile
Open "C:\path\to\my\file.ext" For Binary Access Read As #fileInt
ReDim byteArr(0 To LOF(fileInt) - 1)
Get #fileInt, , byteArr
Close #fileInt

The result is identical to the answer by Todd Owen, but achieved without the use of external libraries.

score 8 · Answer 2 · answered Dec 04 '09 at 06:56

8

Another way is using ADODB.Stream:

With CreateObject("ADODB.Stream")
    .Open
    .Type = 1  ' adTypeBinary
    .LoadFromFile file.Path
    bytes = .Read
    .Close
End With

(Sorry, I'm not actually sure what library it's in, which is why this sample code uses CreateObject and the literal value 1 instead of the named constant adTypeBinary!)

answered Dec 04 '09 at 06:56

Todd Owen

15,650
7
54
52

The library is Microsoft Active Data Object. – S Meaden Oct 20 '17 at 22:00
1

why using an external library for such a trivial and built-in task? – jumpjack May 02 '20 at 16:43
Refrence Microsoft ActiveX Data Objects x.x Library – Noam Brand Oct 17 '22 at 21:37
Dim bytes As Variant – Noam Brand Oct 17 '22 at 21:38

score 7 · Answer 3 · answered Mar 19 '09 at 02:41

You have to open it using "Binary Access".

See http://www.vbforums.com/showthread.php?t=430424

Sub Temp()
    Dim intFileNum%, bytTemp As Byte, intCellRow%
    intFileNum = FreeFile
    intCellRow = 0
    Open "C:\temp.bin" For Binary Access Read As intFileNum
    Do While Not EOF(intFileNum)
        intCellRow = intCellRow + 1
        Get intFileNum, , bytTemp
        Cells(intCellRow, 1) = bytTemp
    Loop
    Close intFileNum
End Sub

how can I read a binary file using VBA?

3 Answers3

Linked