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

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 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