Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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?

1 Solution

Accepted Solutions
Not applicable
Author

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).

View solution in original post

2 Replies
Anonymous
Not applicable
Author

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

Not applicable
Author

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).