KSN
10-04-2009, 10:13 PM
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
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