Automatic sheet naming

I am an instructor who uses Calc as a grade book. I name the sheets with the student’s name. This is a tedious process and must be repeated every semester. I wonder if it would be possible to implement a function where the name of the sheets are taken from one place. Example: I have one sheet called “data” that contains the name of the classes as well as the name of the students. It would be awesome if it was possible to use the cells containing the student’s name as sheet names. (I guess this is more of a feature request than anything.

To qubit. Thank you so much. I shall use that.

@Olle, you’re quite welcome. If you test out the code and it works for you, please mark the answer as ‘correct’.

Hi @Olle,

Given that you’d probably want the sheet to remain even if you (accidentally) deleted a student’s name from a spreadsheet cell, I think that some form of automation might be best for you.

Here’s a quick macro that you can adapt to your needs. I made a couple of simple assumptions

  • Students are listed in a sheet named ‘Students’

  • Column ‘A’ contains students names

  • There are no empty cells before or between student names

  • Student names are unique

     Sub Main
       Set thisBook = ThisComponent
       studentsSheet = thisBook.Sheets.getByName("Students")
    
       i = 0
       Do
         c = studentsSheet.getCellByPosition(0, i)
    
         REM // Stop creating sheets for students once we find a
         REM // row with no name.
         If c.Type = com.sun.star.table.CellContentType.EMPTY Then
           print "Exiting: Found row with no student name."
           Exit Do
         EndIf
    
         studentName = c.String
         sNew = thisBook.createInstance("com.sun.star.sheet.Spreadsheet")
         thisBook.Sheets.insertByName(studentName, sNew)
         i = i + 1
    
       REM // Sanity-check on looping forever. We don't expect 
       REM // to have over 1000 students.
       Loop Until i > 1000
     End Sub