Page 1 of 1

Text to columns

Posted: Sun Mar 18, 2018 6:52 pm
by Peterd51
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

Re: text to columns

Posted: Sun Mar 18, 2018 7:50 pm
by Zizi64
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...

Re: Text to columns

Posted: Mon Mar 19, 2018 10:28 am
by Peterd51
Hi,

Thanks.

Doesn't seem to do much though.

Regards
Peter

Re: Text to columns

Posted: Mon Mar 19, 2018 3:32 pm
by Lupp
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.

Re: Text to columns

Posted: Mon Mar 19, 2018 5:43 pm
by Peterd51
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

Re: Text to columns

Posted: Sat Mar 24, 2018 3:33 pm
by Lupp
(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.

Re: Text to columns

Posted: Sat Mar 24, 2018 4:08 pm
by JeJe
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.

Re: Text to columns

Posted: Sat Mar 24, 2018 5:51 pm
by Peterd51
Hi Lupp,

Code: Select all

  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

Re: Text to columns

Posted: Sat Mar 24, 2018 7:32 pm
by JeJe
There's a similar thread here

viewtopic.php?f=9&t=60198

The filter options info is here

https://wiki.openoffice.org/wiki/Docume ... er_Options

Re: Text to columns

Posted: Sun Mar 25, 2018 12:24 am
by Lupp
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.

Re: Text to columns

Posted: Sun Mar 25, 2018 10:23 am
by Peterd51
Hi Lupp & Jeje,

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

Regards
Peter

Re: Text to columns

Posted: Sun Mar 25, 2018 1:28 pm
by Lupp
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.)

Re: Text to columns

Posted: Mon Mar 26, 2018 10:33 am
by Peterd51
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

Re: Text to columns

Posted: Mon Mar 26, 2018 3:16 pm
by Lupp
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.