Macro to do TextToColumn without dialog box

English
,
#1

I would like to automatically convert a column using TextToColumn. I have the following Macro which works except it brings up the TextToColumn dialog box.

Sub Text_to_columns
dim document as object
dim dispatcher as object
document = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
dim args1(0) as new com.sun.star.beans.PropertyValue
dim args2(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "ToPoint"
args1(0).Value = "$G$32:$G$40"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())
dispatcher.executeDispatch(document, ".uno:TextToColumns", "", 0.,args2())
Msgbox "Completed"
End Sub
#3

As written it converts number strings to numbers in cells G32:G40. I just tested it and it did that except it brings up the TextToColumns dialog box first.

#4

If you don’t have number strings in those cells, then yes, it only says completed. it is only meant as a test.

#10

How were the number strings separated? Is there an assertion of “nothing else does occur”?
What about the number of number strings?

#12

A practical way to use a text number in operations is to multiply it by 1 before the operation.

#14

“Multiply by 1” or similar workarounds don’t help anything if a cell is to be used in a number sequence like SUM() and all other functions taking such cell range expect them. In other cases the results of arithmetic operations with strings depend on the conversion settings, Tools → Options → Calc → Formula, Detailed Calculation Settings, Custom Details, Contents to Numbers. My recommendation is always to set that to Generate #VALUE! error to detect errors early, as all other settings, specifically anything locale dependent (which unfortunately is the only thing Excel does and hence default) tend to lead into an erroneous corner sooner or later.

#5

Alternative macro, using Find and Replace…

Sub Converter
rem ----------------------------------------------------------------------
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "ToPoint"
args1(0).Value = "$G$32:$G$40"
createUnoService("com.sun.star.frame.DispatchHelper") _
.executeDispatch(ThisComponent.CurrentController _
.Frame, ".uno:GoToCell", "", 0, args1())
rem ----------------------------------------------------------------------
dim args2(20) as new com.sun.star.beans.PropertyValue
args2(0).Name = "SearchItem.StyleFamily" : args2(0).Value = 2
args2(1).Name = "SearchItem.CellType" : args2(1).Value = 0
args2(2).Name = "SearchItem.RowDirection" : args2(2).Value = true
args2(3).Name = "SearchItem.AllTables" : args2(3).Value = false
args2(4).Name = "SearchItem.SearchFiltered" : args2(4).Value = false
args2(5).Name = "SearchItem.Backward" : args2(5).Value = false
args2(6).Name = "SearchItem.Pattern" : args2(6).Value = false
args2(7).Name = "SearchItem.Content" : args2(7).Value = false
args2(8).Name = "SearchItem.AsianOptions" : args2(8).Value = false
args2(9).Name = "SearchItem.AlgorithmType" : args2(9).Value = 1
args2(10).Name = "SearchItem.SearchFlags" : args2(10).Value = 71680
args2(11).Name = "SearchItem.SearchString" : args2(11).Value = "^."
args2(12).Name = "SearchItem.ReplaceString" : args2(12).Value = "&"
args2(13).Name = "SearchItem.Locale" : args2(13).Value = 255
args2(14).Name = "SearchItem.ChangedChars" : args2(14).Value = 2
args2(15).Name = "SearchItem.DeletedChars" : args2(15).Value = 2
args2(16).Name = "SearchItem.InsertedChars" : args2(16).Value = 2
args2(17).Name = "SearchItem.TransliterateFlags" : args2(17).Value = 1280
args2(18).Name = "SearchItem.Command" : args2(18).Value = 3
args2(19).Name = "SearchItem.SearchFormatted" : args2(19).Value = false
args2(20).Name = "SearchItem.AlgorithmType2" : args2(20).Value = 2
createUnoService("com.sun.star.frame.DispatchHelper") _
.executeDispatch(ThisComponent.CurrentController _
.Frame, ".uno:ExecuteSearch", "", 0, args2())
End Sub
#8

Thank you! I believe that works. Is it necessary to set all of those options even if I am OK with the defaults?

#6

Sorry. I wanted to post this as a comment to the question, but wrongly made an “answer” of it.

This is exactly what .uno:TextToColumns should be expected to do.

If you are a frequent user of >Data>Text to Columns... you will know that the tool is in need of getting lots of parameters. Not only the parameters that can be set by enabling or disabling options, but also those being defined by user interaction within the dialög per column (or would-be column) need to get values. The overall settings you need to consider are roughly equivalent to the parameters for loading CSV files into spreadsheets, and that’s the most unhandy and complicated casec of this kind.
You seem to expect the macro recorder, to also record your interactions with the dfialog. This doesn’t happen.
If you thoroughly explain the settings you need, somebody here may be able to suggest a formula (based on the REGEX() function, I suppose) doing for you what you need.

#7

The macro works except it brings up the dialog box. The default settings are fine. What I want is for the dialog box NOT to come up. Is there a way from the macro to make this work? (and no, I am not using the macro recorder at all).

#9

What do you assume are the “default settings”?
In fact my Text to Columns... dialog comes with the set of settings last used. I would asume it’s the same thing for you.

I don’t know a way to cause a dialog opened by a .uno: command to close without user interaction.

?? If you didn’t it yourself, somebody else did it for you. The macro you posted is a recorderd macro. (Otherwise somebody must have typed it exactly the same way with the exception of appending the MsgBox.command, and changing a few letters to upper case.)

#11

Sorry, I modified something that was posted as a solution to a different problem. That was, as you observe, undoubtedly done with the macro recorder. The macro posted by @schiavinatto above does what I want although I am still left wondering (for future reference) if there is in fact a way to use TextToColumns without the dialog box coming up.

#13

Oh my!
You wanted to convert single ordinary numbers from textual representation to number values.
The Text to Columns... isn’t the appropriate tool for this if doing it by user code. You need to make sure that the cells of the respective range don’t preserve a non-numeric or misleading NumberFormat. Then the content must be re-inserted for recognition as numbers. To use the .FormulaArray for this step is a little trick.
To apply the General format and aasignment of the key 0 (zero) to the respective property of the range object should suffice. If your needs are different, you can giv an additional cell serving as a NF template.

Sub Converter()
rgN = "$G$32:$G$40" REM Hard coded here.
doc = ThisComponent
sheet = doc.CurrentController.ActiveSheet
rg = sheet.getCellRangeByName(rgN)
da = rg.getDataArray
numFormatKey = 0 REM Should apply number format "General"
rg.NumberFormat = numFormatKey
rg.setFormulaArray(da)
End Sub

Hard coded ranges in Sub aren’t an exactly good idea. The following code allows to parametrize the task in a simple way. (Now also for the nuimber format.)

Sub toTemplateNF_Converter()
REM Many more enhancements possible.
paramCellN ="A1"
REM Name of Template cell, also containing tjhe name of the range to work on.
doc = ThisComponent
sheet = doc.CurrentController.ActiveSheet
paramCell = sheet.getCellRangeByName(paramCellN)
rg = sheet.getCellRangeByName(paramCell.String)
numFormatKey = paramCell.NumberFormat
da = rg.getDataArray
rg.NumberFormat = numFormatKey
rg.setFormulaArray(da)
End Sub

See also:
disask73043example.ods (13.6 KB)

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.