none
check for a sheet in a workbook excel. RRS feed

  • Question

  • Hi all, I have a little doubt: 
    I have an array of string in which I keep the names of the worksheets in a workbook excel, then delete some leaves and then performed some calculations using the array of strings, but in order to make these calculations need to know if the sheet exists, ie : 

    arre (0) = "one" 
    arre (1) = "two" 
    arre (2) "three" 
    arre (3) = "Four" 

    I need to do is like the following: 
    if sheets (arre (i)). exist  then
    ....... 
    else 
    ......... 
    end if 

    I hope I explained and you can help me, greetings


    • Edited by aslf010990 Thursday, October 16, 2014 5:18 PM
    Thursday, October 16, 2014 5:15 PM

Answers

  • Sub Main()
        
        Dim arr(2)
        arr(0) = "Sheet1"
        arr(1) = "Sheet2"
        arr(2) = "nameThatWillNotExists"
    
        Dim shName As Variant
        Dim sh As Worksheet
        
        Dim shExists As Boolean
        
        For Each shName In arr
            Debug.Print shName
            For Each sh In Sheets
                If StrComp(shName, sh.Name, vbTextCompare) = 0 Then
                    shExists = True
                    Exit For
                End If
            Next
            If Not shExists Then
                MsgBox shName & " does not exist"
            End If
            shExists = False
        Next
    
    End Sub
    

    • Marked as answer by aslf010990 Thursday, October 16, 2014 5:45 PM
    Thursday, October 16, 2014 5:29 PM

All replies

  • Sub Main()
        
        Dim arr(2)
        arr(0) = "Sheet1"
        arr(1) = "Sheet2"
        arr(2) = "nameThatWillNotExists"
    
        Dim shName As Variant
        Dim sh As Worksheet
        
        Dim shExists As Boolean
        
        For Each shName In arr
            Debug.Print shName
            For Each sh In Sheets
                If StrComp(shName, sh.Name, vbTextCompare) = 0 Then
                    shExists = True
                    Exit For
                End If
            Next
            If Not shExists Then
                MsgBox shName & " does not exist"
            End If
            shExists = False
        Next
    
    End Sub
    

    • Marked as answer by aslf010990 Thursday, October 16, 2014 5:45 PM
    Thursday, October 16, 2014 5:29 PM
  • thank you very much, my problem was solved, Greetings
    Thursday, October 16, 2014 5:45 PM