macro import file hex in excel

Originally Posted by martindwilson

a hex file? what is that. is it a text file with hex numbers?

I have to import the files with a programmer to read eeprom ... having an extension. bin and contain the hex.
tanks

If you have a bin(ary) file, then don't you want to open it in a hex editor to review/make changes? Apologies, i just can't see why you might open it in Excel...

Originally Posted by Firefly2012

If you have a bin(ary) file, then don't you want to open it in a hex editor to review/make changes? Apologies, i just can't see why you might open it in Excel...

I would like to open it in Excel because I have to perform calculations using the algorithm on the file and then save it in. bin, to rewrite the EEPROM

Originally Posted by snb

Not too complicated; reading as HEX file, converting to ascii

sub snb()
  Open "E:\file.bin" For Binary As #1
   c00 = Input(LOF(1), #1)
  Close #1

  redim sn(len(c00)+1)

  For j = 1 To len(c00) 
    sn(j) = Asc(Mid(c00, j, 1))
  Next

  cells(1)=join(sn,"")
End sub

TANKS for macro but I try a macro that displays the values ​​of excel a BIN file in hex, sorted as if it were an editor and divided by single byte.

Originally Posted by martindwilson

i still don't understand, what would an example excel file look like?

0 1 2 3 4 5 6 7 8 9 a b c d e f
0 af 12 bf ff 44 5f 6f ff ff 12 f1 34 55 89 ff ac
10 5f 6f ff ff 12 ff 44 5f 6f ff 5f 6f ff ff 12 12
20 f1 34 f1 34 55 89 ff ac ff 44 5f 6f ff ff 12 12
30 6f f1 34 55 89 f1 34 55 89 ff ac ff 44 5f 6f ff
40 5f 6f ff ff 12 5f 6f ff ff 12 ff 44 5f 6f ff ff
50 bf ff 44 5f 6f ff ff 12 f1 34 55 89 ff ac ff ff
60 f1 34 55 89 5f 6f ff ff 12 12 f1 34 55 89 ff ac
70 f1 34 55 89 5f 6f ff ff 12 12 f1 34 55 89 ff ac
80 f1 34 55 89 ff ac ff 44 5f 6f ff ff 12 ff 44 5f
90 ff 44 5f 6f ff bf ff 44 5f 55 89 ff ac ff 44 5f
100 f1 34 bf ff 44 5f 6f ff ff 12 f1 34 55 89 ff ac

Originally Posted by snb

Yes I see, but are you aware that a cell in Excel can only show a limited amount of characters ?
So where do you want the hex characters to be shown in Excel ??

ok, every single cell in excel it must contain two characters (a single bite (AB) ok ?????
see attached sample file first

thanks for the macro, but I have two problems:
1) the last cell of the first line is empty and the value of that cell is inserted into the cell below, so moving 'the last column at the bottom of a cell;
2) values ​​in individual cells are displayed as text but I would display them as hexadecimal values ​​as I have to make calculations.
You can edit the macro please
thanks a lot

tanks but when play macro :

index out of range

Originally Posted by protonLeah

When I was writing/testing the macro, I used a file from my own computer and entered the output to a worksheet I named BEN. The mod below will write the output to what ever the active sheet is. But you must still tell the macro where the binary file is located >> change the BLUE portion of the code below.

