CODEWITHSUNDEEP
excelvba
Sabatino Ognibene
Sabatino Ognibene

Reputation: 171

How to format as Text using Text-To-Columns

This code works using text to columns to separate column A into as many columns as I need, but I cannot figure out a way to make it do this with all columns outputted into text format.

Sub TxT_to_Columns()
    ActiveWorkbook.ActiveSheet.Range("A:A").TextToColumns _
    Destination:=Range("A1"), _
    DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, _
    ConsecutiveDelimiter:=False, _
    Tab:=False, _
    Semicolon:=False, _
    Comma:=False, _
    Space:=False, _
    Other:=True, _
    OtherChar:="|"
End Sub

How can I do the same thing but output all columns to text format?

Thank you,

Upvotes: 0

Views: 2852

Answers (2)

FaneDuru
FaneDuru

Reputation: 42236

Firstly, all the credit must go to Scott Craner!

I will only show a shorter way of arrays building, as response at what @Sabatino Ognibene asked lately. I am sure that Scott knew that, but he wanted to present the array building in a way able to easily be understood.

Dim arr() As Variant, i As Long, nrCol As Long
    nrCol = 12 'the number of columns you need to result
    ReDim arr(1 To nrCol) As Variant
    For i = 1 To nrCol
      arr(i) = Array(i, 2) '2 will be used for `Text` formatting.
    Next

Upvotes: 1

Scott Craner
Scott Craner

Reputation: 152495

You use FieldInfo:

FieldInfo:=Array(Array(1, 2), Array(2, 2), Array(3, 2)),

FieldInfo uses an array of arrays: Array(Array(Column,Type),...)

You can get the types from here:

https://learn.microsoft.com/en-us/office/vba/api/excel.xlcolumndatatype

If you know you want to do all the columns then you can pre fill an variant arr variable:

Sub TxT_to_Columns()
    Dim arr(1 To 10) As Variant

    arr(1) = Array(1, 2)
    arr(2) = Array(2, 2)
    arr(3) = Array(3, 2)
    arr(4) = Array(4, 2)
    arr(5) = Array(5, 2)
    arr(6) = Array(6, 2)
    arr(7) = Array(7, 2)
    arr(8) = Array(8, 2)
    arr(9) = Array(9, 2)
    arr(10) = Array(10, 2)


    ActiveWorkbook.ActiveSheet.Range("A:A").TextToColumns _
        Destination:=ActiveWorkbook.ActiveSheet.Range("A1"), _
        DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=False, _
        Tab:=True, _
        Semicolon:=False, _
        Comma:=False, _
        Space:=False, _
        Other:=True, _
        OtherChar:="|", _
        FieldInfo:=arr
End Sub

Upvotes: 2

Related Questions