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

JumboCactuar

Well-known Member
Joined
Nov 16, 2016
Messages
785
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
I assume your reading text from TextBox1, writing text into TextBox2, and the process is triggered by a command button click:

Code:
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
 
Upvote 0
I assume your reading text from TextBox1, writing text into TextBox2, and the process is triggered by a command button click:

Code:
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,342
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top