Solved: Text To Columns - passing FieldInfo value as a string

[KSN]

Hi

I have multiple text files which need to be parsed (Text To Columns). However only some of the columns should be treated as "Text" and the remainder as "General".

In my macro, I have created a string to hold the set of the Array values which are required for the "FieldInfo" portion of the TextToColumns method, but nothing happens (no parsing takes place) when the macro is run.

Have tried many things but none of them seem to work:banghead:

Here is the code:


Sub test()
Dim i as long
Dim FieldInfoVal As String
Dim DTA_DataStartCol as Long
DTA_DataStartCol = 17 ' This will be a dynamic value to be picked up run time - 17 is just an example for testing this piece of code
FieldInfoVal = ""
For i = 1 To DTA_DataStartCol
If i <> DTA_DataStartCol Then
FieldInfoVal = FieldInfoVal & "Array(" & i & ", 2),"
Else: FieldInfoVal = FieldInfoVal & "Array(" & i & ", 2)"
End If
Next i
' The commented out code below is what is sought to be achieved i.e. 1st 17 columns are treated as Text
' Selection.TextToColumns DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="|", FieldInfo:=Array(Array(1, 2), Array(2, 2), Array(3, 2), Array(4, 2), Array(5, _
2), Array(6, 2), Array(7, 2), Array(8, 2), Array(9, 2), Array(10, 2), Array(11, 2), Array(12 _
, 2), Array(13, 2), Array(14, 2), Array(15, 2), Array(16, 2), Array(17, 2)), _
TrailingMinusNumbers:=True

' The below code is the attempt to provide the Array as a string (FieldInfoVal)
Selection.TextToColumns DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="|", FieldInfo:=Array(FieldInfoVal), _
TrailingMinusNumbers:=True


End Sub

Have tried looking up the forum for a possible resolution, but been unable to do so. Hope someone can help? Thanks very much!!

KSN

[KSN]

Hi
The problem is resolved based on the post by xld in another related thread "Using FieldInfo with variable array content". I made the following changes:

Dim FieldInfoVal As String
change to
Dim FieldInfoVal As Variant

Add the following line:
Redim FieldInfoVal(1 to DTA_DataStartCol)


Change the lines below:

For i = 1 To DTA_DataStartCol
If i <> DTA_DataStartCol Then
FieldInfoVal = FieldInfoVal & "Array(" & i & ", 2),"
Else: FieldInfoVal = FieldInfoVal & "Array(" & i & ", 2)"
End If
Next i

To
For i = 1 To DTA_DataStartCol
FieldInfoVal(i) = Array(i, 2)
Next i

And finally, change the TextToColumns method line to:

Selection.TextToColumns DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="|", FieldInfo:=FieldInfoVal, _
TrailingMinusNumbers:=True


This works just fine - thanks so much xld for the help in the other post "Using FieldInfo with variable array content"

Can this thread be closed with status "Solved".

Again many thanks to xld.

Regards
KSN:thumb :cloud9:

[Bob Phillips]

Yes, to close it as solved select the option from Thread Tools in the top bar.