VBA for Text to Columns with fixed width using list of widths

[CodeNewb]

I often need to paste fixed width data into excel then use text to columns. It's sometimes difficult to see where my separations should be, but I do have a reliable list of the column widths (for instance, for one file it might be 1,5,13,2,2,10, for another file it might be 8,100,23,55,2,2, etc.). In VBA I tried making a string value that was the collection of all the "Array" values in the Fixed Width Text to Columns function, then using that string in the TextToColumns command, like so:

Selection.TextToColumns Destination:=Range("B1"), DataType:=xlFixedWidth, _
        FieldInfo: = & myString

Before I started trying to loop through the values in the field lengths to construct the string, I tried just using the string from a recorded macro, like so:

myString = "Array(Array(0, 1), Array(28, 1), Array(56, 1), Array(84, 1), Array(112, 1), _
        Array(140, 1), Array(168, 1), Array(196, 1), Array(224, 1), Array(252, 1), Array(280, 1), _
        Array(308, 1), Array(336, 1), Array(364, 1),"

However, it does nothing when I use it in the TextToColumns.

So my next idea was to just loop through my list of field lengths, then use mid or something to take the 1st field length of the entire string, put that in B1, take the next field length of the remaining string, put that in cell C1, etc. (so let's say i put my field length list in sheet 2 cell A1 (5,1,2,10), data is in sheet 1 cell A1, it would go something like this:

String = Sheet1!range("A1")
Lengths = Split(Sheet2!Range("A1").value),",")
For n = 0 to UBound(Lengths)
     Sheet1.range("A1").offset(0,n+1).value = mid(String,1,Lengths(n))

...etc.

But not sure of best way to set my starting position in Mid function for each new field (the start has to be the end of the last field position in the original string plus 1).

But I thought I'd check here first to see if someone knew why my string value of all the different arrays wasn't working in the TextToColumns, or see if someone had a better idea on how to do a Text To Columns in VBA using a comma-delimited list of field lengths.

Thanks for any input

 

[Alex Blakenburg]

Give this a try:

Sub ImportFixedLengthFile()

    Dim arrFldAll As Variant
    Dim arrFldEachCol As Variant
    Dim arrPosition As Variant
    Dim i As Long
 
    ' Replaces your MyString
    arrPosition = Array(0, 7, 16, 20, 31)                   '<--- Change this with your column positions eg 0,28,56,84 etc
 
    ReDim arrFldEachCol(0 To UBound(arrPosition), 0 To 1)
    ReDim arrFldAll(0 To UBound(arrPosition))
 
    For i = 0 To UBound(arrPosition)
        arrFldEachCol(i, 0) = arrPosition(i)
        arrFldEachCol(i, 1) = 1
    Next i
 
    arrFldAll = arrFldEachCol
 
    Workbooks.OpenText Filename:= _
        "C:\FixedLength.txt", _
        StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=arrFldAll, _
        TrailingMinusNumbers:=True

End Sub