VBA code to convert all data to columns with arbitrary number of delimiters

Question

I would like to have a macro for converting a pipe-delimited csv/text file to to columns. When I recorded the macro, it tailored it to the number of columns in this dataset. How do I alter this code so that it accommodates any number of columns?

Sub Pipe2Col()
'
' Pipe2Col Macro
'
' Keyboard Shortcut: Ctrl+t
'
    ActiveSheet.PasteSpecial Format:="Unicode Text", Link:=False, _
        DisplayAsIcon:=False
    Columns("A:A").Select
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :="|", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, _
        1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12 _
        , 1), Array(13, 1), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1), _
        Array(19, 1), Array(20, 1), Array(21, 1), Array(22, 1), Array(23, 1), Array(24, 1), Array( _
        25, 1), Array(26, 1), Array(27, 1), Array(28, 1)), TrailingMinusNumbers:=True
End Sub

Do you want it dynamic (detects the number of columns) or is it a static number of columns? — Mech, Feb 21 '20 at 02:01
I tried and observed that you can ignore the "FieldInfo" resulting into multiple arrays for each column and split you data into any number of columns with text to column.. So, you can use the same code. — Naresh, Feb 21 '20 at 03:21
Thanks! This seems to be the most elegant solution. Could you write it up as an answer and I'll accept it? — Adam_G, Feb 21 '20 at 03:48

score 1 · Answer 1 · answered Feb 21 '20 at 02:35

Simplist way would be to prebuild a FieldInfo array that you know is larger than the max number of columns in your source data

Here I've assumed that is 100 columns. Also cleaned up the recorder nonsense

Sub Demo()
    Dim ws As Worksheet
    Dim rng As Range
    Dim FieldInfo() As Variant
    Dim ColInfo() As Variant
    Dim i As Long

    Set ws = ActiveSheet

    Set rng = ws.Range(ws.Cells(1, 1), ws.Cells(ws.Rows.Count, 1).End(xlUp))

    ReDim FieldInfo(0 To 99)
    ReDim ColInfo(0 To 1)
    ColInfo(1) = 1
    For i = 1 To 100
        ColInfo(0) = i
        FieldInfo(i - 1) = ColInfo
    Next

    rng.TextToColumns _
      Destination:=rng.Cells(1, 1), _
      DataType:=xlDelimited, _
      TextQualifier:=xlDoubleQuote, _
      ConsecutiveDelimiter:=False, _
      Tab:=False, _
      Semicolon:=False, _
      Comma:=False, _
      Space:=False, _
      Other:=True, _
      OtherChar:="|", _
      FieldInfo:=FieldInfo, _
      TrailingMinusNumbers:=True
End Sub

Interesting. What is the "recorder nonsense" you're referring to? — Adam_G, Feb 21 '20 at 03:00
@Adam_G `Columns("A:A").Select`, `Selection.TextToColumns` and `Destination:=Range("A1")` (BTW, that isn't a dig at you, it's just that recorder code _always_ needs cleaning to be useful) — chris neilsen, Feb 21 '20 at 03:02

VBA code to convert all data to columns with arbitrary number of delimiters

1 Answers1