Reading a Binary File into Excel using VBA

Everyone,

I'm trying to write a program that takes a binary file that contains 5000, short (8 bit) words and spits them out into an excel sheet in individual cells, preferably each in it's own row.

This file is written by an Allen-Bradley CompactLogix PLC (programmable logic controller) and I need to extract the data for analysis. The test program I wrote for the PLC wrote 5000 instances of the integer 3277 to the file but when I open the file with a text editor, I get garbage. I'm assuming it's trying to display the ASCII equivalent of each 8 bit word. For example, the file looks like this, only 5000 times:

್

What the PLC actually does, is split the 3277 integer into two short ints and writes the binary equivalent to the file.

Bottom line: I want to take data eight bits at a time from a binary file, place them into an individual cell, convert them back to decimal and analyze. Any help would be greatly appriciated.

Kevin

This macros read bytes from binary file 'C:\temp.bin' and put it in excel

VB Code:

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

I knew it had to be something simple, I owe you big time. Thanks a lot!

Hi

Can any one let me know how to reverse this process.. ie to create the binary file from decimal ?


Thanks in advance.

Use another method, ie

Code:

---

Put

---

Thank you. Will check it out.

Hi, i have similar question. I want to read binary file in VBA line by line? in simple words i want to read each character till it read End Of Line then it goes to second line. vbCrLf is not seems to work.

Kind regards,
Haq

Try just vbCr or vbLf then. Or maybe they are fixed-length records, so wouldn't require 'End of Line' byte(s). To determine this, use

Debug.Print Hex(bytTemp), IIf(bytTemp >= 32, Chr(bytTemp), "")

Hi Okosv,

Good day!

I just joined the forum and started reading earlier posts/questions regarding binary file. I came acrose your code and tried ran it:

=========================================================================
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

=======================================================================

It was able to read the binary file, 512 bytes in size. There are 512 rows of 1 byte converted to decimal. Can this be read as they are, byte in hexadecimal?

I manipulated the hexadecimal data (1 byte) and the sheet is still single column with 512 rows. How do you save back to the same file, in this case: "c:\temp.bin"?

I tried several of the "save as" binary formats in 2007 Excel but the resulting files are 2Kbytes long and there are additional blank spaces and other stuff.

Thanks and really appreciate your help in advance.

I just have a problem to read a binary file. I can read it, but it is like those below. How can I convert those codea to some numbers or texts?



001013616 1 ◦ ◦ M 1 M ◦ C 1 + ◦ 9 1 b ◦
001013632 . 1 ◦ ◦ # 1 ◦ ◦ ◦ 1 ◦ ◦ ◦ 1 ◦ ◦
:eek:001013648 ◦ 1 , ◦ ◦ 0 w | n 1 w 5 f 1 ◦ T
001013664 ] 1 ◦ ◦ S 1 ◦ ◦ I 1 ◦ ◦ > 1 ◦ ◦
001013680 3 1 O ◦ ( 1 ◦ ◦ ◦ 1 ◦ ◦ ◦ 1 f o
001013696 ◦ 1 ◦ ◦ ◦ 0 ◦ ) ◦ 1 ◦ ◦ y 1 & b
001013712 r 1 ◦ ◦ j 1 ◦ ◦ a 1 " h X 1 ` ◦
001013728 N 1 ◦ ◦ C 1 ◦ ◦ 8 1 v ◦ - 1 ◦ G
001013744 # 1 ◦ ◦ ◦ 1 ◦ d ◦ 1 ◦ o ◦ 1 1 ◦
001013760 ◦ 0 E ◦ ◦ 0 ◦ ◦ ◦ 0 i n ◦ 0 ◦ B
001013776 ◦ 0 ◦ ◦ ◦ 0 r ◦ ◦ 0 e a ◦ 0 S j
001013792 ~ 0 ◦ ~ g 0 ◦ ◦ Q 0 ◦ E > 0 4 ◦
001013808 ◦ 1 M ◦ ◦ 1 z 2 ◦ 1 v j { 1 ◦ ◦
001013824 t 1 ◦ ◦ m 1 E ◦ d 1 q J [ 1 ' ◦
001013840 Q 1 ◦ N G 1 U ◦ < 1 ◦ ◦ 2 1 ◦ /
001013856 ( 1 ^ ◦ ◦ 1 ◦ / ◦ 1 ◦ ` ◦ 1 ◦ V
001013872 ◦ 1 ; ◦ ◦ 0 Y ◦ ◦ 0 ◦ ◦ ◦ 0 ◦


thank you for helping!