-
Sep 28th, 2006, 04:36 PM
#1
Thread Starter
New Member
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
-
Sep 28th, 2006, 11:26 PM
#2
Lively Member
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:
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
-
Sep 29th, 2006, 07:27 AM
#3
Thread Starter
New Member
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!
-
Mar 8th, 2009, 08:56 PM
#4
New Member
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.
-
Jun 11th, 2012, 07:43 PM
#5
New Member
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.
-
Feb 14th, 2013, 06:02 PM
#6
New Member
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!
-
Mar 12th, 2009, 11:22 PM
#7
Lively Member
Re: Reading a Binary File into Excel using VBA
-
Mar 12th, 2009, 11:28 PM
#8
New Member
Re: Reading a Binary File into Excel using VBA
Thank you. Will check it out.
-
Apr 6th, 2011, 05:15 AM
#9
New Member
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
-
Apr 8th, 2011, 07:05 PM
#10
Addicted Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|