CODEWITHSUNDEEP
excelvba
Adam_G
Adam_G

Reputation: 7879

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

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

Upvotes: 0

Views: 320

Answers (1)

chris neilsen
chris neilsen

Reputation: 53126

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

Upvotes: 1

Related Questions