PDA

View Full Version : [SOLVED] Read textfile contents into user form



gibbo1715
09-11-2005, 09:44 AM
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
09-11-2005, 10:31 AM
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?


setting up a userform?
reading from a text file?
moving the text file info into the userform?

A few other questions?

should the userform display the current, say, path and log file?
should the userform allow the user to select another logfile for display?
should the user be able to display only what is of interest (perhaps based on some criteria?
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
09-11-2005, 10:40 AM
Thanks for the reply



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

setting up a userform?
reading from a text file?
moving the text file info into the userform?
A few other questions?

should the userform display the current, say, path and log file?
should the userform allow the user to select another logfile for display?
should the user be able to display only what is of interest (perhaps based on some criteria?
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
09-11-2005, 10:43 AM
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
09-11-2005, 10:46 AM
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
09-11-2005, 11:31 AM
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
09-11-2005, 12:56 PM
How about a list box? (http://vbaexpress.com/kb/getarticle.php?kb_id=594)

gibbo1715
09-11-2005, 01:33 PM
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
09-12-2005, 01:54 AM
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