Text to columns
Text to columns
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
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
Re: text to columns
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 functionWhere is the default workbook stored please, the equivelent of Excels 'normal.dot'?
File - Templates...
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
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.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
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.
Re: Text to columns
Hi,
Thanks.
Doesn't seem to do much though.
Regards
Peter
Thanks.
Doesn't seem to do much though.
Regards
Peter
OpenOffice 4.1.4 on Windows 10
Re: Text to columns
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.
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 257 times
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
---
Lupp from München
Re: Text to columns
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
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
Re: Text to columns
(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.
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 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
---
Lupp from München
Re: Text to columns
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.
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.
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
Re: Text to columns
Hi Lupp,
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
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
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
Re: Text to columns
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
viewtopic.php?f=9&t=60198
The filter options info is here
https://wiki.openoffice.org/wiki/Docume ... er_Options
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
Re: Text to columns
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 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
---
Lupp from München
Re: Text to columns
Hi Lupp & Jeje,
thanks, I'll play with those and see what I can get it to do.
Regards
Peter
thanks, I'll play with those and see what I can get it to do.
Regards
Peter
OpenOffice 4.1.4 on Windows 10
Re: Text to columns
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.)
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 194 times
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
---
Lupp from München
Re: Text to columns
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
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
Re: Text to columns
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.Peterd51 wrote:It works OK, I'm just looking to remove one step from the process.
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 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
---
Lupp from München