none
Excel VBA: How do I automate a variable number of columns in a CSV as TEXT formatting? RRS feed

  • Question

  • How do I identify and automate a variable number of columns in a CSV as TEXT formatting?

    I can hard code the QueryTable.TextFileColumnDataTypes for the number of columns in a CSV if they remain the same. However, if the number of columns vary then text formatting will only be applied to the specified number. So if I have the array set for 3 and there are 5 columns, only the first 3 are formatted as text on import. Since the CSV is being imported at the same time this count is needed, I'm uncertain how I could identify the number of columns to set this property correctly each time.

    Sub ImportCSVasText()
    ' Select and Open CSV
        Dim path As String
        With Application.FileDialog(msoFileDialogFilePicker)
            .AllowMultiSelect = False
            .Filters.Add "CSV Text Only", "*.csv"
            .Show
            path = .SelectedItems.Item(1)
        End With
        
    
        With ActiveSheet.QueryTables.Add(Connection:= _
            "TEXT;" & path, Destination:=Range("$A$1"))
            .Name = "CSV Data"
            .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 = Array(xlTextFormat, xlTextFormat, xlTextFormat) ' xlTextFormat = 2
            .TextFileTrailingMinusNumbers = True
            .Refresh BackgroundQuery:=False
        End With
    End Sub


    SV

    Friday, December 14, 2018 12:55 PM

Answers

  • You can open the file and count the columns then close the file prior to create the connection:

                       
    Sub ImportCSVasText2()
    ' Select and Open CSV
        Dim path As String
        Dim varArr As Variant
        Dim wb As Workbook
        Dim i As Integer

        With Application.FileDialog(msoFileDialogFilePicker)
            .AllowMultiSelect = False
            .Filters.Add "CSV Text Only", "*.csv"
            .Show
            path = .SelectedItems.Item(1)
        End With

        'open the file to count columns
        'and create the variant array for column types
        
        Set wb = Workbooks.Open(path)
        ReDim varArr(1 To wb.Worksheets(1).Columns.Count)
        varArr(1) = 2
        For i = 2 To UBound(varArr)
            varArr(i) = 2
        Next i
        
        'Close the CSV file
        wb.Close False

        'Create the data connection using the new array
        With ActiveSheet.QueryTables.Add(Connection:= _
            "TEXT;" & path, Destination:=Range("$A$1"))
            .Name = "CSV Data"
            .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 = varArr ' xlTextFormat = 2
            .TextFileTrailingMinusNumbers = True
            .Refresh BackgroundQuery:=False
        End With
    End Sub


    Friday, December 14, 2018 9:13 PM