2 Replies Latest reply: Dec 15, 2011 3:38 AM by koryu666 RSS

    How to see newly created sheets with GetSheet(j)?

      Hello,

       

      I have a macro which should dynamicaly create sheets if they are not already created. I do this by looping through the sheets and comparing their name.

      Unfortunately newly created sheets are not seen from the macro.

       

      See the code for reference:

      For i = 0 To unimportant.Count-1
           'Define the name of the sheet to check
           Select Case i
           Case 0
                sheetName = "one"
           Case 1
                sheetName = "two"
           Case 2
                'It's the same name as in Case 0, so there shouldn't be any sheet created.
                sheetName = "one"
           Case Else
                sheetName = "three"
           End Select
      
           createNew = true
      
           For j = 0 To ActiveDocument.NoOfSheets-1
                If (StrComp(ActiveDocument.GetSheet(j).GetProperties.Name, sheetName) = 0) Then
                     'This fires if there was a sheet with sheetName before starting the macro, but not if the sheet was created in the if statement later.
                     '(In this version it should fire when i = 2, but it doesn't, so a second sheet with name "one" is created.)
                     createNew = false
                End If
           Next
      
           'This works fine and the newly created sheet is also shown correctly, but is not seen in the for-Statement above.
           If createNew Then
                Set newSheet = ActiveDocument.CreateSheet()
                Set newProperties = newSheet.GetProperties
                newProperties.Name = sheetName
                newSheet.SetProperties(newProperties)
                'I think something should go here so that the macro loads the new sheets into memory or whatever.
           End If
      Next
      

       

      I think there should be a function to update the sheets for the macro, so it sees all of the sheets again and not only the ones that were there before starting the macro, but I can't find it.

       

      Alternatively, do you see any other possibility to do this?

        • How to see newly created sheets with GetSheet(j)?
          sarathi thangavel

          For i = 0 To unimportant.Count-1
               'Define the name of the sheet to check
               Select Case i
               Case 0
                    sheetName = "one"
               Case 1
                    sheetName = "two"
               Case 2
                    'It's the same name as in Case 0, so there shouldn't be any sheet created.
                    sheetName = "one"
               Case Else
                    sheetName = "three"
               End Select

               createNew = true

               For j = 0 To ActiveDocument.NoOfSheets-1
                    If (StrComp(ActiveDocument.GetSheet(j).GetProperties.Name, sheetName) = 0) Then
                         'This fires if there was a sheet with sheetName before starting the macro, but not if the sheet was created in the if statement later.
                         '(In this version it should fire when i = 2, but it doesn't, so a second sheet with name "one" is created.)
                         createNew = false
                    End If
               Next

               'This works fine and the newly created sheet is also shown correctly, but is not seen in the for-Statement above.
               If createNew Then
                    Set newSheet = ActiveDocument.CreateSheet()
                    Set newProperties = newSheet.GetProperties
                    newProperties.Name = "sheetName"
                    newSheet.SetProperties(newProperties)
                    'I think something should go here so that the macro loads the new sheets into memory or whatever.
               End If
          Next

           

          newProperties.Name = "sheetName"

          add Double quotes to add sheet name ...search sheet using name

            • Re: How to see newly created sheets with GetSheet(j)?

              sheetName is actualy a String variable, but I could try searching the sheet by the name instead of comparing Strings, you are right.

               

              EDIT: I did it a bit more different than intended before, but here is my solution:

               

              Set workSheet = Nothing                                           'You have to do this, otherwise workSheet will keep the previously found sheet,
                                                                                             'if there is no sheet found in the next statement.
              Set workSheet = ActiveDocument.GetSheet(sheetName)
              On Error Resume Next                                              'If you don't add this, the macro will raise an error with no message in the previous line.
              
              If (TypeName(workSheet) = "Nothing") Then                'Here's how you check, if GetSheet returned a sheet.
                  Set newSheet = ActiveDocument.CreateSheet()
                  Set newProperties = newSheet.GetProperties
                  newProperties.Name = sheetName
                  newSheet.SetProperties(newProperties)
              End If
              

               

              The funny thing is, if I print out the name of the newly created sheet, it will appear as the standard name it was created with ("Arbeitsblatt #" in German QV), but it is still found, if you put the name you have given it in GetSheet (as it is done in later iterations of the loop for example or later in the code for adding SheetObjects to it).