Convert Comma Separated String to Array for Text Conversion

Question

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

"it crashes" - exactly what happens when it fails? do you get an error? If Yes what's the error message? If you set xl.Visible = True you may be able to see what's going on. — Tim Williams
– Tim Williams, Commented Feb 6, 2023 at 21:15
Will you share the contents of aDataTypes or should we guess? — VBasic2008
– VBasic2008, Commented Feb 6, 2023 at 21:17
Have you tried Array(1, 1, 1, 1, 1, 1, 2) instead of ConvertStringToArray(",,,,,,2")? — VBasic2008
– VBasic2008, Commented Feb 6, 2023 at 21:35
In addition to Tim's answer, I would use just ...Optional aDataTypes) (The default data type is Variant, the default value of a variant is Empty, not Nothing) when the If statement would become If Not IsMissing(aDataTypes) Then. — VBasic2008
– VBasic2008, Commented Feb 6, 2023 at 21:50

Tim Williams · Accepted Answer · 2023-02-06 21:41:55Z

1

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

answered Feb 6, 2023 at 21:41

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Convert Comma Separated String to Array for Text Conversion

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related