Writing a Record at an Arbitrary Position in Excel VBA

The following program stores a record at any specified position within a random access file. You can either overwrite an existing record or append a new one. It is important to ensure that a valid record number is provided:

Sub WriteRandomRecord()
    Dim fullFileName As String
    Dim recordNumber As Integer
    Dim totalRecords As Integer
    Dim Px As Person
    Dim i As Integer
    ThisWorkbook.Worksheets("Sheet1").Activate
    On Error GoTo ErrorHandler
    ' Store data from the worksheet into a variable of user-defined type "Person"
    Px.FirstName = Cells(17, 1).Value
    Px.LastName = Cells(17, 2).Value
    Px.City = Cells(17, 3).Value
    Px.BirthDate = Cells(17, 4).Value
    Px.Salary = Cells(17, 5).Value
    ' Determine the current number of records in the file
    fullFileName = ThisWorkbook.Path & "C:\Users\POPOLY\Desktop\Person.txt"
    totalRecords = FileLen(fullFileName) / Len(Px)
    ' Validate the record number entered by the user
    recordNumber = Cells(16, 1).Value
    If recordNumber < 1 Then recordNumber = 1
    If recordNumber > totalRecords + 1 Then recordNumber = totalRecords + 1
    ' Open the file for random access writing
    Open fullFileName For Random As #1 Len = Len(Px)
    ' Write the record at the specified position
    Put #1, recordNumber, Px
    ' Close the file
    Close #1
    Exit Sub
ErrorHandler:
    MsgBox Err.Description
End Sub

Explanation:

The contents of a record from the Excel worksheet are stored in a variable of the user-defined data type.

The size of the file and the number of existing records are calculated. The maximum valid record number for writing is the current number of records plus one. If the record number equals this maximum, the record will be appended to the end of the file.

The Put statement writes the record into the file at the specified position.