Excel 2007 VBA Macro reading a text file line by line how do I stop delimiting on a comma (,)

Question

I have a simple Excel 2007 Macro that is reading a text file line by line and displaying the output.

This macro is breaking on commas. I want it to simply read the entire line breaking on a carrage return.

What am I doing wrong?

Sub Directory()
  Dim strFileName As String
  Dim strDirectory As String
  Dim intFileKey As Integer
  Dim strLine As String

  strDirectory = "C:\Documents and Settings\e1009028\My Documents\embosstest"

  ChDir (strDirectory)

  strFileName = Dir("*.txt")

  Do While Len(strFileName) > 0
    intFileKey = FreeFile
    Open strFileName For Input As intFileKey
    Do While Not EOF(intFileKey)
        Input #intFileKey, strLine
        MsgBox Mid(strLine, 1, 10)
    Loop
    strFileName = Dir
  Loop

End Sub

Here is a sample text file:

1 blahblahblabh
2 blah,blahblah

Solution

For a quick fix, try using Line input instead of input.

For a more modern solution, have a look at FileSystemObject, especially OpenTextFile.

Comments

[gMale]

Thanks, I wandered in here and found your "OpenTextFile" suggestion. I cut/paste the VBScript portion into an Excel Subroutine and it worked like a charm! Line Input wouldn't read my lines correctly but OpenTextFile does...