Writing to a .TXT file then retrieve contents into userform?

[JumboCactuar]

Hi,
I'm trying to create a userform which takes some input, writes the contents to last line of DATA.TXT file in same folder.
Then retrieve last 10 entries from text file into userform text box.

Unsure how to copy the data to text file at last row

Appreciate any help

 

[ParamRay]

I assume your reading text from TextBox1, writing text into TextBox2, and the process is triggered by a command button click:

Private Sub CommandButton1_Click()
  Const ForAppending = 8
  Const ForReading = 1
  Dim objFileSystem As Object
  Dim objTextStream As Object
  Dim astrAllLines() As String
  Dim lngLineCount As Long
  Dim strFilePath As String
  Dim strNewText As String
  Dim strAllText As String
  Dim j As Long
  
  On Error GoTo ErrorHandler
  Set objFileSystem = CreateObject("Scripting.FileSystemObject")
  strFilePath = ThisWorkbook.Path & "\Data.txt"
  strNewText = Me.TextBox1.Value
  
  Set objTextStream = objFileSystem.OpenTextFile(strFilePath, ForAppending, True)
  objTextStream.WriteLine strNewText
  objTextStream.Close
  
  Set objTextStream = objFileSystem.OpenTextFile(strFilePath, ForReading)
  strAllText = objTextStream.ReadAll()
  objTextStream.Close
  
  astrAllLines = Split(strAllText, vbCrLf)
  Me.TextBox2.Value = vbNullString
  Me.TextBox2.MultiLine = True
  
  For j = UBound(astrAllLines) To LBound(astrAllLines) Step -1
    If astrAllLines(j) <> vbNullString Then
      lngLineCount = lngLineCount + 1
      Me.TextBox2.Value = astrAllLines(j) & vbCrLf & Me.TextBox2.Value
      If lngLineCount >= 10 Then Exit For
    End If
  Next j

ExitHandler:
  On Error Resume Next
  objTextStream.Close
  Set objTextStream = Nothing
  Set objFileSystem = Nothing
  Exit Sub
  
ErrorHandler:
  MsgBox Err.Description, vbExclamation
  Resume ExitHandler
End Sub

 

[JumboCactuar]

I assume your reading text from TextBox1, writing text into TextBox2, and the process is triggered by a command button click:

Private Sub CommandButton1_Click()
  Const ForAppending = 8
  Const ForReading = 1
  Dim objFileSystem As Object
  Dim objTextStream As Object
  Dim astrAllLines() As String
  Dim lngLineCount As Long
  Dim strFilePath As String
  Dim strNewText As String
  Dim strAllText As String
  Dim j As Long
  
  On Error GoTo ErrorHandler
  Set objFileSystem = CreateObject("Scripting.FileSystemObject")
  strFilePath = ThisWorkbook.Path & "\Data.txt"
  strNewText = Me.TextBox1.Value
  
  Set objTextStream = objFileSystem.OpenTextFile(strFilePath, ForAppending, True)
  objTextStream.WriteLine strNewText
  objTextStream.Close
  
  Set objTextStream = objFileSystem.OpenTextFile(strFilePath, ForReading)
  strAllText = objTextStream.ReadAll()
  objTextStream.Close
  
  astrAllLines = Split(strAllText, vbCrLf)
  Me.TextBox2.Value = vbNullString
  Me.TextBox2.MultiLine = True
  
  For j = UBound(astrAllLines) To LBound(astrAllLines) Step -1
    If astrAllLines(j) <> vbNullString Then
      lngLineCount = lngLineCount + 1
      Me.TextBox2.Value = astrAllLines(j) & vbCrLf & Me.TextBox2.Value
      If lngLineCount >= 10 Then Exit For
    End If
  Next j

ExitHandler:
  On Error Resume Next
  objTextStream.Close
  Set objTextStream = Nothing
  Set objFileSystem = Nothing
  Exit Sub
  
ErrorHandler:
  MsgBox Err.Description, vbExclamation
  Resume ExitHandler
End Sub

impressive, thankyou
its so fast

 

[JumboCactuar]

Hi,
Is there any way (outside of the userform) to check if any new messages?

Example, change cell Z1 to "Unread Messages"

I was thinking maybe checking the file size or line count of the txt file.

Though I don't like using ontimer to check things, but could possibly check on worksheet change event.