Error 14. Out of string space.

I found some code written by Chip Pearson that allows importing a large text file into Excel. I tried it on a file that was nearly 400mb and got an Error14 when I tried to convert vbLf to vbCr in one of his functions (which tries to load the entire text file into one string). Does anyone know a work around?

Thanks.

.... or read the file one line at a time (probably an order of magnitude slower, but without the runtime error) using code like one of the following from the attached workbook:

Sub ImportTextFileAsTextReadUsingInputPound()
  'This imports a Text File into Sheet 'Scratch00' opening the file as Text and
  'reading the contents using 'Input #'


  Dim iFileNo As Integer
  
  Dim iOutputRow As Long
  
  Dim bNeedMore As Boolean
  
  Dim sText As String
  Dim sFileName As String
  Dim sFolder As String
  Dim sPathAndFileName As String
  
  
  '''''''''''''''''''''''''''''''''''''''''''''
  'Preparation for Import
  '''''''''''''''''''''''''''''''''''''''''''''
 
  'Get the Folder and File Name Combination from the Worksheet
  sFolder = Trim(Workbooks(ThisWorkbook.Name).Sheets(sMainSheetNAME).Range(sMainSheetDataFolderCELL).Text)
  sFileName = Trim(Workbooks(ThisWorkbook.Name).Sheets(sMainSheetNAME).Range(sMainSheetDataFileCELL).Text)
  
  'Make sure the Folder has a trailing BACKSLASH
  If Right(sFolder, 1) <> "\" Then
    sFolder = sFolder & "\"
  End If

  'Build the Path and File Name Combination
  sPathAndFileName = sFolder & sFileName
  
  'Verify that the file to be imported exists
  If LJMFileExists(sPathAndFileName) = False Then
    MsgBox "NOTHING DONE.  File to be IMPORTED can not be found." & vbCrLf & _
           "Folder: '" & sFolder & "'" & vbCrLf & _
           "File: '" & sFileName & "'" & vbCrLf & _
           ""
    Exit Sub
  End If

  'Create the 'Destination Sheet' if it doesn't Exist
  Call LjmAddSheetByName(sScratchSheetNAME)
  
  'Clear the contents of the Destination Sheet
  Call ClearContentsOfSheetScratch00
  
  
  '''''''''''''''''''''''''''''''''''''''''''''
  'Import
  '''''''''''''''''''''''''''''''''''''''''''''
    
  'Allocate a file 'handle'
  iFileNo = FreeFile
  
  'Open the file
  Open sPathAndFileName For Input As #iFileNo
  
  bNeedMore = True
  While bNeedMore = True
  
    'Read the entire contents of the file
    Input #iFileNo, sText
  
    'Output the text to the next row in the Output Sheet
    iOutputRow = iOutputRow + 1
    Sheets(sScratchSheetNAME).Cells(iOutputRow, 1) = sText
    
    'Stop if at End of File
    If EOF(iFileNo) Then
      bNeedMore = False
    End If
  
  Wend
  
  'Close the file
CLOSEFILE:
  Close #iFileNo
  
  
  '''''''''''''''''''''''''''''''''''''''''''''
  'Termination
  '''''''''''''''''''''''''''''''''''''''''''''
  
  'Set the focus on the 'Destination Sheet'
  Call GoToSheetScratch00
  
  MsgBox "File IMPORT completed using Input Open and Read using 'Input #'." & vbCrLf & _
         "Destination Sheet: '" & sScratchSheetNAME & "'" & vbCrLf & vbCrLf & _
         "Folder: '" & sFolder & "'" & vbCrLf & _
         "File: '" & sFileName & "'" & vbCrLf & _
         ""
              
End Sub


