Read textfile contents into user form

[gibbo1715]

All

I use the code below to read a log file in notepad

Private Sub CommandButton12_Click()
'' Purpose   : View the results of the log file
Dim viRetVal As Double
Dim vtFileLog As String
    vtFileLog = ThisWorkbook.Path & Application.PathSeparator & "details.LOG"
If Dir(vtFileLog) = "" Then
        MsgBox "No file available", vbOKOnly, "LogPrint"                'Can't find the file
    Else
       viRetVal = Shell("NOTEPAD.EXE" & " " & vtFileLog, 3)           
    End If
End Sub

What i would like to be able to do do is read the contents of the text file in a textbox on a userform with in excel, Is this easliy achievable?

Thanks for the help

Gibbo

[MWE]

All

I use the code below to read a log file in notepad

Private Sub CommandButton12_Click()
'' Purpose   : View the results of the log file
Dim viRetVal As Double
Dim vtFileLog As String
    vtFileLog = ThisWorkbook.Path & Application.PathSeparator & "details.LOG"
If Dir(vtFileLog) = "" Then
        MsgBox "No file available", vbOKOnly, "LogPrint"                'Can't find the file
    Else
       viRetVal = Shell("NOTEPAD.EXE" & " " & vtFileLog, 3)           
    End If
End Sub

What i would like to be able to do do is read the contents of the text file in a textbox on a userform with in excel, Is this easliy achievable?

Thanks for the help

Gibbo

Yes, this can be done and it is not very hard. re what aspect do you need help?

1. setting up a userform?
2. reading from a text file?
3. moving the text file info into the userform?

A few other questions?

1. should the userform display the current, say, path and log file?
2. should the userform allow the user to select another logfile for display?
3. should the user be able to display only what is of interest (perhaps based on some criteria?
4. in addition to what the user might want to do (#3), is there any info in the log file that triggers action by the spreadsheet? For example, lines that should not be displayed

[gibbo1715]

Thanks for the reply

Yes, this can be done and it is not very hard. re what aspect do you need help?

1. setting up a userform?
2. reading from a text file?
3. moving the text file info into the userform?

A few other questions?

1. should the userform display the current, say, path and log file?
2. should the userform allow the user to select another logfile for display?
3. should the user be able to display only what is of interest (perhaps based on some criteria?
4. in addition to what the user might want to do (#3), is there any info in the log file that triggers action by the spreadsheet? For example, lines that should not be displayed

What i am trying to do is allow my user to see a list of textfiles in a listbox think im ok with that bit, Im also ok with writing my textfiles, im using the extention CMI. i.e. filename.cmi, but it is just a text file

I want them to be able to select a file to open from that list and then the contents be imported into the textbox on my userform, thats the bit I need help with.

My text file will contain 2 parts seperated by a : would be good to be able to put the first part in textbox 1 and the second part in text box 2 if thats not too much more complicated.

in answer to the above, i dont require anything more complicated than the ability to open the two parts of my text file into my two textboxes, if they require to open another file it will be from the list box and just overwrite the previous content of my two textboxes

Thanks again for the help

Gibbo

[Killian]

Hi Gibbo,

It's certainly achievable, but how you do it depends on how the logfile is structured.
Assuming you just want to dump the whole content into the textbox, the easiest way is probably the ReadAll method for textstream files in the Scripting runtime

'a constant for the logfile path
Const LOG_FILE_PATH As String = "C:\TEMP\LOGFILE.TXT"
'add a reference to the Scipting Runtime in Tools>References
Dim fso As FileSystemObject
Dim ts As TextStream
Set fso = New FileSystemObject
    Set ts = fso.OpenTextFile(LOG_FILE_PATH)
Me.TextBox1.Text = ts.ReadAll
ts.Close
    Set ts= Nothing
    Set fso = Nothing

[gibbo1715]

Thanks for the reply, but if possible having considered it in more detail it would be better if I were able to seperate it into two parts as detailed above

[Killian]

You can extend the use of the filesystem object to easily fill the list box.
Provided your log file doesn't contain any other ":" 's you cen then use the Split functio to get you two strings

'add a reference to the Scipting Runtime in Tools>References
'a constant for the logfiles path
Const LOG_FILE_PATH As String = "C:\TEMP"
Dim fso As New FileSystemObject
Dim ts As TextStream

Private Sub UserForm_Initialize()
Dim f As File
For Each f In fso.GetFolder(LOG_FILE_PATH).Files
        If Right(f.Name, 4) = ".cmi" Then
            ListBox1.AddItem f.Name
        End If
    Next f
End Sub

Private Sub CommandButton1_Click()
Dim strTemp As String
Dim arrLog() As String
If Not ListBox1.Text = "" Then
        Set ts = fso.OpenTextFile(LOG_FILE_PATH & _
            Application.PathSeparator & ListBox1.Text)
strTemp = ts.ReadAll
        ts.Close
arrLog = Split(strTemp, ":")
        TextBox1.Text = arrLog(0)
        TextBox2.Text = arrLog(1)
    End If
End Sub

[Justinlabenne]

How about a list box?

[gibbo1715]

Thankyou both, now know how do what i wanted too, out of interest if i wanted to break it into three parts using the : seperator i assume i just add arrLog(2) and so on?

Cheers

Gibbo

[Killian]

Thankyou both, now know how do what i wanted too, out of interest if i wanted to break it into three parts using the : seperator i assume i just add arrLog(2) and so on?

Cheers

Gibbo

Exactly. Split returns the resulting separate strings in an array.
I should point out... it's not available in XL 97