Text to columns

Creating a macro - Writing a Script - Using the API

Text to columns

Postby Peterd51 » Sun Mar 18, 2018 6:52 pm

Hi,

after opening a blank Calc sheet, I compose CSV data lines in VB, then select each row in turn, column A, and use '.SetString'...no problem so far.

When it's done I select 'text to columns' from VB and it brings up the dialog box which is always set to 'tab' delimited.

Is there any way to set the Calc default to always show 'comma'...it wouldn't matter if it also showed 'tab' as well.

That way I can just hit enter without using the mouse (or any other keys).

I get the same thing when selecting 'Data / text to columns' too.

Where is the default workbook stored please, the equivelent of Excels 'normal.dot'?

Regards
Peter
OpenOffice 4.1.4 on Windows 10
Peterd51
 
Posts: 31
Joined: Sun Dec 31, 2017 12:06 pm

Re: text to columns

Postby Zizi64 » Sun Mar 18, 2018 7:50 pm

Where is the default workbook stored please, the equivelent of Excels 'normal.dot'?


The feature is named as "Default template" in the Apache OpenOffice and LibreOffice. You can manage the templates (included the default one) by the built-in function
File - Templates...
Tibor Kovacs, Hungary; LO4.4.7, LO6.1.6 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.4; AOO4.1.5
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 7964
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Text to columns

Postby Peterd51 » Mon Mar 19, 2018 10:28 am

Hi,

Thanks.

Doesn't seem to do much though.

Regards
Peter
OpenOffice 4.1.4 on Windows 10
Peterd51
 
Posts: 31
Joined: Sun Dec 31, 2017 12:06 pm

Re: Text to columns

Postby Lupp » Mon Mar 19, 2018 3:32 pm

You shouldn't expect to be able to do such things by "VB" (assumed to mean Visual Basic). AOO and VB are from different planets.

And:
Why go that detour? Why not place the parts one by one into the respective cells? A sequence of contiguous cells in a row is as easily accessed as an object as a single cell. You may then use the .DataArray property e.g.
If you need the composite ("data line") anyway you may either first create an array from the parts and then get the composite by the Join() function, or reversely compose your lines in another way and then use the Split() function with the comma as delimier to get an array containing the parts.

See attached demo.
Attachments
aoo92858SplitToColumns_1.ods
(12.75 KiB) Downloaded 49 times
On Windows 10: LibreOffice 6.2 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2462
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Text to columns

Postby Peterd51 » Mon Mar 19, 2018 5:43 pm

Hi Lupp,

I do it this way rather than each cell in turn as it's far quicker to copy the data to the spreadsheet.

It can be up to 700 rows of 14 columns.

It works OK, it's just that I would like the 'text to columns' options box to show 'comma' selected instead of, or as well as, 'tab' so I don't have to move the mouse and I can just hit enter.

The fewer things I have to do the better my chances of not messing it up as I get older...I'm already swapping letter, and sometimes whole words, as I type so I know my brain's starting to fail.

Regards
Peter
OpenOffice 4.1.4 on Windows 10
Peterd51
 
Posts: 31
Joined: Sun Dec 31, 2017 12:06 pm

Re: Text to columns

Postby Lupp » Sat Mar 24, 2018 3:33 pm

