Writing cell contents to binary file

[bluto32]

(I have Excel 2010 running on Windows 7 Ultimate)

I am trying to write an Excel macro which will (1) pull in all satellite transponder info from a webpage; (2) sort it according to which beams are desired; (3) break down the appropriate data into bytes, one per cell; (4)write the appropriate data to a file.

So far, with the help of the built-in macro recorder and searching this forum, I have managed to sort out (1), (2) and (3). Part (4) ought to be the easy part, but I'm struggling...

Suppose I have already have a file called "test.ndf" with initial file size 1000 bytes. After doing stages (1)-(3) above, my spreadsheet has the relevant data in cells A1 to A2000, with each cell containing a decimal between 0 and 255 inclusive. I would like to write a macro which will loop through these 2000 cells, appending each value as a byte to my orginal file, so that it has file size 3000 bytes at the end.

Thanks for taking the time to read this,
Bluto

 

[John_w]

Try this (change the file path as required):

    Dim row As Long
    
    Open "C:\path\to\test.ndf" For Binary As #1
    
    'Set pointer to end of file
    Seek #1, LOF(1) + 1
    
    For row = 1 To 2000
        Put #1, , CByte(Cells(row, "A").Value)
    Next
    Close #1

 

[bluto32]

Thank you so much, John - it works a treat!

I have never read/written files in VBA before, and this has really helped me get started.

Couple of quick questions:

1) What is the reason for the # before the file number in four places (everywhere except in the LOF(1) bit)? I tried the code without any #, and it still seems to work ok. For some reason, it doesn't like LOF(#1).

2) Omitting the .Value in the put command still seems to work. Is this a fluke in this case, or should the .Value always be used at the end of Cells(row,"A")?

Many thanks again,
Bluto

 

[xenou]

# is optional so it doesn't matter. It means "number". (hehe). Either way, the number is used by the OS to identify the file while you are using it. But the LOF function takes an integer as an argument so you can only pass a real number to the LOF function.

Value as a property of the cell is slightly different - in this case, it happens to be the default property so if we omit it it is the one that is used anyway. I think default properties are a VBA thing - not sure if they're so common else where.

I would actually suspect that CByte() is also optional, since 1 cast as a byte is still 1, and so on ...

 

[shg]

I would actually suspect that CByte() is also optional, since 1 cast as a byte is still 1, and so on ... <!-- / message --><!-- sig -->

I would have expected (haven't tried), that the code would write a Double lacking the CByte.

 

[bluto32]

Thank you, xenou and shg.

I did also try omitting CByte earlier, and got some strange results: each decimal from 0 to 255 was converted into 10 bytes rather than just 1. Is 10 bytes the right length for the "double" (precision floating point?) you refer to?.

(Also, when omitting the CByte, I had to reinsert the .Value for the code to actually run.)

Bluto

 

[shg]

An ordinary Double is eight bytes. A Variant Double is two bytes that identify the Variant subtype & other stuff, plus the eight bytes.

CDbl(cell.Value) should write just the eight-byte Double.

See Help for the Put statement.

 

[bluto32]

Ok - thanks for the info.
Bluto

 

[bluto32]

I just stumbled across this old thread (10 years!) and realised that my code was extremely slow when writing large amounts of data. It would write the bytes one at a time, resulting in considerable hard disk thrashing. The improved code below first creates a byte array to hold the data, and then writes the whole array to the file in one go.

Sub WriteBytes()
    Dim n As Long 'Row counter
    Dim ByteArray() As Byte
    Dim InputRange As Range
    Dim cell As Range
    
    'Copy worksheet data into byte array
    Set InputRange = ThisWorkbook.Sheets("Sheet1").Range("A1:A4")
    ReDim ByteArray(1 To InputRange.Count)
    n = 1
    For Each cell In InputRange
        ByteArray(n) = cell.Value
        n = n + 1
    Next cell
    
    'Append contents of byte array to file
    Open "C:\users\bluto\desktop\file" For Binary As #1
    Put #1, LOF(1) + 1, ByteArray
    Close #1
End Sub

While the above appears to work quickly and as intended, I was wondering if the first section could be improved.
Is there a more elegant way of populating the byte array, which avoids looping through the cells?
I have tried using variants or arrays of variants as shown below, but have problems later when it comes to writing to the file.

Sub WriteBytes2()
    Dim ByteArray() As Variant
    Dim InputRange As Range
    
    'Copy worksheet data into byte array
    Set InputRange = ThisWorkbook.Sheets("Sheet1").Range("A1:A4")
    ByteArray = InputRange.Value
    
    'Append contents of byte array to file
    Open "C:\users\bluto\desktop\file" For Binary As #1
    Put #1, LOF(1) + 1, ByteArray
    Close #1
End Sub

The code above appends 40 bytes rather than 4. I think it's because the 4 values read from the worksheet are assumed to be of type "double" rather than "byte".
Using Dim ByteArray As Variant (i.e. a variant rather than an array of variants) appends 60 bytes...
And simply trying Dim ByteArray() As Byte doesn't run at all: I get a "Type mismatch" error at the Set InputRange... line.
Is there a sneaky way?

 

[John_w]

Happy 10th Birthday to your thread

While the above appears to work quickly and as intended, I was wondering if the first section could be improved.
Is there a more elegant way of populating the byte array, which avoids looping through the cells?

The fastest way is to load the Variant array with the range values, which creates a 2-dimensional array. Then write the 2-dimensional array to a 1-dimensional byte array which can be written to the file in one go.

Sub WriteBytes3()

    Dim ByteArray2D() As Variant
    Dim ByteArray1D() As Byte
    Dim InputRange As Range
    Dim fileName As String
    Dim i As Long
   
    fileName = ThisWorkbook.Path & "\binary_bytes.tmp"
   
    'Copy worksheet data into Variant array - creates a 2-d array
    Set InputRange = ThisWorkbook.Sheets("Sheet1").Range("A1:A4")
    ByteArray2D = InputRange.Value
   
    'Write 2-d Variant array to 1-d Byte array
    ReDim ByteArray1D(1 To UBound(ByteArray2D))
    For i = 1 To UBound(ByteArray2D)
        ByteArray1D(i) = CByte(ByteArray2D(i, 1))
    Next
       
    'Append contents of 1-d Byte array to file - file size is 4 bytes
    Open fileName For Binary As #1
    Put #1, LOF(1) + 1, ByteArray1D
    Close #1
   
End Sub