Excel Text to column script not working? Topic is solved

Get help with using AutoHotkey and its commands and hotkeys
JKnight_xbt33
Posts: 112
Joined: 18 Sep 2019, 02:06

Excel Text to column script not working?

02 Mar 2020, 11:13

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
User avatar
TLM
Posts: 1589
Joined: 01 Oct 2013, 07:52
GitHub: TLMcode
Contact:

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

03 Mar 2020, 15:32

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,"~")
User avatar
flyingDman
Posts: 869
Joined: 29 Sep 2013, 19:01

Re: Excel Text to column script not working?

04 Mar 2020, 00:11

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)
JKnight_xbt33
Posts: 112
Joined: 18 Sep 2019, 02:06

Re: Excel Text to column script not working?

04 Mar 2020, 07:49

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:
User avatar
flyingDman
Posts: 869
Joined: 29 Sep 2013, 19:01

Re: Excel Text to column script not working?

04 Mar 2020, 11:24

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
User avatar
TLM
Posts: 1589
Joined: 01 Oct 2013, 07:52
GitHub: TLMcode
Contact:

Re: Excel Text to column script not working?

04 Mar 2020, 11:59

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.

Return to “Ask For Help”

Who is online

Users browsing this forum: CaptainZok, hasantr, mikeyww, waa2 and 47 guests