VBA/Excel/Access/Word/File Path/Random Access Files

Gets the desired record from the file and writes the fields to the worksheet.

 
Private Type Employee
     empID As String * 8
     empName As String * 20
     empAge As Integer
     empStatus As String * 10
     empSalaryClass As String * 1
End Type
 
Sub GetEmployeeInfo()
    Dim empData As Employee
    Dim filePath As String
    filePath = ActiveWorkbook.Path & "\Employees.txt"
    Open filePath For Random As #1 Len = Len(empData)
        Get #1, recNum, empData
        Cells(2, "A").Value = empData.empID
        Cells(2, "B").Value = empData.empName
        Cells(2, "C").Value = empData.empAge
        Cells(2, "D").Value = empData.empStatus
        Cells(2, "E").Value = empData.empSalaryClass
    Close #1
End Sub

Random Access Files

 
Private Type Employee
     empID As String*8
     empName As String*20
     empAge As Integer
     empStatus As String*10
     empSalaryClass As String*1
End Type
Private recNum As Integer
Sub WriteEmployeeInfo()
    Dim empData As Employee
    Dim filePath As String
    recNum = GetMaxRecNum     "Get the next available record number
    filePath = ActiveWorkbook.Path & "\Employees.txt"
    Open filePath For Random As #1 Len = Len(empData)    "Open file
        empData.empID = Cells(2, "A").Value
        empData.empName = Cells(2, "B").Value
        empData.empAge = Cells(2, "C").Value
        empData.empStatus = Cells(2, "D").Value
        empData.empSalaryClass = Cells(2, "E").Value
        Put #1, recNum, empData   "Write the data
    Close #1
End Sub
"

Use Random Access Files to save a user defined data type

 
Private Type Employee
     empID As String * 8
     empName As String * 20
     empAge As Integer
     empStatus As String * 10
     empSalaryClass As String * 1
End Type
Sub GetMaxRecNum()
    Dim empData As Employee
    Dim empDataLen As Long
    Dim filePath As String
    filePath = ActiveWorkbook.Path & "\Employees.txt"
    empDataLen = FileLen(filePath)
    Debug.Print empDataLen / Len(empData) + 1
End Sub