Subscript Out of Range Error

[goss]

Hi all,

Using Excel 2010.

I'm trying to

• 
  Loop through all lines in a text file
  Burst each line into substrings
  Load substring into array
  Transfer array to range

I'm receiving a subscript out of range error here, not sure why?

            sarrValue(k, j) = Trim(sSubValue(i))

Full code below
thx
w

Option Base 1
Option Explicit


Sub ReadTextFile()
    '
    'Purpose
    '---------------------------------
    ' Read text file
    '
    'References
    '----------------------------------
    ' Uses Early Binding - Microsoft Scripting Runtime
    ' Tools >> References >> Microsoft Scripting Runtime
    '
    'Date       Developer       Action          Comment
    '------------------------------------------------------------------------------
    '12/08/12   ws              Created

    'Declare variables
     Dim wb As Workbook
     Dim rng As Range
     Dim fso As FileSystemObject
     Dim fsoFile As File
     Dim fsoStream As TextStream
     Dim sLine As String
     Dim sPath As String
     Dim sFile As String
     Dim sExtension As String
     Dim sDelimiter As String
     Dim sSubValue() As String
     Dim sarrValue() As String
     Dim i, j As Integer
     Dim k As Long
     Dim lRows As Long
     Dim lColumns As Long
    
    'Initialize variables
     sPath = "C:\Tmp\"
     sFile = "tstArray"
     sExtension = ".txt"
     sDelimiter = "|"
     i = 1
     j = 1
     k = 1
    
    'Object reference
     Set wb = ThisWorkbook
     
     With wb
        Set rng = .Worksheets(1).Range("A1")
     End With
     
     Set fso = New FileSystemObject
     Set fsoFile = fso.GetFile(sPath & sFile & sExtension)
     Set fsoStream = fsoFile.OpenAsTextStream(ForReading, TristateUseDefault)
   
   ' Loop through each line of the text file
     Do While Not fsoStream.AtEndOfStream
        
        'Read line from text file and assign to a string variable
         sLine = fsoStream.ReadLine
         
        'Split the string baed on a delimiter
         sSubValue() = Split(sLine, vbTab)
         Debug.Print UBound(sSubValue)
         
        'Load to array
         For i = 1 To UBound(sSubValue)
            sarrValue(k, j) = Trim(sSubValue(i))
            j = j + 1
         Next i
     
        'Counters
         k = k + 1 'Update row counter
         j = 1 'Reset column counter
     Loop

    'Close the streamreader
     fsoStream.Close
     
    'Get array dimensions
     lRows = UBound(sarrValue, 1)
     lColumns = UBound(sarrValue, 2)

     

    
    
    'Tidy up
     'Close the text file
      fsoStream.Close
      
    'Erase array

     
     'Destroy objects


End Sub

 

[Andrew Poulsom]

You don't appear to have dimensioned the array sarrValue.

 

[goss]

Thanks Andrew,

Here is what I came up with

 'Redim array
     'Rows
      sLine = fsoStream.ReadLine 'Header row for column dimension
      fsoStream.ReadAll
      lFileRecords = fsoStream.Line
      
    'Columns
     sSubHeader() = Split(sLine, vbTab)
     lFileColumns = UBound(sSubHeader)
     
    'TextStream
     fsoStream.Close
     Set fsoStream = Nothing
     sLine = Empty
     
     ReDim sarrValue(lFileRecords, lFileColumns)
   
   ' Loop through each line of the text file
     Set fsoStream = fsoFile.OpenAsTextStream(ForReading, TristateUseDefault)
     Do While Not fsoStream.AtEndOfStream

I could not find a method of the FileSystemObject to move to the beginning of the file, so I closed TextStream and reopened it.

Thanks
w

Full:

Option Base 1
Option Explicit


Sub ReadTextFile()
    '
    'Purpose
    '---------------------------------
    ' Read text file
    '
    'References
    '----------------------------------
    ' Uses Early Binding - Microsoft Scripting Runtime
    ' Tools >> References >> Microsoft Scripting Runtime
    '
    'Date       Developer       Action          Comment
    '------------------------------------------------------------------------------
    '12/08/12   ws              Created

    'Declare variables
     Dim wb As Workbook
     Dim rng As Range
     Dim fso As FileSystemObject
     Dim fsoFile As File
     Dim fsoStream As TextStream
     Dim sLine As String
     Dim sPath As String
     Dim sFile As String
     Dim sExtension As String
     Dim sDelimiter As String
     Dim sSubValue() As String
     Dim sSubHeader() As String
     Dim sarrValue() As String
     Dim i, j As Integer
     Dim k As Long
     Dim lRows As Long
     Dim lColumns As Long
     Dim lFileRecords As Long, lFileColumns As Long
    
    'Initialize variables
     sPath = "C:\Tmp\"
     sFile = "tstArray"
     sExtension = ".txt"
     sDelimiter = "|"
     i = 1
     j = 1
     k = 1
    
    'Object reference
     Set wb = ThisWorkbook
     
     With wb
        Set rng = .Worksheets(1).Range("A1")
     End With
     
     Set fso = New FileSystemObject
     Set fsoFile = fso.GetFile(sPath & sFile & sExtension)
     Set fsoStream = fsoFile.OpenAsTextStream(ForReading, TristateUseDefault)
     
    'Redim array
     'Rows
      sLine = fsoStream.ReadLine 'Header row for column dimension
      fsoStream.ReadAll
      lFileRecords = fsoStream.Line
      
    'Columns
     sSubHeader() = Split(sLine, vbTab)
     lFileColumns = UBound(sSubHeader)
     
    'TextStream
     fsoStream.Close
     Set fsoStream = Nothing
     sLine = Empty
     
     ReDim sarrValue(lFileRecords, lFileColumns)
   
   ' Loop through each line of the text file
     Set fsoStream = fsoFile.OpenAsTextStream(ForReading, TristateUseDefault)
     Do While Not fsoStream.AtEndOfStream
        
        'Read line from text file and assign to a string variable
         sLine = fsoStream.ReadLine
         
        'Split the string baed on a delimiter
         sSubValue() = Split(sLine, vbTab)
       
        'Load the record to the array
         For i = 1 To UBound(sSubValue)
            sarrValue(k, j) = Trim(sSubValue(i))
            j = j + 1
         Next i
     
        'Counters
         k = k + 1 'Update row counter
         j = 1 'Reset column counter
     Loop

    'Close the streamreader
     fsoStream.Close
     
    'Get array dimensions
     lRows = UBound(sarrValue, 1)
     lColumns = UBound(sarrValue, 2)
     Debug.Print lRows; lColumns
       
    'Tidy up
     'Close the text file
      fsoStream.Close
      
    'Erase array

     
     'Destroy objects


End Sub