How do I vary .TextFileColumnDataTypes array?

[osianllwyd]

Hi,

I have found similar posts on this but not one with a solution.

I'm trying to import a .csv file via a QueryTable but I want to vary the .TextFileColumnDataTypes so that a different array of column types are entered.

With ActiveSheet.QueryTables.Add(Connection:="TEXT;\\*****\" & tablename & ".csv" _
        , Destination:=Range("$A$1"))
        .Name = "temp"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 850
        .TextFileStartRow = 4
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1,2,1,1,1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With

I have tried to use the Split function to create an array called ColValTypes in this manner:

ColumnDataTypes = Split(ColumnDataTypesVals, ",")

but when I then enter

.TextFileColumnDataTypes=Array(ColumnDataTypes)

or

.TextFileColumnDataTypes=ColumnDataTypes

or

.TextFileColumnDataTypes=ColumnDataTypes()

I get an error. PLease can anyone help?

 

[Norie]

What is in ColumnDataTypesVals?

 

[osianllwyd]

ColumnDataTypesVals = "1,2,1,1,1"