Reading a Record at an Arbitrary Position in Excel VBA

The following program reads a specific record from a random access file. It is important to ensure that only the number of an existing record can be specified:

Type Person
    FirstName As String * 20
    LastName As String * 20
    City As String * 20
    BirthDate As Date
    Salary As Single
End Type
Sub ReadRandomRecord()
    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
    ' Determine the number of records in the file
    fullFileName = ThisWorkbook.Path & "C:\Users\POPOLY\Desktop\Person.txt"
    totalRecords = FileLen(fullFileName) / Len(Px)
    ' Get the desired record number from the worksheet
    recordNumber = Cells(16, 1).Value
    ' Validate the record number within the valid range
    If recordNumber < 1 Then recordNumber = 1
    If recordNumber > totalRecords Then recordNumber = totalRecords
    ' Open the file for random access reading
    Open fullFileName For Random As #1 Len = Len(Px)
    ' Read the specified record into Px
    Get #1, recordNumber, Px
    ' Close the file
    Close #1
    ' Output the record's fields to the worksheet
    Cells(17, 1).Value = Trim(Px.FirstName)
    Cells(17, 2).Value = Trim(Px.LastName)
    Cells(17, 3).Value = Trim(Px.City)
    Cells(17, 4).Value = CDate(Px.BirthDate)
    Cells(17, 5).Value = Round(CDbl(Px.Salary), 2)
    Exit Sub
ErrorHandler:
    MsgBox Err.Description
End Sub

Explanation:

The function FileLen() returns the size of a file in bytes. Using this, the total number of records stored in the file can be calculated by dividing the file size by the length of one record.

The user inputs the number of the desired record in the Excel worksheet (e.g., cell A16). If the specified record number is too large or too small, it is adjusted to the nearest valid value (either the first or the last record).

The Get statement reads a record from the file, similar in operation to the Put statement:

• The first parameter is the file number.
• The second parameter specifies the position of the record in the file to be read.
• The third parameter is the user-defined type variable into which the data is read.

When outputting the data, small adjustments are made:

• For fixed-length strings, the Trim() function removes trailing spaces added during storage.
• The date is converted to a proper date format using CDate().
• The salary value is converted to a double with CDbl() and rounded to two decimal places.

Note that due to the limited precision of floating-point numbers, slight differences may appear in the stored or retrieved values. For example, a stored value of 4620.85 may sometimes read back as 4620.849999.