Sub ImportTextFileUsingFileSystemObject()
  'This imports a Text File into Sheet 'Scratch00' using FileSystemObject

  Const nOpenFileForREADING = 1

  Dim fso As Object
  Dim f As Object
  
  Dim iOutputRow As Long
  
  Dim bNeedMore As Boolean
  
  Dim sArray() As String
  Dim sRange As String
  Dim sText As String
  Dim sFileName As String
  Dim sFileReadMode As String
  Dim sFolder As String
  Dim sPathAndFileName As String
  
  
  '''''''''''''''''''''''''''''''''''''''''''''
  'Preparation for Import
  '''''''''''''''''''''''''''''''''''''''''''''
 
  'Get the Folder and File Name Combination from the Worksheet
  sFolder = Trim(Workbooks(ThisWorkbook.Name).Sheets(sMainSheetNAME).Range(sMainSheetDataFolderCELL).Text)
  sFileName = Trim(Workbooks(ThisWorkbook.Name).Sheets(sMainSheetNAME).Range(sMainSheetDataFileCELL).Text)
  
  'Make sure the Folder has a trailing BACKSLASH
  If Right(sFolder, 1) <> "\" Then
    sFolder = sFolder & "\"
  End If

  'Build the Path and File Name Combination
  sPathAndFileName = sFolder & sFileName
  
  'Verify that the file to be imported exists
  If LJMFileExists(sPathAndFileName) = False Then
    MsgBox "NOTHING DONE.  File to be IMPORTED can not be found." & vbCrLf & _
           "Folder: '" & sFolder & "'" & vbCrLf & _
           "File: '" & sFileName & "'" & vbCrLf & _
           ""
    Exit Sub
  End If

  'Create the 'Destination Sheet' if it doesn't Exist
  Call LjmAddSheetByName(sScratchSheetNAME)
  
  'Clear the contents of the Destination Sheet
  Call ClearContentsOfSheetScratch00
  
  
  '''''''''''''''''''''''''''''''''''''''''''''
  'Import
  '''''''''''''''''''''''''''''''''''''''''''''
    
  'Create the 'File Sysem Object' pointer
  Set fso = CreateObject("Scripting.FileSystemObject")
    
  'Open the file for reading
  Set f = fso.OpenTextFile(sPathAndFileName, nOpenFileForREADING)
    
    
'Set the value of 'READ_ONE_LINE_AT_A_TIME' to 'True'  to read one line at a time using TBSL
'Set the value of 'READ_ONE_LINE_AT_A_TIME' to 'False' to read all lines at once 'using .ReadAll'
#Const READ_ONE_LINE_AT_A_TIME = True
#If READ_ONE_LINE_AT_A_TIME = True Then

  sFileReadMode = "READ ONE LINE AT A TIME"

  'Read one line at a time
  bNeedMore = True
  While bNeedMore = True
  
    'Read the entire contents of the file
    sText = f.ReadLine
  
    'Output the text to the next row in the Output Sheet
    iOutputRow = iOutputRow + 1
    Sheets(sScratchSheetNAME).Cells(iOutputRow, 1) = sText
    
    'Stop if at End of File
    If f.AtEndOfStream Then
      bNeedMore = False
    End If
  
  Wend

#Else

  sFileReadMode = "READ ALL LINES AT ONCE"
  
  'Read all lines at one time
  sText = f.readall
  
  'Put the string into an array of strings (parsing on CRLF)
  On Error GoTo 0
  sArray() = Split(sText, vbCrLf)
  
  'Output the array to the Worksheet (assumes 'Option Base 0')
  sRange = "A1:A" & (UBound(sArray) + 1 * 0)
  
  On Error Resume Next
  'NOTE: If 'Transpose' does not work because file is too large, the data can be output one row at a time
  Sheets(sScratchSheetNAME).Range(sRange) = WorksheetFunction.Transpose(sArray)
    
  If err.Number <> 0 Then
    MsgBox "NOTHING DONE.  Transpose FAILURE because file was TOO LARGE." & vbCrLf & _
           "Destination Sheet: '" & sScratchSheetNAME & "'" & vbCrLf & vbCrLf & _
           "Folder: '" & sFolder & "'" & vbCrLf & _
           "File: '" & sFileName & "'" & vbCrLf & _
           ""
    On Error GoTo 0
    Exit Sub
  End If

#End If
    
  'Close the file
  f.Close
  
  '''''''''''''''''''''''''''''''''''''''''''''
  'Termination
  '''''''''''''''''''''''''''''''''''''''''''''
  
  'Set the focus on the 'Destination Sheet'
  Call GoToSheetScratch00
  
  MsgBox "File IMPORT completed using FileSystemObject." & vbCrLf & _
         "FileSystemObject Read Mode:: '" & sFileReadMode & "'" & vbCrLf & vbCrLf & _
         "Destination Sheet: '" & sScratchSheetNAME & "'" & vbCrLf & vbCrLf & _
         "Folder: '" & sFolder & "'" & vbCrLf & _
         "File: '" & sFileName & "'" & vbCrLf & _
         ""
              
End Sub

Lewis

Thank you very much.