[Solved] Test for presence of sheet with macro

[gsd4me]

Dear all
A simple test (or so I thought) for the presence/absence of a worksheet.
When I run this, the first time it creates the 2 required spreadsheets but then on each subsequent call/run, it creates sequential new sheets (Alan 1_1, _2 ... and Mary 1_1, _2 ...)
Am I missing something here about the uniqueness of sheet names, or variable scope, or am I not seeing some syntactic or semantic error? or is the code complete nonsense ?
The actual code basis is an edit of a recorded macro just to let me find the correct syntax, so not fully written from scratch
many thanks
ADB

Code: Select all

REM  *****  BASIC  *****
rem define variables
private document   as object
private dispatcher as object
private  globalSheetName as  string
private globalSheetExists as  Boolean


sub doesNamedSheetExist
   rem does exactly what implied - global variable specifies the name of the relevant sheet
   dim localSheet as object
   on local Error GoTo noSheetLabel  
   ' Assume sheet exists
   globalSheetExists = True
   localSheet = oDoc.sheets.getbyName (globalSheetName)
   on Error GoTo 0
   exit sub

   noSheetLabel:
   globalSheetExists = False
   resume next
   
end sub

sub createNamedWorksheet
   rem Global variable holds the name of the relevant sheet name to be created
   dim args1(1) as new com.sun.star.beans.PropertyValue
   args1(0).Name = "Name"
   args1(0).Value = globalSheetName
   args1(1).Name = "Index"
   args1(1).Value = 1

dispatcher.executeDispatch(document, ".uno:Insert", "", 0, args1())

end sub

sub Main
rem ----------------------------------------------------------------------
rem ---------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
rem Check to see if the relevant (fixed name) worksheets exist and create them if necessary
globalSheetName = "Alan 1"
call doesNamedSheetExist ()
if globalSheetExists = False then
  call createNamedWorksheet ()
end if

globalSheetName = "Mary 1"
call doesNamedSheetExist ()
if globalSheetExists = False then
  call createNamedWorksheet ()
end if



end sub

Edit: Changed subject, was am I being stupidly blind, or blindly stupid? Macro failure  Make your post understandable by others  -- MrProgrammer, forum moderator

[Villeroy]

[Tutorial] Introduction into object inspection with MRI

Code: Select all

shs = ThisComponent.Sheets
if not shs.hasByName("Mary 1") then shs.insertNewByName("Mary 1", 0)
if not shs.hasByName("Alan 1") then shs.insertNewByName("Alan 1", 0)