Making an array a variable

[rhino4eva]

Sub import()
    
    'Application.Wait (Now + #12:00:05 AM#)


        

        Dim fName, DrivePath
    
'Uses open file dialog box

        Sheets("Sheet1").Select
        DrivePath = "T:"
        With Application.FileDialog(msoFileDialogFilePicker)
        .InitialFileName = DrivePath & "*TCTB*.*"
        .AllowMultiSelect = False
        .Show
        If .SelectedItems.Count > 0 Then fName = .SelectedItems(1)
        Sheets("Sheet1").Range("m1") = fName
        
        On Error Resume Next
        End With
        MsgBox "You have selected " & fName & " to import"
        With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & fName, Destination:=Range("a7"))
        .FieldNames = True
        .RowNumbers = False
        .Name = fName
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlOverwriteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = False
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 850
        .TextFileStartRow = 10
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = True
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 4, 1, 1, 4, 9, 1, 1, 1, 1, 1, 9, 9, 9, 1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
end sub

I have mashed together my limited knowledge of code with a recorded macro to come up with the VBA above
It basically imports a text file into a new sheet . I designed it for one person to use but inevitably every wants a go. The only problem is that everyone text file is just that tiny bit different and I need to make the ".TextFileColumnDataTypes = Array" variable. I have got a frontend userform so I can control the variable but as part of the test I tried a hardwired version first ie

code:
ImpArray = "1, 1, 1, 4, 1, 1, 4, 9, 1, 1, 1, 1, 1, 9, 9, 9, 1, 1, 1, 1, 1, 1, 1, 1"
.TextFileColumnDataTypes = Array(ImpArray)

the results weren't as expected ... what am I doing wrong ????

 

[RoryA]

When you do that you get an array that just contains that literal string. To separate each element into an array, you can use Split like this:

ImpArray = "1,1,1,4,1,1,4,9,1,1,1,1,1,9,9,9,1,1,1,1,1,1,1,1"
.TextFileColumnDataTypes = Split(ImpArray, ",")

 

[rhino4eva]

Sub import()
        Dim WS As Worksheet
        Dim fName As String
        Set WS = ThisWorkbook.Sheets("Sheet1")
        With Application.FileDialog(msoFileDialogFilePicker)
        .InitialFileName = "T:" & "*" & WS.Range("F2") & "*txt"
        .AllowMultiSelect = False
        .Show
        If .SelectedItems.Count > 0 Then fName = .SelectedItems(1)
        End With
        MsgBox "You have selected " & fName & " to import"
        With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & fName, Destination:=Range("A5"))
        'If WS.Range("F2") = "JICU" Then imparray = "1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1"
        If WS.Range("F2") = "TCTB" Then imparray = "1,1,1,4,1,1,4,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1"
        'If WS.Range("F2") = "LICU" Then imparray = "1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1"
        'If WS.Range("F2") = "LNNU" Then imparray = "1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1"
        'If WS.Range("F2") = "DGTBQ" Then imparray = "1, 1, 1, 4, 1, 1, 4, 4, 4, 4, 1, 1, 1, 1, 1, 1"
        'If WS.Range("F2") = "DGBPCR" Then imparray = "1, 1, 1, 4, 1, 1, 1, 4, 4, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1"
        'If WS.Range("F2") = "MGIT" Then imparray = "1, 4, 4, 1, 1, 1, 4, 1, 1, 1, 1, 1, 1"
        'If WS.Range("F2") = "LTBI" Then imparray = "1, 1, 1, 1, 1, 4, 1, 4, 1, 1, 1, 1, 1, 1, 1, 1"
        'If WS.Range("F2") = "LTBIN" Then imparray = "1, 1, 1, 1, 1, 4, 1, 4, 1, 1, 1, 1, 1, 1, 1, 1"
        'If WS.Range("F2") = "INFFLU" Then imparray = "4, 1, 4, 1, 1, 1, 1, 1, 1, 1"
        'If WS.Range("F2") = "J82" Then imparray = "1, 1, 1, 4, 4, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1"
        'If WS.Range("F2") = "JUROLO" Then imparray = "1, 1, 1, 4, 4, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1"
        'If WS.Range("F2") = "LIMBO" Then imparray = "1, 4, 4, 1, 1, 1, 1, 1, 1, 1, 1"
        'If WS.Range("F2") = "SFSD" Then imparray = "1, 4, 4, 1, 1, 4, 1, 1, 1, 1, 1"
        'If WS.Range("F2") = "LPONC" Then imparray = "1, 1, 1, 4, 1, 4, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1"
              
        .Name = fName
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlOverwriteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = False
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 850
        .TextFileStartRow = 8
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = True
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Split(imparray, ",")
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With

I tried Split(imparray, ",") but it seems to just ignore the array and default to import all 1's ....I do have some 4 that's are important DOB

I know the vba is very dirty but I don't know what I can clean ...please ca anyone help

 

[RoryA]

Perhaps you need actual numeric values. Since there's no real benefit to using strings here, you may as well use an actual array:

imparray = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)

 

[rhino4eva]

Perhaps you need actual numeric values. Since there's no real benefit to using strings here, you may as well use an actual array:

imparray = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)

yep yu r correct ... It's all about the syntax

Thank you