vba read all text in a text file?

Question

I am trying to use vba to read all text in a text file and display it in an excel message box. the problem I have is whilst this is in effect working, it displays each line of text in a separate message box when instead I want it all in one?

can someone please show me where I am going wrong. thanks

If Range("L" & ActiveCell.Row).Value = "Performance" Then
Dim FilePath As String
Dim strLine As String
FilePath = "\\UKSH000-FILE06\Purchasing\New_Supplier_Set_Ups_&_Audits\ATTACHMENTS\" & Range("C" & ActiveCell.Row).Value & "\performance.txt"
Open FilePath For Input As #1
While EOF(1) = False
    'read the next line of data in the text file
    Line Input #1, strLine
    'print the data in the current row
    MsgBox strLine
    'increment the row counter
    i = i + 1
Wend
Close #1

End If

Answer 1

Within your loop you have to concatenate all the lines a string variable and output the result at the end. It's basically like this:

Dim Total As String
' ...
While EOF(1) = False
    'read the next line of data in the text file
    Line Input #1, strLine
    Total = Total & vbNewLine & strLine
    'increment the row counter
    i = i + 1
Wend

MsgBox Total

Note: While this solution is working, for large files it may not be very efficient due to the fact that what looks like a concatenation in code, in fact means copying the existing content to a new memory location, and then inserting the new line's string. That is done for every line. So for 1000 lines, the incresingly large total string is copied around 999 times.

Answer 2

You need to accumulate the text in a separate string:

1. Write Dim strAll As String before the loop.
2. Replace the MsgBox in the loop with strAll = strAll & strLine.
3. After the loop, use MsgBox strAll

& is used to join strings in VBA. You could separate the individual lines with a space:

strAll = strAll & " " & strLine.

Or even multi-line

strAll = strAll & vbCrLf & strLine.

where vbCrLf is a VBA constant which means "carriage return followed by line feed". You'll introduce an extra space / line feed at the start of the string but I'll leave that for you to fix!