CODEWITHSUNDEEP
vbaexcelexcel-formula
Jeanjean
Jeanjean

Reputation: 863

VBA Data conversion texte format

I'm trying to convert datas:

Date;Employee;Ticket number;Table number
30/09/2016;Josh;005421;17
30/09/2016;Annie;004412;25
30/09/2016;John;001124;12

I need to change the format of my third column, otherwise i'll lose the "zeros".

Expected result :

Expected result

My code is the following :

 Range("A6:A1048576").TextToColumns Destination:=Range("A6"), DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, Semicolon:=True, Fieldinfo:=Array(Array(3,2))

The problem is that the format changes on the FIRST column with my code, not the third. I don't understand why.

The problem gotta be here :

 Fieldinfo:=Array(Array(3,2))

I recorded the process with the macro editor : Format text for col 3

Sub Macro1()
    '
    ' Macro1 Macro
    '

    '
    Columns("A:A").Select
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=True, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(Array(1, 1), Array(2, 1), Array(3, 2), Array(4, 1)), TrailingMinusNumbers:= _
        True
    Range("B6").Select
End Sub

My code looks similar to the macro editor. Therefore, I don't get why I don't get the expected result.

Could you please give me a hand?

Thanks and have a great day

Upvotes: 1

Views: 116

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57683

As your macro recorder shows you need to specify

FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 2), Array(4, 1))

One array for each column.

When the data is delimited, this argument is an array of two-element arrays, with each two-element array specifying the conversion options for a particular column. The first element is the column number (1-based), and the second element is one of the xlColumnDataType constants specifying how the column is parsed.

Here is an example how to generate an array for FieldInfo dynamically:
First we need to determine how many columns we need. We do this by counting the semicolons.

Sub GeneraterFieldInfoArrayExample()
    Const cDelimiter As String = ";"

    Dim str As String
    str = "30/09/2016;Josh;005421;17" 'replace with representative data, something like ThisWorkbook.Worksheets("Sheet1").Range("A6").Value

    Dim countCols As Long
    countCols = Len(str) - Len(Replace(str, cDelimiter, "")) + 1 'determine how many columns are needed by counting semicolons. Afterwards we build the array.

    Dim arrFieldInfo() As Variant
    ReDim arrFieldInfo(countCols - 1) As Variant 'resize array

    Dim iCol As Long
    For iCol = 1 To countCols 'generate array for each column
        Select Case iCol
            Case 3, 10 'format column 3 and 10 as text
                arrFieldInfo(iCol - 1) = Array(iCol, 2)
            Case Else 'format any other columns as default
                arrFieldInfo(iCol - 1) = Array(iCol, 1)
        End Select
    Next iCol

    'use like: Fieldinfo:=arrFieldInfo()
End Sub

In the first case all columns which have to be formatted as text ar listed. All other columns become default format. You can add additional cases on your needs.

Upvotes: 1

Related Questions