Option Explicit
Sub BinToHex()
    Dim ColumnNumber    As Long, _
        RowNumber       As Long, _
        Ndx             As Long, _
        InBucket        As Variant
     
    Open "c:\downloads\encoding.bin" For Binary As #1
    InBucket = Input(LOF(1), #1)
    Close #1
    RowNumber = 1
    ColumnNumber = 0

    For Ndx = 1 To Len(InBucket)
        ColumnNumber = ColumnNumber + 1
        Cells(RowNumber, ColumnNumber) = Hex(Asc(Mid(InBucket, Ndx, 1)))
        If (Ndx Mod 16) = 0 Then
            RowNumber = RowNumber + 1
            ColumnNumber = 0
        End If
    Next Ndx
  End Sub

1. What is the path and name of your file ?
2. Where did you copy the code (did you put it in a worksheet module)?

TANKS, works perfectly ....... but I still need you !!!!!!
I'm sorry .... Once I changed the file in excel how do I save it in. BIN?? I need to calculate a few bytes to join 4-cell, and I do it with the following function: "= A1&A2&A3&A4 "..... but then how do I rewrite the result obtained on 4 separate cells?hopefully last question: can you give me create a macro that I want to ask me to upload files without having to change each time the macro??
tanks
tanks

Originally Posted by tatop

TANKS, works perfectly ....... but I still need you !!!!!!
I'm sorry .... Once I changed the file in excel how do I save it in. BIN?? I need to calculate a few bytes to join 4-cell, and I do it with the following function: "= A1&A2&A3&A4 "..... but then how do I rewrite the result obtained on 4 separate cells?hopefully last question: can you give me create a macro that I want to ask me to upload files without having to change each time the macro??
tanks
tanks

UP !!!!!! tankssss

Does the EEPROM use Big- or Little-endian data? That may well determine how you want the data extracted and written to the file.

Originally Posted by protonLeah

tatop seems to be done but I thought I would post the update anyway:

Option Explicit
Option Base 1
Sub BinToHex()
    Dim ColumnNumber    As Long, _
        RowNumber       As Long, _
        Ndx             As Long, _
        handleNumber    As Long, _
        InBucket        As Variant, _
        FileToOpen      As String
    
    'get the name of the binary file to open
    FileToOpen = Application.GetOpenFilename("Binary files (*.bin),*.bin")
    
    ' get the next handle number from windows
    handleNumber = FreeFile
    
    'open the file, read into a string
    Open FileToOpen For Binary As handleNumber
        InBucket = Input(LOF(handleNumber), handleNumber)
    Close handleNumber
    
    RowNumber = 1
    ColumnNumber = 0

    ' for each byte of the string, convert to decimal integer value 0 - 256,
    ' convert the integer to hex
    
    For Ndx = 1 To Len(InBucket)
        ColumnNumber = ColumnNumber + 1
        Cells(RowNumber, ColumnNumber) = Hex(Asc(Mid(InBucket, Ndx, 1)))
        If (Ndx Mod 16) = 0 Then
            RowNumber = RowNumber + 1
            ColumnNumber = 0
        End If
    Next Ndx
  End Sub

Sub SaveAsBinary()
    Dim LastRow     As Long, _
        RowNdx      As Long, _
        ColNdx      As Long, _
        FileNum     As Long, _
        t0          As Double, _
        FName
        
    FName = Application.GetSaveAsFilename
    t0 = Timer

    LastRow = Cells(Rows.Count, "R").End(xlUp).Row
    ReDim outarray(1 To LastRow, 1 To 16)
    
    For RowNdx = 1 To LastRow
        For ColNdx = 1 To 16
            ' convert the cell hex back to decimal and replace with the character string
            outarray(RowNdx, ColNdx) = Chr(WorksheetFunction.Hex2Dec(Cells(RowNdx, ColNdx).Value))
        Next ColNdx
    Next RowNdx
    
    'open the file and save the array
    FileNum = FreeFile
    Open FName For Binary As FileNum
    Put FileNum, , outarray
    Close FileNum
    Debug.Print "elapsed time ", Timer - t0
End Sub

tanks , the first macro is OK...loaf file OK
but the macro SaveAsBinary dont go!!!
show this error:
error of runtime "438"
Property or method not supported

can u correct , please

thank again !!!!!!

up ! ! ! ! !

error 438.jpg

error of runtime "438"
Property or method not supported

Attachment 137838

error of runtime "438"
Property or method not supported

Originally Posted by protonLeah

Sorry, the save macro runs on my machine. I'm not familiar with runtime error 438. Can you step through the "save" macro to see which line is highlighted when it stops?

I'm sorry, the macro in Excel 2007 works, the problem was that I was using Excel 2003. I have it converted and it works .... only problem is that, hex conversion is incorrect, because when I save the bin file I see only data wrong.
my new code is:

Sub SaveAsBinary()
    Dim LastRow     As Long, _
        RowNdx      As Long, _
        ColNdx      As Long, _
        FileNum     As Long, _
        t0          As Double, _
        FName
        
    FName = Application.GetSaveAsFilename
    t0 = Timer

    LastRow = Cells(Rows.Count, "R").End(xlUp).Row
    ReDim outarray(1 To LastRow, 1 To 16)
    
    For RowNdx = 1 To LastRow
        For ColNdx = 1 To 16
            ' convert the cell hex back to decimal and replace with the character string
            outarray(RowNdx, ColNdx) = Evaluate("Chr(Clng(RowNdx, ColNdx).Value)))")
        Next ColNdx
    Next RowNdx
    
    'open the file and save the array
    FileNum = FreeFile
    Open FName For Binary As FileNum
    Put FileNum, , outarray
    Close FileNum
    Debug.Print "elapsed time ", Timer - t0
End Sub