Convert Comma Separated String to Array for Text Conversion

I am creating an application [In Access] to convert text files to excel files because my company does a lot of them. So I created a table that I keep the File Name, Num of Cols, and a 3rd field with the common separated list of the datatypes for the columns.

Everything is working except I cannot get the comma separated list to work as an array. First, I call the ImportText File:

Call ImportTextFile("TestFileName", 7, ConvertStringToArray(",,,,,,2"))

Then I ConvertSTringToArray:

Function ConvertStringToArray(ByVal StringToConvert As String) As Variant

      Dim rawArray() As String
      Dim varArray() As Variant

      rawArray = Split(StringToConvert, ",")
      ReDim varArray(LBound(rawArray) To UBound(rawArray))

      Dim i As Long: For i = LBound(rawArray) To UBound(rawArray)
           varArray(i) = rawArray(i)
      Next i
      ConvertStringToArray = varArray

End Function

Then it passes to ImportTextFile (Up until here aDataTypes is passed as an Array.):

Public Sub ImportTextFile(ByVal strFileName As String, ByVal iNumOfCols As Integer, Optional aDataTypes As Variant = Nothing)

    On Error GoTo Sub_Err
    Dim xl As New Excel.Application: Set xl = New Excel.Application
    xl.DisplayAlerts = False
    Dim sPathAndFile As String: sPathAndFile = cPath & strFileName
    Dim wb As Workbook: Set wb = xl.Workbooks.Add
    Dim ws As Worksheet: Set ws = wb.Sheets(1)
    With ws.QueryTables.Add(Connection:="TEXT;" & sPathAndFile & ".txt", Destination:=ws.Range("$A$1"))
        .FieldNames = True
        .RowNumbers = False
        .RefreshStyle = xlInsertDeleteCells
        .SaveData = True
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        If IsArray(aDataTypes) Then
            .TextFileColumnDataTypes = aDataTypes
        End If
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With

End Sub

However, it crashes on this line:

.TextFileColumnDataTypes = aDataTypes

What am I missing? Why isn't this working?

The Error message that I receive is:

> Invalid Procedure Call or Argument

TextFileColumnDataTypes expects an array of XlColumnDataType values, but you're passing in an array of strings.

Maybe consider reworking your array function:

Function FormatsArray(ByVal StringToConvert As String) As Variant
    Dim i As Long
    Dim rawArray() As String
    Dim varArray As Variant, v

    rawArray = Split(StringToConvert, ",")
    ReDim varArray(LBound(rawArray) To UBound(rawArray))

    For i = LBound(rawArray) To UBound(rawArray)
        v = Trim(rawArray(i))
        If Len(v) > 0 Then     'specific format supplied?
            varArray(i) = CLng(v)
        Else
            varArray(i) = xlGeneralFormat 'use default
        End If
    Next i
    FormatsArray = varArray
End Function