Solved

How does the FieldInfo argument work for TextToColumns in VBA?

Posted on 2015-02-18
8
5,086 Views
Last Modified: 2016-02-10
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
Comment
Question by:shampouya
  • 7
8 Comments
 
LVL 12

Accepted Solution

by:
Jeff Darling earned 500 total points
ID: 40617430
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
 
LVL 12

Expert Comment

by:Jeff Darling
ID: 40617468
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
 

Author Comment

by:shampouya
ID: 40617647
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
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 
LVL 12

Expert Comment

by:Jeff Darling
ID: 40617702
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

0
 
LVL 12

Expert Comment

by:Jeff Darling
ID: 40617707
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
 
LVL 12

Expert Comment

by:Jeff Darling
ID: 40617715
This is what the arrays look like when populated.

arrays populated
0
 
LVL 12

Expert Comment

by:Jeff Darling
ID: 40617760
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
 
LVL 12

Expert Comment

by:Jeff Darling
ID: 40617782
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

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

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

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question