Automatic sheet naming

[Olle]

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.

[Olle]

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

[qubit1]

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

[qubit1]

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