I am looking for VBA to export a binary file (in hex) into Excel.
I currently use a hex editor to open the binary file, and set it to show 100 bytes in each row. The hex output looks something as follows:
32 30 43 4D 54 58 03 00 00 00 ... 100 bytes long ... 00 00 38 58 03 00
00 00 00 00 4D D7 FF 89 A4 24 ... 100 bytes long ... 4C 24 00 00 00 00
00 00 00 00 00 00 00 00 00 00 ... 100 bytes long ... 00 00 00 00 00 00
00 00 00 00 00 00 00 00 00 00 ... 100 bytes long ... 00 00 00 00 00 00
00 00 00 00 00 00 00 00 00 00 ... 100 bytes long ... 00 00 00 00 00 00
00 00 00 00 D6 2C 94 35 B6 00 ... 100 bytes long ... 77 0F D9 1B 82 33
98 51 3A 00 A1 6E D1 51 E7 2F ... 100 bytes long ... CD 5A B2 31 5E 30
etc.
I then select all of this, copy and paste into Excel, and use text to columns to fill a range with all of the hex values (100 columns wide, by ~2,000 rows long).
Instead, I want to automatically extract the hex into Excel without having to first open the file in a hex editor, copy and paste, etc.
I found the following code that is able to pull out one byte at a time, but there are two issues:it imports in decimal (instead of hex), and it places one value in a row, then the next value in the next row, etc.
Instead, I need the first value in A1, the second value in B1, the third value in C1, etc. Until it has filled up to A100, then start in the next row. Byte 101 in B1, the next in B2, etc.
I hope that makes sense!
Here is the code I used:
Sub import_hex()
Dim intFileNum%, bytTemp As Byte, intCellRow%
intFileNum = FreeFile
intCellRow = 0
Open "import.ros" 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
I am almost there, but the main issues are:
- Need to paste directly in hex, (not decimal first and then using DEC2HEX to convert to hex)
- Fill 100 cells in row 1, before moving to row 2, fill 100 cells, move to row 3, etc.
Thanks!
Bookmarks