Results 1 to 10 of 10

Thread: Reading a Binary File into Excel using VBA

Hybrid View

  1. #1

    Thread Starter
    New Member
    Join Date
    Sep 2006
    Posts
    2

    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

  2. #2
    Lively Member okosv's Avatar
    Join Date
    Sep 2006
    Posts
    95

    Re: Reading a Binary File into Excel using VBA

    This macros read bytes from binary file 'C:\temp.bin' and put it in excel
    VB Code:
    1. Sub Temp()
    2.     Dim intFileNum%, bytTemp As Byte, intCellRow%
    3.     intFileNum = FreeFile
    4.     intCellRow = 0
    5.     Open "C:\temp.bin" For Binary Access Read As intFileNum
    6.     Do While Not EOF(intFileNum)
    7.         intCellRow = intCellRow + 1
    8.         Get intFileNum, , bytTemp
    9.         Cells(intCellRow, 1) = bytTemp
    10.     Loop
    11.     Close intFileNum
    12. End Sub

  3. #3

    Thread Starter
    New Member
    Join Date
    Sep 2006
    Posts
    2

    Re: Reading a Binary File into Excel using VBA

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

  4. #4
    New Member
    Join Date
    Mar 2009
    Posts
    4

    Re: Reading a Binary File into Excel using VBA

    Hi

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


    Thanks in advance.

  5. #5
    New Member
    Join Date
    Jun 2012
    Location
    It's more fun in the Philippines
    Posts
    1

    Re: Reading a Binary File into Excel using VBA

    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.

  6. #6
    New Member
    Join Date
    Feb 2013
    Posts
    3

    Exclamation Re: Reading a Binary File into Excel using VBA

    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 ◦ ◦
    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!

  7. #7
    Lively Member okosv's Avatar
    Join Date
    Sep 2006
    Posts
    95

    Re: Reading a Binary File into Excel using VBA

    Use another method, ie
    Code:
    Put

  8. #8
    New Member
    Join Date
    Mar 2009
    Posts
    4

    Re: Reading a Binary File into Excel using VBA

    Thank you. Will check it out.

  9. #9
    New Member
    Join Date
    Apr 2011
    Posts
    1

    Re: Reading a Binary File into Excel using VBA

    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

  10. #10
    Addicted Member
    Join Date
    Jul 2009
    Posts
    208

    Re: Reading a Binary File into Excel using VBA

    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), "")

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width