Values ranging from 0 to 255 in Excel's cells to bytes in a binary file

[atferrari]

In the lack of a more suitable forum, I am posting this here.

I need to create a binary file with values previously calculated in Excel.

The amount of data precludes anything manual.

Can anyone tell what is the straightest way to convert every value in the cell to a byte with possible values ranging from 0 to 255?

Sorry but I forgot all my VB and I am not C or C# conversant.

Help is much appreciated.

 

[samovar]

I don't think you can do this without programming.

You can save it as a .csv file and then use a little program to convert .csv to binary.

 

[Pommie]

Here is an excel sheet that will take a table at B2 and convert it to source, make your table as wide and as long as you wish and it'll find the dimensions. It places the source on the clip board so just paste it where you need it.

Have fun,

Edit, just noticed you said byte - I assumed you needed source code! Do you need a binary file? If so, post an example of the data you need converting.

Mike.
Actually, you only need the macro, add a module to your sheet and add,

'This will convert data starting at location B2 into
'source code.

Dim MyData As DataObject

Sub Convert()
Dim Out As String
    Row = 2
    Out = ""
    Do While Not IsEmpty(Cells(Row, 2))
        Col = 2
        Line = Chr(9) & Chr(9) & "db" & Chr(9)
        Do While Not IsEmpty(Cells(Row, Col))
            If Cells(Row, Col) >= 0 Then
                Line = Line & "." & Cells(Row, Col) & ","
            Else
                Line = Line & "-." & -Cells(Row, Col) & ","
            End If
            Col = Col + 1
        Loop
        Line = Left(Line, Len(Line) - 1)
        Out = Out & Line & vbCrLf
        Row = Row + 1
    Loop
    Set MyData = New DataObject
    MyData.SetText Out
    MyData.PutInClipboard
End Sub

This produces,

		db	-.23,.54,.97,.139,.177
		db	.12,.53,.92,.128,.162
		db	.16,.53,.87,.119,.149
		db	.21,.53,.83,.111,.136
		db	.26,.53,.79,.103,.125
		db	.30,.54,.76,.97,.115
		db	.35,.55,.74,.91,.106
		db	.39,.56,.72,.86,.98
		db	.44,.57,.70,.81,.92

 

[jpanhalt]

I am not very competent in Excel yet -- just migrated from QuattroPro. That conversion is easy in QuattroPro using an @dec2bin type of function. In Excel, there is a similar function: Function Library>More Functions>Engineering>Dec2Bin

Hope that helps,

John

 

[atferrari]

Hola Pommie,

You came to the rescue again! Gracias for that. Not source code this time. You helped me in the past with that!!!

Yes, a binary file. Attached an Excel sample. (Please eliminate the appended ".txt").

To John,

That function in excel seems to change how it is displayed for you to read it on the screen but nothing else.

 

[Pommie]

Here you go, at the moment it writes to a file at c:\temp.bin. Just edit the source to the path you require.

Edit, I made it start at row 5 - see the source code.

Edit2, just noticed I left a debug line in there. Just delete the line that starts debug.print.

Mike.

 

[atferrari]

Here you go, at the moment it writes to a file at c:\temp.bin. Just edit the source to the path you require.

Edit, I made it start at row 5 - see the source code.

Edit2, just noticed I left a debug line in there. Just delete the line that starts debug.print.

Mike.

Sorry Pommie, I cannot see any macro. Could I be doing something wrong?

 

[Pommie]

I put the macro in the sheet1 code and not in a module but it should still show up in the macro list.

Try adding a module and copy the following into it,

Sub Convert()
Dim Out As String
Dim FileNum As Integer
    Row = 5         'start row
    Out = ""
    Do While Not IsEmpty(Cells(Row, 2))
        Col = 2     'start column
        Do While Not IsEmpty(Cells(Row, Col))
            If Cells(Row, Col) >= 0 Then
                Out = Out & Chr(Cells(Row, Col))
            Else
                Out = Out & Chr(256 - Cells(Row, Col))
            End If
            Col = Col + 1
        Loop
        Out = Out & Line
        Row = Row + 1
    Loop
    FileNum = FreeFile
    Open "c:\temp.bin" For Binary As #FileNum
        Put #FileNum, , Out
    Close FileNum
End Sub

See if that works.

Mike.

 

[atferrari]

I put the macro in the sheet1 code and not in a module but it should still show up in the macro list.

Try adding a module and copy the following into it,

Sub Convert()
Dim Out As String
Dim FileNum As Integer
    Row = 5         'start row
    Out = ""
    Do While Not IsEmpty(Cells(Row, 2))
        Col = 2     'start column
        Do While Not IsEmpty(Cells(Row, Col))
            If Cells(Row, Col) >= 0 Then
                Out = Out & Chr(Cells(Row, Col))
            Else
                Out = Out & Chr(256 - Cells(Row, Col))
            End If
            Col = Col + 1
        Loop
        Out = Out & Line
        Row = Row + 1
    Loop
    FileNum = FreeFile
    Open "c:\temp.bin" For Binary As #FileNum
        Put #FileNum, , Out
    Close FileNum
End Sub

See if that works.

Mike.

(emphatic) YES!!

Gracias for that, Mike

BTW, could you please explain briefly these two:

Out = Out & Chr(256 - Cells(Row, Col))
and
Out = Out & Line

The ampersand confuses me.

Thanks

 

[Pommie]

The ampersand just joins two strings but the second line (out = out & line) isn't needed and could have caused a bug. Luckily, line was just an empty string and so no harm was done.

The line, Out = Out & Chr(256 - Cells(Row, Col)) is just in case there are any negative numbers in your data, it's so a value of -1 will become 255 etc.

Mike.

 

[atferrari]

The ampersand just joins two strings but the second line (out = out & line) isn't needed and could have caused a bug. Luckily, line was just an empty string and so no harm was done.

The line, Out = Out & Chr(256 - Cells(Row, Col)) is just in case there are any negative numbers in your data, it's so a value of -1 will become 255 etc.

Mike.

For my case where there would be no negative values I eliminated both lines. It works OK.

Mike, you solved a stumbling block for me. As usual, now that I know how, it is pretty "evident".

Now moving faster to the next step of my design.

Thanks again.