• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 10241
  • Last Modified:

How does the FieldInfo argument work for TextToColumns in VBA?

I have 1 column in my Excel file that I'm converting into over 60 columns using the TextToColumns function in VBA. The macro recorder is putting an huge amount of code in the FieldInfo argument. How do those arrays work? Do they each reference a different column? Why is a column being referred to in the form of an array? Is there any simple way to shorten the code? And what if I have 70 columns the next time I run this code, will the macro error out?

    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), Array(29, 1), Array(30, 1), Array(31, 1), _
        Array(32, 1), Array(33, 1), Array(34, 1), Array(35, 1), Array(36, 1), Array(37, 1), Array( _
        38, 1), Array(39, 1), Array(40, 1), Array(41, 1), Array(42, 1), Array(43, 1), Array(44, 1), _
        Array(45, 1), Array(46, 1), Array(47, 1), Array(48, 1), Array(49, 1), Array(50, 1), Array( _
        51, 1), Array(52, 1), Array(53, 1), Array(54, 1), Array(55, 1), Array(56, 1), Array(57, 1), _
        Array(58, 1), Array(59, 1), Array(60, 1), Array(61, 1), Array(62, 1), Array(63, 1), Array( _
        64, 1), Array(65, 1)), TrailingMinusNumbers:=True

Open in new window

0
shampouya
Asked:
shampouya
  • 7
1 Solution
 
Jeff DarlingDeveloper AnalystCommented:
The fieldinfo parameter is optional.  

https://msdn.microsoft.com/en-us/library/office/ff193593.aspx

However, it is used for the purpose of enumerating the data types for each column.

https://msdn.microsoft.com/en-us/library/office/ff193030.aspx

All your data types in the example provided are of type xlGeneralFormat.

One of the pitfalls of using xlGeneralFormat is that you may experience unwanted conversions.  Conversions that remove leading zeros for example in Zip Codes.

Dim myArrB() As Variant
Dim iItem As Long
Dim myArrayTemp() As Variant
Dim myFields As Long

myFields = 2

myFields = myFields - 1 ' Zero Based

' Create a Jagged Array that is required by TextToColumns
' -------------------------------------------------------
ReDim myArrB(0 To myFields)

For iItem = 0 To myFields
    ReDim myArrayTemp(0 To 1)
    myArrayTemp(0) = iItem + 1
    myArrayTemp(1) = 1 ' xlGeneralFormat DataType
    myArrB(iItem) = myArrayTemp
Next iItem
' -------------------------------------------------------


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:=myArrB, TrailingMinusNumbers:=True

Open in new window

0
 
Jeff DarlingDeveloper AnalystCommented:
Other unwanted conversions can happen depending on how Microsoft guesses the data type should be converted.  When in doubt, I use xlTextFormat instead of xlGeneralFormat.

Attached is a sample that uses xlTextFormat, but notice what happens when the number of fields is more than the number of fields in the fieldinfo array.  It defaults back to xlGeneralFormat if not specified.
EE-20150218001.xlsm
0
 
shampouyaAuthor Commented:
Thanks Jeff. Could you repost that code with a comment at the end of the first 18 rows? I'm trying to follow your logic in those rows but it's going over my head since I'm a newbie.
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
Jeff DarlingDeveloper AnalystCommented:
code with comments added

Dim myArrB() As Variant              ' Array to be used for FieldInfo
Dim iItem As Long                    ' Counter for Fields in FieldInfo
Dim myArrayTemp() As Variant         ' Temporary Array used for Arrays inside of FieldInfo Array
Dim myFields As Long                 ' Variable to hold number of fields we want to setup

myFields = 3                         ' Number of fields we want.

myFields = myFields - 1              ' Zero Based - This is for the array index.  subtract 1

' Create a Jagged Array that is required by TextToColumns
' -------------------------------------------------------
ReDim myArrB(0 To myFields)          ' Redimension the Array to the number of fields

For iItem = 0 To myFields            ' Loop through each field
    ReDim myArrayTemp(0 To 1)        ' Redimension the Array inside the FieldInfo Array
    myArrayTemp(0) = iItem + 1       ' iItem is the field number
    myArrayTemp(1) = 2               ' xlTextFormat DataType
    myArrB(iItem) = myArrayTemp      ' Build the FileInfo Array by storing the Array inside the Array
Next iItem
' -------------------------------------------------------


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:=myArrB, TrailingMinusNumbers:=True

Open in new window

1
 
Jeff DarlingDeveloper AnalystCommented:
notice that I replaced myArrB as the array that FieldInfo uses.

myArrB is a Jagged Array.  Meaning, that it is an array that contains arrays inside of it.  Notice on line 18 that I put the myArrayTemp inside of the myArrB.  This is because myArrayTemp contains 2 elements.  one element is for the field number and the other is for the data type.
0
 
Jeff DarlingDeveloper AnalystCommented:
This is what the arrays look like when populated.

arrays populated
0
 
Jeff DarlingDeveloper AnalystCommented:
As you can see, this is 18 lines of code just to build the array.

If you only had 3 fields, you would be better off doing it like this:

Array(Array(1, 2), Array(2, 2), Array(3, 2))

Open in new window


However, If you had 70 fields, it is probably easier to use the code and put 70 on line 6.
0
 
Jeff DarlingDeveloper AnalystCommented:
Here is what the Array looks like if using the Array function to build the Jagged array.

screenshot of code and watch variable window
See how they both have Arrays inside of Arrays?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now