Page 1 of 1

Excel Text to column script not working?

Posted: 02 Mar 2020, 11:13
by JKnight_xbt33
Dear all,
I made a text to column script after reading these threads. I am pretty sure it worked a few weeks back after testing on a few excel files.

Have i accidentally deleted some important code

Code: Select all

^!b::  ;deliminates 1st column to get data separated into columns

xl:= ComObjActive("Excel.Application")
xl.activesheet.range("a1").EntireColumn.TextToColumns

Return
I also read up that some people use the below line in their script but this does not work as intended either:

Code: Select all

xl.Selection.TextToColumns([color=#FF0000]xl.range("A1")[/color],1,1,0,1,0,0,0,1,"|")
your help is always appreciated
J

Re: Excel Text to column script not working?  Topic is solved

Posted: 03 Mar 2020, 15:32
by TLM
Assuming the text delimiter is | ( eg. abc|def|ghi )
try: xl.Range( "A1" ).EntireColumn.TextToColumns( xl.range("A1") ,1,1,0,1,0,0,0,1,"|")

If the delimiter is something else ( eg. abc~def~ghi )
use: xl.Range( "A1" ).EntireColumn.TextToColumns( xl.range("A1") ,1,1,0,1,0,0,0,1,"~")

Re: Excel Text to column script not working?

Posted: 04 Mar 2020, 00:11
by flyingDman
I don't get any of these to work unless a SafeArray is included. So this works for me:

Code: Select all

var:= "abc|def|xyz"
Xl := ComObjActive("Excel.Application")
xl.range("a1").value := var
SA := ComObjArray(12, 2)
SA[1] := 1
xl.range("a1").TextToColumns(xl.Range("A10"), 1,,,,,,,1,"|", SA)      ; put the parsed data in A10
(don't ask me why it needs to be populated this way!).
Also there seems to be a simple way around this issue:

Code: Select all

var:= "abc|def|xyz"
clipboard := ""
for x,y in strsplit(var,"|")
	clipboard .= x=1?y:"`t"y
xl.range("a11").pastespecial(-4104)

Re: Excel Text to column script not working?

Posted: 04 Mar 2020, 07:49
by JKnight_xbt33
Thanks for your responses both.
I've selected TLM answer because it was the simplest for me to understand and re-use later.

Appreciate the support
:dance:

Re: Excel Text to column script not working?

Posted: 04 Mar 2020, 11:24
by flyingDman
My issue with @TLM solution is that this seems to work:

Code: Select all

var := "abc|def|ghi"
Xl := ComObjActive("Excel.Application")
xl.range("a1").value := var
xl.range( "a1" ).EntireColumn.TextToColumns( xl.range("a1"),1,1,0,1,0,0,0,1,"|")
But if you want the results in a different set of cells (a10), it doesn't:

Code: Select all

var := "abc|def|ghi"
Xl := ComObjActive("Excel.Application")
xl.range("a1").value := var
xl.range( "a1" ).EntireColumn.TextToColumns( xl.range("a10"),1,1,0,1,0,0,0,1,"|")
Edit: drop the EntireColumn and it does work

Re: Excel Text to column script not working?

Posted: 04 Mar 2020, 11:59
by TLM
I was going by the op.

@flyingDman I just tested this:

Code: Select all

xl.Range( "A1" ).value := "abc~def~ghi"
xl.Range( "A1" ).EntireColumn.TextToColumns( xl.range("A10") ,1,1,0,1,0,0,0,1,"~")
It works just fine.