Solved

How does the FieldInfo argument work for TextToColumns in VBA?

Posted on 2015-02-18
8
3,666 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 12

Expert Comment

by:Jeff Darling
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 12

Expert Comment

by:Jeff Darling
Comment Utility
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
Comment Utility
This is what the arrays look like when populated.

arrays populated
0
 
LVL 12

Expert Comment

by:Jeff Darling
Comment Utility
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
Comment Utility
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

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…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

763 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

8 Experts available now in Live!

Get 1:1 Help Now