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?
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
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
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)
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
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)
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)
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)
(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