Apache OpenOffice Community Forum

I'm trying to get a macro to run in a calc file that checks
all sheets for special characters (?\/ etc.) and deletes them.
deletes them. I realize that this is not possible with Left. Is there
a basic function that checks all characters of a sheet name for these characters?

Look up the InStr function in the IDE help file

You can also use the like operator

and what is the replacing part?

Misunderstood - thought you meant delete the sheets. Look up mid in the help file.

I really wonder for what reason you think it makes sense to search the names of spreadsheets for characters that are not allowed in these names.
If you want to search the contents of sheets for something by user code, create a SearchDescriptor, set the property .SearchRegularExpression to True, and assign the respective RegEx to .SearchString. If you want to delete the findings, use the empty string for the .ReplaceString.

I would be sure that there are no characters which are not compatile for hyperlinks, which based on sheetname

you can use the Calc Substitute function as well

thx for the Calc Substitute function snippets

The basic-function replace exists! (at least in Libreoffice)
edit: corrected typo

karolus wrote: ↑Sat Apr 15, 2023 6:46 pm The basic-function replace exists! (at least in Libreoffice)

Code: Select all

Sub Main
calcDoc = ThisComponent
dim ch as string
for each sheet in calcDoc.Sheets
	sheetname = sheet.name
	letters ="-?\/!"
	for i = 1 to  len(letters)
		ch =  mid(letters,i,1)
		sheetname = replace( sheeetname, ch, "",1,,0)
	next
	sheet.setName( sheetname )
next
end sub

Unfortunately does not work after my test (LO 7.1.8.1)

klausk wrote: ↑Sat Apr 15, 2023 7:18 pm

karolus wrote: ↑Sat Apr 15, 2023 6:46 pm The basic-function replace exists! (at least in Libreoffice)

Code: Select all

Sub Main
calcDoc = ThisComponent
dim ch as string
for each sheet in calcDoc.Sheets
	sheetname = sheet.name
	letters ="-?\/!"
	for i = 1 to  len(letters)
		ch =  mid(letters,i,1)
		sheetname = replace( sheeetname, ch, "",1,,0)
	next
	sheet.setName( sheetname )
next
end sub

Unfortunately does not work after my test (LO 7.1.8.1)

So you're not able to correct the typo: sheetname = replace( sheeetname, ch, "",1,,0)

(once again)
The characters []?/\: aren't allowed in sheetnames anyway - and never were. No need to check insofar.

In addition1 : The character ' (single quote) is not allowed to occur as the first or the last character of a sheetname.

In addition2: If a sheetname needs to be changed, you also need to check for an existing sheet already bearing the name resulting from your changes. In case of conflict you need a program branch (Sub?) to resolve it. Not exactly simple!

In addition3: Any urgent reason to change sheetnames at all should be seen as a welcome opportunity, to replace all the unclean sheetnames used in the document by CLEAN ones. A clean sheetname should not only avoid characters that are forbidden for sheetnames (or in a slightly enlarged scope), but should follow a clear traditional naming convention like:
"Start with a basic Latin letter and continue with zero or more characters taken from the range of basic Latin letters again, or from the decimal digits (0-9, Western kind) or underscores."
In specific spaces and every kind of quotes should be excluded. Not obeying such a rule you may get in a next trouble every other day if you need to use your sheet names in document automation or in an external context.

In addition4: Changing a sheetname you need to consider (and check / clear) if the old name was used as a literal part of a formula / function call (INDIRCET() e.g.). This may even afflict badly designed external sheets. Your intention seems to point in the direction that you want to pass the new names to such an external functionality. Reconsider the design.