Creating a File with All Records in Excel VBA

The following program creates a file with random access. Three records are stored in this file. This file will serve as the basis for later reading or writing records at arbitrary positions:

Type Person
    FirstName As String * 20
    LastName As String * 20
    City As String * 20
    BirthDate As Date
    Salary As Single
End Type
Sub WriteAllRandomAccess()
    Dim P(1 To 3) As Person
    Dim i As Integer
    ThisWorkbook.Worksheets("Sheet2").Activate
    On Error GoTo ErrorHandler
    ' Store data in an array of the user-defined type "Person"
    For i = 1 To 3
        P(i).FirstName = Cells(i, 1).Value
        P(i).LastName = Cells(i, 2).Value
        P(i).City = Cells(i, 3).Value
        P(i).BirthDate = Cells(i, 4).Value
        P(i).Salary = Cells(i, 5).Value
    Next i
    ' Open the file for random access writing
    Open ThisWorkbook.Path & "C:\Users\POPOLY\Desktop\Person.txt" For Random As #1 Len = Len(P(1))
    ' Write all records into the file
    For i = 1 To 3
        Put #1, i, P(i)
    Next
    ' Close the file
    Close #1
    Exit Sub
ErrorHandler:
    MsgBox Err.Description
End Sub

Explanation:

The contents of the Excel table are stored in an array of variables of the user-defined data type.

The file randomAccess.txt is opened using the Random mode, which enables random access—both for writing and reading.

When opening a file in Random mode, you must specify the length of a single record after the Len keyword. This length can be determined using the Len() function. Besides string length, this function can also return the memory size of a variable.

The Put statement writes a record into the file:

• The first parameter is the file number.
• The second parameter specifies the record position within the file to write to.
• The third parameter is the user-defined type variable that is written.

You can open the created file in a text editor. You will notice that the fixed-length strings contain trailing spaces used for padding. All records are stored directly one after another continuously on a single line. The components of other data types (e.g., Date, Single) are not stored in a human-readable format (see Figure 9.7). This is normal and sufficient for reading and writing using this program.