Excel VBA Import Text Array Definition problem

Question

I have a workbook with 30 tabs of data that all follow the same process:

Go to a tab in the template workbook
Use the Data Import routine to spit out a CSV's data, dumping values on line 7 to start.
Delete line 7 when finished (it's useless headers we don't need)

The problem arises from the Import Text File routine, that needs an array for every single sheet. I end up with 40 lines of code for every sheet and no way to variablize the routine. Here's the first section of a 30-part sub (all have similar structure):

    'Use the Get Data routine to dump the csv onto the sheet as text/dates where appropriate, then delete line 7
Sheets("Sheet Alpha info").Select                 'explicitly declare which sheet to dump onto
Application.CutCopyMode = False                 'this is good programming

    'this code section is the Get Data routine run in the UI, turned into VBA
With ActiveSheet.QueryTables.Add(Connection:= _
    "TEXT;" & ThisWorkbook.Path & "\sheet_alpha.CSV", _
    Destination:=Range("$A$7"))                 'important on every tab!
    '.CommandType = 0                           'this is only needed when you use the UI to do the routine, so currently commented out.
    .Name = "sheet_alpha"                       'could variablize this routine, but signficance of .Name is unknown in Import routine.
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .TextFilePromptOnRefresh = False
    .TextFilePlatform = 437                     'no idea what this actually is. encoding for UTF-8?
    .TextFileStartRow = 1
    .TextFileParseType = xlDelimited            'not set width
    .TextFileTextQualifier = xlTextQualifierDoubleQuote              'yes, well-behaved CSV.
    .TextFileConsecutiveDelimiter = False
    .TextFileTabDelimiter = False
    .TextFileSemicolonDelimiter = False
    .TextFileCommaDelimiter = True              'yes, well-behaved CSV.
    .TextFileSpaceDelimiter = False
    .TextFileColumnDataTypes = Array(2, 2, 2, 2, 2, 2, 2, 2, 2) 'this damn array is why we repeat the code. Need a new array for each sheet.
    .TextFileTrailingMinusNumbers = True
    .Refresh BackgroundQuery:=False
End With

    'and now remove the useless header line
Rows("7:7").Select
Selection.Delete Shift:=xlUp

So question is: How can I variablize this routine and make it a single FOR loop that also defines each TextFileColumnDataType array as a text-only array (so, Array() filled with 2s each time)?

Extension: If I wanted the array to read other data types (so an array might be Array(1, 2, 2, 3, 2, 2, 2)), how do I do that?

1. this could be simplified by looping through the worksheets. 2) This method would be further enhanced by using arrays instead of manipulating the object that much - it runs super slow doesnt it? If you know what the end result ought to be you can easily get at that by more efficient means — Doug Coats, Sep 18 '18 at 18:28
@DougCoats It would be more efficient to use an array, which is why Tim gave me this answer: https://stackoverflow.com/a/52284928/5725420. However, his method doesn't open the CSVs in a way that preserves the base text. Turn out that even this 40-line code runs faster than Tim's b/c Tim's opens a book - this does not. — C.A.R., Sep 18 '18 at 19:18
There are really only 3 variables here: the source file, the destination range, and the array of field types. You could wrap this code in a sub with those 3 parameters and it should work fine. The only challenge is determining the exact field types for each file (assuming that's important here) — Tim Williams, Sep 18 '18 at 22:39

Tim Williams · Accepted Answer · 2018-09-19T19:12:54.050

There are really only 3 variables here: the source file, the destination range, and the array of field types.

You could wrap this code in a sub with those 3 parameters and it should work fine. The only challenge is determining the exact field types for each file (assuming that's important here)

Sub Tester()
    'eg - call directly
    ImportFromText ThisWorkbook.Sheets("test").Range("A7"), _
                    ThisWorkbook.Path & "\test.csv", _
                    Array(2, 2, 2, 2, 2, 2, 2, 2, 2)

    '...or from a worksheet table
    Dim rw As Range
    For Each rw in ThisWorkbook.Sheets("Files").Range("A2:C32").Rows 

        ImportFromText ThisWorkbook.Sheets(rw.Cells(1).Value).Range("A7"), _
                    ThisWorkbook.Path & "\" & rw.Cells(2).Value, _
                    Split(rw.Cells(3).Value, "|")

    Next rw


End Sub


Sub ImportFromText(DestRange As Range, filePath As String, arrFieldTypes)

    Dim sht As Worksheet, qt As QueryTable

    Set sht = DestRange.Worksheet

    'clear any previous....
    Do While sht.QueryTables.Count > 0
        sht.QueryTables(1).Delete
    Loop
    sht.UsedRange.Clear

    Set qt = sht.QueryTables.Add(Connection:="TEXT;" & filePath, Destination:=DestRange)

    With qt
        '.CommandType = 0
        .Name = "sheet_alpha"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = arrFieldTypes
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With

    DestRange.EntireRow.Delete Shift:=xlUp 'and now remove the useless header line

End Sub

Where do I store the array/collection of the 3 variables? I see the `Sub Tester()` section at the top, but how do I read a new CSV, a new range, and a new array into the `Tester` sub 29 more times? — C.A.R., Sep 19 '18 at 16:39
You could store each file as a line in a worksheet table: store the array as (e.g.) "2|2|2|2|2|2|2" and then use `Split(arrayStringHere, "|")` to convert it to an array when calling `ImportFromText` See my edit above — Tim Williams, Sep 19 '18 at 19:09
Huzzah! That second one makes so much more sense. I will try this! — C.A.R., Sep 19 '18 at 19:59

Excel VBA Import Text Array Definition problem

1 Answers1