VBA - Text to columns with variable array

[Oliver Dewar]

Hello MrExcel!

I have the following code in a macro:

Range("BB9").TextToColumns Destination:=Range("BH1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
        :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
        Array(7, 1)), TrailingMinusNumbers:=True

I recorded this with the macro recorder - I now need to adapt the code so it automatically includes the right number of arrays for the csv info that may be being extracted.

This works fine for seven columns of info, but I have varying csv's.

How can I use a variable array in this sequence?

Cheers all.

 

[fredlo2008]

Hi Oliver,

I am new to VBA too. If you search the help file for any method or property you can get a lot of information out of it. So i did it an found out that all the specifications in the syntax are optional, so you can omit it. The thing VBA recorder records everything. In other words you do not need to specify the number of column the program will do that.

So here is the code you need. Try it in a copy of your file.

Range("[LEFT][COLOR=#574123]BB9[/COLOR][/LEFT]
").TextToColumns Destination:=Range("[LEFT][COLOR=#574123]BH1[/COLOR][/LEFT]
")

It will even work with more than one line

Range("[LEFT][COLOR=#574123]BB9:BB15[/COLOR][/LEFT]
").TextToColumns Destination:=Range("[LEFT][COLOR=#574123]BH1[/COLOR][/LEFT]
")

Hope it helps

PS: I cannot get my lines to display correctly. Can any moderator fix it for me please?

 

[Oliver Dewar]

Thanks Fred, but I don't think you understood my question (unless I misunderstand your answer of course!).

I'm not worried about where the destination is - I need to know how to make excel determine the number of arrays for the fieldinfo - ie the number of columns that the original csv text includes.

Cheers Oliver

 

[Oliver Dewar]

Fred - not sure who's not understanding who, but probably I'm in error! I just removed the referrence to Array(1,0), array(2,0) etc completely and it still worked.

As this was triggered by you saying that the recorder records everything - and that some is unnecissary - I must thank you for solving this case!

 

[fredlo2008]

I am glad. That was what I was trying to say that you needed only the destination only also I did not include it but I think it would be safe to include the delimiter which in your case is a comma.

Range("A9").TextToColumns Destination:=Range("C1"), Comma:=True