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,"|")
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
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.