[Solved] Search and replace in sheetnames

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
klausk
Posts: 19
Joined: Thu May 28, 2009 9:07 pm

[Solved] Search and replace in sheetnames

Post by klausk »

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?

Code: Select all

Sub Main
Dim calcDoc as Object 
calcDoc = ThisComponent
j=0
for i = 0 to calcDoc.sheets.count-1 
   If Left(calcDoc.sheets(i).name,2) = "XT" then
    j=j+1
   end if
next
end sub
Last edited by MrProgrammer on Sat Apr 15, 2023 4:26 am, edited 2 times in total.
Reason: Tagged ✓ [Solved] -- MrProgrammer, forum moderator
OOo 3.1.X on Ubuntu 8.x + opensuse 11.1
JeJe
Volunteer
Posts: 2763
Joined: Wed Mar 09, 2016 2:40 pm

Re: Search and replace in sheetnames

Post by JeJe »

Look up the InStr function in the IDE help file
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
JeJe
Volunteer
Posts: 2763
Joined: Wed Mar 09, 2016 2:40 pm

Re: Search and replace in sheetnames

Post by JeJe »

You can also use the like operator

Code: Select all

msgbox "nametotest" like "*[?\/]*"
msgbox "nametote?st" like "*[?\/]*"
msgbox "nametotest/" like "*[?\/]*"

Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
klausk
Posts: 19
Joined: Thu May 28, 2009 9:07 pm

Re: Search and replace in sheetnames

Post by klausk »

and what is the replacing part?
OOo 3.1.X on Ubuntu 8.x + opensuse 11.1
JeJe
Volunteer
Posts: 2763
Joined: Wed Mar 09, 2016 2:40 pm

Re: Search and replace in sheetnames

Post by JeJe »

Misunderstood - thought you meant delete the sheets. Look up mid in the help file.
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Search and replace in sheetnames

Post by Lupp »

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.
Last edited by Lupp on Sat Apr 15, 2023 12:18 am, edited 1 time in total.
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
klausk
Posts: 19
Joined: Thu May 28, 2009 9:07 pm

Re: Search and replace in sheetnames

Post by klausk »

I would be sure that there are no characters which are not compatile for hyperlinks, which based on sheetname
OOo 3.1.X on Ubuntu 8.x + opensuse 11.1
JeJe
Volunteer
Posts: 2763
Joined: Wed Mar 09, 2016 2:40 pm

Re: Search and replace in sheetnames

Post by JeJe »

you can use the Calc Substitute function as well

Code: Select all

dim ch as string
fc =  CreateUNOService("com.sun.star.sheet.FunctionAccess")
NameToWorkon ="/Na\meToWorkon?"
lettersToRemove ="?\/"
for i = 1 to len(lettersToRemove )
ch =  mid(lettersToRemove,i,1)
NameToWorkon = fc.callFunction("SUBSTITUTE",array(NameToWorkon,ch , ""))
next
msgbox NameToWorkon
				
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
klausk
Posts: 19
Joined: Thu May 28, 2009 9:07 pm

Re: [SOLVED]Search and replace in sheetnames

Post by klausk »

thx for the Calc Substitute function snippets

Code: Select all

Sub Main
calcDoc = ThisComponent
dim ch as string
fc =  CreateUNOService("com.sun.star.sheet.FunctionAccess")
for each sheet in calcDoc.Sheets()
	NameToWorkon=sheet(i).name
	lettersToRemove ="-?\/!"
	for i = 1 to len(lettersToRemove )
		ch =  mid(lettersToRemove,i,1)
		NameToWorkon = fc.callFunction("SUBSTITUTE",array(NameToWorkon,ch , ""))
	next
	sheet(i).setName(NameToWorkon)
next
end sub
OOo 3.1.X on Ubuntu 8.x + opensuse 11.1
User avatar
karolus
Volunteer
Posts: 1158
Joined: Sat Jul 02, 2011 9:47 am

Re: [Solved] Search and replace in sheetnames

Post by karolus »

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( sheetname, ch, "",1,,0) 'typo sheeetname →→ sheetname'
	next
	sheet.setName( sheetname )
next
end sub
edit: corrected typo
Last edited by karolus on Sat Apr 15, 2023 9:47 pm, edited 1 time in total.
AOO4, Libreoffice 6.1 on Rasbian OS (on ARM)
Libreoffice 7.4 on Debian 12 (Bookworm) (on RaspberryPI4)
Libreoffice 7.6 flatpak on Debian 12 (Bookworm) (on RaspberryPI4)
klausk
Posts: 19
Joined: Thu May 28, 2009 9:07 pm

Re: [Solved] Search and replace in sheetnames

Post by klausk »

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)
OOo 3.1.X on Ubuntu 8.x + opensuse 11.1
User avatar
karolus
Volunteer
Posts: 1158
Joined: Sat Jul 02, 2011 9:47 am

Re: [Solved] Search and replace in sheetnames

Post by karolus »

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)
AOO4, Libreoffice 6.1 on Rasbian OS (on ARM)
Libreoffice 7.4 on Debian 12 (Bookworm) (on RaspberryPI4)
Libreoffice 7.6 flatpak on Debian 12 (Bookworm) (on RaspberryPI4)
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: [Solved] Search and replace in sheetnames

Post by Lupp »

(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.
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
Post Reply