VBA: Binary Files

  • Hi Guys,


    I was wondering if there is a way to open a Binary file, read all the data until a particular record is found and then overwrite that record with new information.


    I have tried to do this with the put method and used and iindex but all the records in the entire file are erased except the one I just wrote.


    Does anyone have any experiece in using Binary files in VBA as this is the only way that I can acomplish the project I have running at the moment.


    Any assistance would be much appreciated


    DW

  • Just checked the Walkenbach Power Prog book - he mentions binary files and says they are rarely used in VBA so he didn't cover them.


    no help sorry

    There are three types of people in this world.
    Those who can count and those who can't.

  • Hi DW,


    Here is an example of creating, reading and editing a binary file.


    Place all the code in a module.


    Cheers
    Andy

    [h4]Cheers
    Andy
    [/h4]

  • Thanks Andy for the code....


    but I still cannot get it to work, when I read the file again it becomes jumbled and incohesive.


    I noticed two things with the code.


    What is the lngIndex variable for? I noticed it was declared but not used....


    Furthernore with my code I copied your code and adapted it for my use but my binary file contains 110 records but the length function returns a value of 82 (ie len(myrecord) = 82).


    Also with your code when you comment that it replaces record 3 is this becasue the 3 is hardcoded and to apply this to look for another record index I would change the 3 to an index no.


    am slowly getting closer to the solution but it I just need a few more verifications


    Thanks for your help again


    DW

  • Hi DW,


    The lngIndex variable in the routine EditBinaryFile is just a left over from copy+paste of the other routine.


    Yes the 3 is a direct reference to record number 3.


    I'm assuming that the example code worked before you modified it. It did for me.
    If you post your code, especially the record structure, and some example data.
    We might see the problem that is causing " jumbled and incohesive" data.


    cheers
    Andy

    [h4]Cheers
    Andy
    [/h4]

  • Hi andy,


    The code I used is based on the following passage found at http://www.erlandsendata.no/english/index.php:- (a great website)


    ...and I added your EditBinary File eg and adapted it for myRecord


    I hope this helps???


    DW


    Option Explicit


    Type MyBinRecordInfo
    LastName As String
    FirstName As String
    BirthDate As Date
    End Type


    Sub WriteBinaryFile()
    Dim MyRecord As MyBinRecordInfo, FileNum As Integer, i As Integer
    If Dir("C:\FOLDERNAME\BINFILE.DAT") <> "" Then
    ' deletes the file if it exists
    Kill "C:\FOLDERNAME\BINFILE.DAT"
    End If
    FileNum = FreeFile ' next free filenumber
    Open "C:\FOLDERNAME\BINFILE.DAT" For Binary As #FileNum
    ' creates the new file
    ' write records to the binary file
    For i = 1 To 100
    With MyRecord
    .LastName = "LastName" & i
    .FirstName = "FirstName" & i
    .BirthDate = Date
    End With
    WriteBinaryRecord MyRecord, FileNum ' save the record
    Next i
    Close #FileNum ' close the file
    End Sub



    Sub WriteBinaryRecord(tRecord As MyBinRecordInfo, fn As Integer)
    ' writes the content of tRecord to the next record in an open binary file
    Dim sSize As Integer
    With tRecord
    sSize = Len(.LastName) ' get the length of the LastName variable
    Put fn, , sSize ' write the length information
    Put fn, , .LastName ' write the variable
    sSize = Len(.FirstName) ' get the length of the FirstName variable
    Put fn, , sSize ' write the length information
    Put fn, , .FirstName ' write the variable
    Put fn, , .BirthDate ' write the variable (fixed length)
    End With
    End Sub


    Sub ReadBinaryFile()
    Dim MyRecord As MyBinRecordInfo, FileNum As Integer, i As Integer
    FileNum = FreeFile ' next free filenumber
    Open "C:\FOLDERNAME\BINFILE.DAT" For Binary As #FileNum
    ' open the binary file
    ' read records from the binary file
    For i = 1 To 100
    While Loc(FileNum) < LOF(FileNum)
    ReadBinaryRecord MyRecord, FileNum
    ' do something with the input
    With MyRecord
    Debug.Print .LastName, .FirstName, .BirthDate
    End With
    Wend
    Next i
    Close FileNum ' close the file
    End Sub



    Sub ReadBinaryRecord(tRecord As MyBinRecordInfo, fn As Integer)
    ' reads the next record from an open binary file
    Dim sSize As Integer
    With tRecord
    Get fn, , sSize ' size of the LastName field
    .LastName = String(sSize, " ") ' set the variable length
    Get fn, , .LastName ' read the variable string field
    Get fn, , sSize ' size of the FirstName field
    .FirstName = String(sSize, " ") ' set the variable length
    Get fn, , .FirstName ' read the variable string field
    Get fn, , .BirthDate ' read the BirtDate field (fixed length)
    End With
    End Sub


    Sub EditBinaryFile()
    Dim intUnit As Integer
    Dim typInfo As MyBinRecordInfo
    Dim lngIndex As Long, lngPos As Long

    intUnit = FreeFile
    Open "C:\FOLDERNAME\BINFILE.DAT" For Binary As #intUnit 'Access 'Write As intUnit Len = Len(typInfo)
    typInfo.FirstName = "John"
    typInfo.LastName = "Smith"
    typInfo.BirthDate = #1/1/2001#
    ' Replace record 3 - Charlie
    lngPos = 1 + ((3 - 1) * Len(typInfo))
    Put #intUnit, lngPos, typInfo
    Close intUnit
    ' show changes
    ReadBinaryFile
    End Sub

  • Hi DW,


    Ok I see the problem. My version deals with fixed length records, 12 bytes.
    Where as your example handles variable length records. It does this by writing the length of the record just before the record itself. (an offset I believe)


    This will work fine for reading from and adding to BUT could have trouble handling updates. Especially when the length of the name changes.
    The only way to deal with changes in this case is to re write the file completely. Replacing the old information with new.
    You could do this every time a change is made, depending on the size of the file.
    Otherwise you will need to manage the changes in memory and write the new file on completion.


    Cheers
    Andy

    [h4]Cheers
    Andy
    [/h4]