Remove slash from name when insert new sheet with macro

[Vitorengcruz]

Hi there,

I made a list of products descriptions from the company I work, some of them have a slash symbol in their description.

I also made a dialog to select a product from this list and show it in a text field, where an user can check the product name before run some tasks. One task is create a new file and insert a new sheet with the name of the selected product.

I was wondering if there is a way to remove all slash and other non permitted symbols before run the insert new sheet function, as these symbol cause a runtime exception error in this situation:

new_doc = StarDesktop.loadComponentFromURL("private:factory/scalc", "_blank", 0, Array())
oSheets = new_doc.getSheets()  

oTextField1 = oDialog.GetControl("TextField1")
oNumericField1 = oDialog.GetControl("NumericField1")
	
oSheets.insertNewByName((oNumericField1.Text + " - " + oTextField1.Text), 0)

Result when TextField1 has a slash in it’s text:

Thanks in advance,

Vítor Cruz

[Lupp]

(I wouldn’t comment on the TextField idea and the shown code.)

The appropriate means to remove the disallowed characters from a string is the REGEX() function. To remove in specific characters disallowed in sheet names you can use the SearchString "(^')|\[|\]|\?|\*|:|/|\\|('$)" and the replaceString "" (empty).
The relevant lines of code should then be

Const disallowed = "(^')|\[|\]|\?|\*|:|/|\\|('$)"
Const replace    = ""
fa       = CreateUNOService("com.sun.star.sheet.FunctionAccess")
goodName = fa.callFunction("REGEX", Array(badName, disallowed, replace, "g"))

You then can use the goodName for the sheet to insert by name.
There are lots of doubtable situations, and the goodName can, of course, also be factually bad if it is empty or already used for a sheet in advance.

[sokol92]

This message appears when you try to enter an incorrect sheet name:


The sheet name must not:
• be empty
• already exist
• contain [ ] * ? : / \
• use ’ (apostrophe) as first or last character

[JohnSUN]

I find it easiest to use Function CheckNewSheetname() from the standard Tools library (module Misc).

[sokol92]

@JohnSUN

to use Function CheckNewSheetname()

This is an overly picky function.

Msgbox CheckNewSheetname(ThisComponent.Sheets, "Sheet.1")

shows Sheet_1.

In addition, although the oSheets argument is present, function does not check that the sheet name being added is already in the document.

[JohnSUN]

@sokol92

This is an overly picky function.

Msgbox CheckNewSheetname(ThisComponent.Sheets, "Sheet.1")

shows Sheet_1.

Well, as far as I understand the code in the question, we are not talking about Sheet1, but about 1Sheet

@sokol92

function does not check that the sheet name being added is already in the document.

This is done by the following procedure in the Misc module, Sub AddNewSheetName(oSheets as Object, ByVal SheetName as String)