0

I'm adapting a function that open a tabulatted .txt file and parse into a 2d Array. It's been working fine with most my tabulatted .txt but this particular file I'm getting Error runtime 9 , is that because of the time of my file or a particular format on it's carriage return or tab etc? How can I debug this? Any help is appreciated

Example of file that works

Example of File that error runtime 9

My code:

'Option Explicit
Sub test()
    'On Error Resume Next 'just in case... comment this in dev. mode to see debug message

    Dim myArr() As Variant
    Dim m As Integer
    Dim Path As String, Delim As String
    Dim ArchiFile As String

    Delim = vbTab                                'if Tabullated .txt 'vbTab   Chr( 9 )    Tab character
    'Delim = "," 'if Coma Separated Value .csv

    ArchiFile = "C:\Users\diego\Desktop\RoofDataBase.txt"
    'Error runtime 9
    'Download file here https://www.dropbox.com/s/zg8otjfhtb5vxb2/RoofDataBase.txt?dl=0

    Path = "C:\Users\diego\Desktop\A340.txt"
    'Works!
    'Download file here https://www.dropbox.com/s/6vosudkytx6vjjl/A340.txt?dl=0

    '****** WHERE THE MAGIC HAPPEN *****
    'myArr = TwoDArr(ArchiFile, Delim) 'not working with roof export? it does open and loop trough in function but not save in array
    myArr = TwoDArr(Path, Delim)                 'works for schema exports (perhaps suze?
    '********* END OF MAGIC ************

    Debug.Print "sub - "; myArr(2, 2)            'Remember arrays start at 0, so (1,1) means "B2", (1,0) means "A2")

End Sub

Function TwoDArr(file As String, Delim As String) As Variant
    'This function open up a .csv or tabulatted .txt and parse it's info to an array
    'It loop row by row (RowData) and in each Row, loops Column by Column (ColData) saving it's values
    'in TempTwoDArr() which is then parsed to function request (TwoDArr)
    'Adapted from https://stackoverflow.com/questions/12259595/load-csv-file-into-a-vba-array-rather-than-excel-sheet

    Dim MyData As String, RowData() As String, ColData() As String
    Dim TempTwoDArr() As Variant
    Dim i As Long, n As Long

    Open file For Binary As #1
    MyData = Space$(LOF(1))
    Get #1, , MyData
    Close #1
    RowData() = Split(MyData, vbLf) 'for some reason RoodDatabase.txt only contain Linefeed character (No Carriage Return) and this will work on splitting other files too.
    'RowData() = Split(MyData, vbCrLf)            'vbCrLf  Chr( 13 ) + Chr( 10 )   Carriage return-linefeed combination
    'source https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/miscellaneous-constants

    Rs = UBound(RowData)
    ReDim Preserve TempTwoDArr(Rs, 0)

    For i = LBound(RowData) To UBound(RowData)
        If Len(Trim(RowData(i))) <> 0 Then
            ColData = Split(RowData(i), Delim)
            'n = n + 1
            ReDim Preserve TempTwoDArr(Rs, UBound(ColData))
            For n = LBound(ColData) To UBound(ColData)
                TempTwoDArr(i, n) = ColData(n)
                Debug.Print ColData(n)
            Next n
        End If
    Next i

    TwoDArr = TempTwoDArr()

    Debug.Print TempTwoDArr(2, 2)

    Erase TempTwoDArr                            'clear up memory
End Function

[Edit 1] Amended Variable file should read Path on line 17

[Solution] Amended RowData() = Split(MyData, vbLf) instead of RowData() = Split(MyData, vbCrLf) 'for some reason RoodDatabase.txt only contain Linefeed character (No Carriage Return) and since this file is generated in a 3rd party software I have no control over it. vBLf will work on splitting other files rows too.

Community
  • 1
  • 1
Diego
  • 105
  • 2
  • 4
  • 18
  • what line gives you an error? – Marcucciboy2 Jun 26 '18 at 13:16
  • @Marcucciboy2 line 48 `ReDim Preserve TempTwoDArr(Rs, 0)` – Diego Jun 26 '18 at 13:20
  • 1
    An error makes no sense if you're saying that one file works and the other does not, because the latter implies it's the contents of the data itself, and it's not iterated through that data yet at that point. Even if one file had extra lines at the end, it should not fail, if at all, until it gets inside the loop and redims again. – Bill Hileman Jun 26 '18 at 15:43
  • 1
    What's the value of `Rs` when the error raised? – David Zemens Jun 26 '18 at 18:52
  • step through your code to debug: http://www.cpearson.com/excel/debuggingvba.aspx – David Zemens Jun 26 '18 at 18:52
  • `Path` is not assigned a value. – David Zemens Jun 26 '18 at 18:57
  • If I fix that problem, the only error I get is at the `Debug.Print TempTwoDArr(2,2)` line because `2` exceeds the upper bound of the array's first dimension which is `0`. https://i.imgur.com/G7XgLkb.png – David Zemens Jun 26 '18 at 18:58
  • Thanks everyone, @DavidZemens by `debug.print Rs` I realized that RoofDatabase.txt didn't have Carriage Returns at each row, hence not splitting in an array, hence out of range (error 9) as I was trying to read info on line 3... The solution was posted above. – Diego Jun 26 '18 at 22:45

0 Answers0