Excel text file connection TextFileColumnDataTypes

Question

I'm having an issue with the array used in the TextFileColumnDataTypes argument of the connection in Excel.

I've commented in the code below so you can see the line that has the problem. This is not my full code being used but the error is the same and this is it essentially boiled-down so some things might seem a bit crude as I've hardcoded them into this example.

Sub TestWhyStuffBreaks()
Dim xlApp As Excel.Application, xlWb As Excel.Workbook, xlSht As Excel.Worksheet, i As Integer, arrDT() As Integer

Set xlApp = CreateObject("Excel.Application")
Set xlWb = xlApp.Workbooks.Add

ReDim arrDT(16)
For i = 1 To 16
    arrDT(i) = 2
Next i

xlApp.Visible = True

Set xlSht = xlWb.Sheets(1)

With xlSht.QueryTables.Add(Connection:="TEXT;C:\temp\textfile.txt", Destination:=xlSht.Range("$A$1"))
    .Name = xlSht.Name
    .FieldNames = True
    .RowNumbers = False
    .TextFilePlatform = 437
    .TextFileStartRow = 1
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierNone
    .TextFileConsecutiveDelimiter = False
    .TextFileOtherDelimiter = "|"
    .TextFileColumnDataTypes = arrDT 'This line errors with message of "Invalid procedure call or argument".
    '.TextFileColumnDataTypes = Array(2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2) 'This line is fine but not how I want to do it, I want to be able to dynamically change the length of the array.
    .Refresh BackgroundQuery:=False
End With

End Sub

In the full version of the code the size of the array is different as it's looping through more than 1 text file hence wanting to be dynamic about it.

What's the difference between Array(2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2) and arrDT in this case?

Answer 1

So, the answer is to either put Option Base 1 at the top of your module, or loop through from 0 to 15.

Nothing to do with the TextFileColumnDataTypes property, just me being crap at arrays!