Solved

How does the FieldInfo argument work for TextToColumns in VBA?

Posted on 2015-02-18
8
6,277 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
8 Comments
 
LVL 13

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 13

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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
LVL 13

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 13

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 13

Expert Comment

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

arrays populated
0
 
LVL 13

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 13

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

717 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