(We won't find out whose brain started to fail earlier.)

I don't know a way to call the 'Text to Columns...' tool by user code, and you didn't tell me how you achieved that from your VB macro.
Please tell us!

When called from the UI, the tool starts with always the same default settings, and I don't know a way to change the defaults. As opposed to AOO in recent LibO versions the 'Text to Columns...' dialog remembers the basic settings last used. They seem also to be saved to the user profile.
On Windows 10: LibreOffice 6.2 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2462
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Text to columns

Postby JeJe » Sat Mar 24, 2018 4:08 pm

As you're on Windows you could use something like AutoIt to simulate the keystrokes you want in response to a defined shortcut key.

Alt + D
(menu pops up)
x
(dialog pops up)
Alt + C
enter

https://www.autoitscript.com/site/autoit/

An OO macro that does what you want in response to a shortcut without the dialog would be better though.
Openoffice 4.1.2
Windows 8
JeJe
Volunteer
 
Posts: 507
Joined: Wed Mar 09, 2016 2:40 pm

Re: Text to columns

Postby Peterd51 » Sat Mar 24, 2018 5:51 pm

Hi Lupp,

Code: Select all   Expand viewCollapse view
  Set oRangeC = oSheetC.getCellRangeByName("A1:A900")

  Call oCTLC.Select(oRangeC)

  Set oDispC = oSMC.createInstance("com.sun.star.frame.DispatchHelper")
 
  'in excel: selection.TextToColumns Comma:=True, Space:=False, other:=False

  Call oDispC.executeDispatch(oCTLC, ".uno:TextToColumns", "", 0, argC())

  Set oRangeC = oSheetC.getCellRangeByName("A1:N900")
  oRangeC.Columns(0).OptimalWidth = True



I found this by creating a macro in Calc, selecting 'text to columns' and then editing the macro and copying the instructions across to VB.

In the Excel command I could specify 'comma' and I was hoping there'd be something similar in Calc.

I have a list of all of the commands that I've found so far, all tested and working with a few exceptions that I've noted, and I could upload the file if anyone is interested. It's a bit rough and ready, a bit mixed up, but as a quick reference to comand lines that I know will work from VB it's a handy guide for me.

Jeje: thanks for the suggestion, I may end up using something like that.

It's been a bit hectic with cars for the last few days, I've just bought a salavge car and I've been bashing out the dent in the tailgate, cutting and welding, got it on the road now, so I've not had time to follow up on my last programming session.

Regards
Peter
OpenOffice 4.1.4 on Windows 10
Peterd51
 
Posts: 31
Joined: Sun Dec 31, 2017 12:06 pm

Re: Text to columns

Postby JeJe » Sat Mar 24, 2018 7:32 pm

Openoffice 4.1.2
Windows 8
JeJe
Volunteer
 
Posts: 507
Joined: Wed Mar 09, 2016 2:40 pm

Re: Text to columns

Postby Lupp » Sun Mar 25, 2018 12:24 am

According to https://opengrok.libreoffice.org/xref/c ... /scalc.sdi the uno command 'TextToColumns' does not accept parameters in LibreOffice. I don't know the respective document for AOO, but I assumme it won't differ in this regard.
On Windows 10: LibreOffice 6.2 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2462
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Text to columns

Postby Peterd51 » Sun Mar 25, 2018 10:23 am

Hi Lupp & Jeje,

thanks, I'll play with those and see what I can get it to do.

Regards
Peter
OpenOffice 4.1.4 on Windows 10
Peterd51
 
Posts: 31
Joined: Sun Dec 31, 2017 12:06 pm

Re: Text to columns

Postby Lupp » Sun Mar 25, 2018 1:28 pm

I still feel "make first a compound and later split it again by TtC" to be a misconception. You will need to know the output range in advance after all, at least to make sure there's enough spare space.
Get the .DataArray from that range and put the "micro-items" there. Then set the DataArray back. Done.

On the other hand a kind of emulation of TtC by macro within the reduced range od duties is rather simple. Run the Sub 'demo' from the only BASIC module contained in the attached Calc document to see what I mean.

Most of the code there is for creating an example range and for organising the demo. The actual functionality is implemented with 23 lines of code in sparse layout. (Next time, please, supply an example of your own. This kind of question cannot easily be answered completely abstract. At least Lupp needs to develop it example-based.)
Attachments
aoo92858ttcExtra_1.ods
(11.46 KiB) Downloaded 48 times
On Windows 10: LibreOffice 6.2 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2462
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Text to columns

Postby Peterd51 » Mon Mar 26, 2018 10:33 am

Hi Lupp,

I do know the data range, 16 columns and between 100 and 700 rows.

All string data, I convert numbers to text to save them to an array or file.

I couldn't provide an example as the original query was about setting a default within Calc so any string of CSV data would do.

It works OK, I'm just looking to remove one step from the process.

Regards
Peter
OpenOffice 4.1.4 on Windows 10
Peterd51
 
Posts: 31
Joined: Sun Dec 31, 2017 12:06 pm

Re: Text to columns

Postby Lupp » Mon Mar 26, 2018 3:16 pm

Peterd51 wrote:It works OK, I'm just looking to remove one step from the process.


If the one step you want to remove from the process the manual choice of the comma as the column separator, I don't know remedy. Beyond that I tried some research and feel rather sure now that there is none.

On the other hand: A variable taking the .DataArray of up to 11200 cells for access shouldn't be any kind of problem.
On Windows 10: LibreOffice 6.2 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2462
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 3 guests