Solved

How does the FieldInfo argument work for TextToColumns in VBA?

Posted on 2015-02-18
8
4,488 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
Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

 
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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

813 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now