Text to Columns Macro

[DaveRadford]

Afternoon All,

I have a macro or rather i recorded one for what i wanted to automate:

Columns("A:A").Select

Selection.TextToColumns Destination:=Range( _
"Table_owssvr_1[[#Headers],[Workstage]]"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True


May be a fairly silly questions, but, when i try and call this in a sub or even run the macro, it complains that i can only do one column at a time. .TestToColumns is what its called so i dont get it. I have tried the range being A:A etc.

Thanks,

 

[Joe4]

Text to Columns turns a single column into multiple columns, so it can only be run on one column at a time. So if you wish to do multiple columns, you will need to do it multiple times (can probably create a loop to do this).

 

[DaveRadford]

Thanks for the reply.

I have been using it as a quick way of data properties to text instead of number etc. Doing a standard format doesnt change the feilds.

Im selecting only one column and after i do it manualyl it onyl returns one column.

Thanks,

Dave

 

[Joe4]

I have been using it as a quick way of data properties to text instead of number etc. Doing a standard format doesnt change the feilds.

That's fine. I do this sometimes also.

Im selecting only one column and after i do it manualyl it onyl returns one column.

If you are not trying to to split the data into multiple columns (which it sounds like you are not trying to do), this is the expected behavior.

So what exactly is the issue you are having?

You may want to check your code, as you seem to have some named range for your destination. If it is not the same as "A:A", it may cause you problems. You usually want to have your Selected range and Destination ranges be the same if you are just trying to convert the data format (and not delimit data).

 

[DaveRadford]

the Code was generated automatically by the Macro, you are correct that i am formating data within a named range. Rnage A:A happens to be Table_owssvr_1[[#Headers],[Workstage]]"),

I have attempted to make the range A:A aswell however this does not change the error i have been experiencing.

I am trying to format all the cells as general, however by going in to properties and settings this it does nothing. This way the formatting is reset and reapplied (so im guessing)

 

[Joe4]

It looks like TextToColumn only wants you to enter the first cell in your Destination range, i.e. A1:

    Columns("A:A").Select
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1)

I am trying to format all the cells as general, however by going in to properties and settings this it does nothing. This way the formatting is reset and reapplied (so im guessing)

It is important to understand how Excel works. Changing the formatting of any cell does NOT change the nature or data type of that cell's entry - it simply changes how that cell is presented to you (think of it this way, you can put a coat and hat on a dog, but underneath it is still a dog). The data type of a cell's entry is "captured" at point of entry.

If you want to change the data type of a cell, then you need to use "re-enter" the data correctly, and that is what TextToColumns will do for you on a whole column simultaneously (which sure beats doing each cell individually).

 

[DaveRadford]

Unfortunately i get the same error when using this code:

With Sheets("WSMASTER")
Range("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1)
End With

 

[Joe4]

I tried the code that you just posted, and it works for me.

What exactly is the error message that is being displayed?
Do you have any protected/locked cells on this worksheet?

 

[DaveRadford]

i think im narrowing it down, when your code worked did you have it "With sheets" ? mine worked until i tried using it with with sheets ("WSMaster") then started failing with columns error.

 

[Joe4]

Maybe instead of trying "With Sheets...", use:
Sheets("WSMASTER").Activate
This way you are selecting/activating the sheet before running the TextToColumns code.

Normally, people discourage the use of Activate or Select, as it can slow your code down, but using it once in your code will have a neglible affect on your code.