locked
Access a sheet from add in RRS feed

  • Question

  • Hi

    I have a workbook that calls a module in an add-in.  Within the add-in I want to access a sheet  in the calling workbook and make some changes to that sheet e.g. delete some entries.

    I would prefer to access the sheet using the code name (e.g. Sheet18) rather than the actual sheet name.

    the type of code I would execute is like this. I think I need a qualifier in front to the Sheet18 but I am not sure what this should be.

    Sheet18.Rows("6:104").Select
    Selection.Delete Shift:=xlUp

    To add to this issue,  I will have 2 or more workbooks (with different names) but both with a sheet18 which will call the addin. So I cannot use the workbook name as a qualifier as this might change depending on which workbook is caling the add-in.

    I hope all this makes sense!

    can anyone help please? 

    thank you!

    Wednesday, March 30, 2016 9:15 AM

Answers

  • I would prefer to access the sheet using the code name (e.g. Sheet18) rather than the actual sheet name.

    Sheet18.Rows("6:104").Select

    Selection.Delete Shift:=xlUp

    To add to this issue,  I will have 2 or more workbooks (with different names) but both with a sheet18 which will call the addin. So I cannot use the workbook name as a qualifier as this might change depending on which workbook is caling the add-in.

    You should never use a hard-coded reference, never the code name nor something like Sheets("blablabla"), you'll get an error if the sheet doesn't exists!

    You should never use SELECT and SELECTION, when the sheet is not active you'll get a RTE!

    It is possible to modify more then one workbook at a time, but most users expect that the AddIn make changes in the active workbook only!

    Anyway, whatever you do: Refer to the objects! After that it doesn't matter how you call the dog.

    Andreas.

    Option Explicit
    
    Sub Test()
      Dim Wb As Workbook
      Dim Ws As Worksheet
      
      'At first, refer to the workbook that you prefer, there are 3 different ways:
      Set Wb = ActiveWorkbook
      Set Wb = Workbooks(1)
      Set Wb = Workbooks("PERSONAL.XLSB")
      
      'The last 2 can produce an error, because no workbook might be open, resp. the user might not have a PERSONAL.XLSB
      'So I recommend to use a function to refer to a special workbook:
      Set Wb = GetWorkBook("Whatever")
      
      If Wb Is Nothing Then
        MsgBox "Workbook not found"
        Exit Sub
      End If
      
      'The usual way is to use the name of the sheet and you can check if this sheet exists:
      If Not WorksheetExists("Whatever", Wb) Then
        MsgBox "Sheet not found"
        Exit Sub
      Else
        Set Ws = Wb.Worksheets("Whatever")
      End If
      
      'It is also possible to search in the workbook for a code name:
      Set Ws = GetCodenameSheet("Sheet18", Wb)
      If Ws Is Nothing Then
        MsgBox "Sheet not found"
        Exit Sub
      End If
      
      'Finally delete the rows
      Ws.Rows("6:104").Delete
    End Sub
    
    Private Function WorksheetExists(ByVal SheetNameOrIndex As Variant, _
        Optional ByVal Wb As Workbook = Nothing) As Boolean
      'True if worksheet SheetNameOrIndex exists
      On Error Resume Next
      If Wb Is Nothing Then Set Wb = ActiveWorkbook
      WorksheetExists = Not Wb.Worksheets(SheetNameOrIndex) Is Nothing
    End Function
    
    Private Function SheetExists(ByVal SheetNameOrIndex As Variant, _
        Optional ByVal Wb As Workbook = Nothing) As Boolean
      'True if sheet SheetNameOrIndex exists
      On Error Resume Next
      If Wb Is Nothing Then Set Wb = ActiveWorkbook
      SheetExists = Not Wb.Sheets(SheetNameOrIndex) Is Nothing
    End Function
    
    Private Function GetCodenameSheet(ByVal Codename As String, _
        Optional ByVal Wb As Workbook = Nothing) As Object
      'Return the sheet that has this codename
      Dim S As Object
      If Wb Is Nothing Then Set Wb = ActiveWorkbook
      For Each S In Wb.Sheets
        If StrComp(Codename, S.Codename, vbTextCompare) = 0 Then
          Set GetCodenameSheet = S
          Exit Function
        End If
      Next
    End Function
    
    Private Function GetWorkBook(ByVal WorkBookName As String) As Workbook
      'Return the workbook that name is like WorkBookName, Nothing if not open
      Dim fso As Object 'FileSystemObject
      Set fso = CreateObject("Scripting.FileSystemObject")
      'Path given?
      If Len(fso.GetParentFolderName(WorkBookName)) > 0 Then
        'Compare the full path of each open workbook
        For Each GetWorkBook In Workbooks
          If StrComp(GetWorkBook.FullName, WorkBookName, vbTextCompare) = 0 Then
            Exit Function
          End If
        Next
      ElseIf InStrRev(WorkBookName, ".") > 0 Then
        'We must exact match if an extension is given
        On Error GoTo ExitPoint
        Set GetWorkBook = Workbooks(WorkBookName)
      Else
        'Without an extension it can be a new file too
        On Error GoTo SearchIt
        Set GetWorkBook = Workbooks(WorkBookName)
        Exit Function
    SearchIt:
        On Error GoTo ExitPoint
        If (InStr(WorkBookName, "?") > 0) Or (InStr(WorkBookName, "*") > 0) Then
          For Each GetWorkBook In Workbooks
            If fso.GetBaseName(GetWorkBook.Name) Like WorkBookName Then
              Exit Function
            End If
          Next
        Else
          For Each GetWorkBook In Workbooks
            If StrComp(fso.GetBaseName(GetWorkBook.Name), WorkBookName, vbTextCompare) = 0 Then
              Exit Function
            End If
          Next
        End If
      End If
    ExitPoint:
    End Function
    

    • Marked as answer by py1 Wednesday, March 30, 2016 2:12 PM
    Wednesday, March 30, 2016 10:30 AM

All replies

  • I would prefer to access the sheet using the code name (e.g. Sheet18) rather than the actual sheet name.

    Sheet18.Rows("6:104").Select

    Selection.Delete Shift:=xlUp

    To add to this issue,  I will have 2 or more workbooks (with different names) but both with a sheet18 which will call the addin. So I cannot use the workbook name as a qualifier as this might change depending on which workbook is caling the add-in.

    You should never use a hard-coded reference, never the code name nor something like Sheets("blablabla"), you'll get an error if the sheet doesn't exists!

    You should never use SELECT and SELECTION, when the sheet is not active you'll get a RTE!

    It is possible to modify more then one workbook at a time, but most users expect that the AddIn make changes in the active workbook only!

    Anyway, whatever you do: Refer to the objects! After that it doesn't matter how you call the dog.

    Andreas.

    Option Explicit
    
    Sub Test()
      Dim Wb As Workbook
      Dim Ws As Worksheet
      
      'At first, refer to the workbook that you prefer, there are 3 different ways:
      Set Wb = ActiveWorkbook
      Set Wb = Workbooks(1)
      Set Wb = Workbooks("PERSONAL.XLSB")
      
      'The last 2 can produce an error, because no workbook might be open, resp. the user might not have a PERSONAL.XLSB
      'So I recommend to use a function to refer to a special workbook:
      Set Wb = GetWorkBook("Whatever")
      
      If Wb Is Nothing Then
        MsgBox "Workbook not found"
        Exit Sub
      End If
      
      'The usual way is to use the name of the sheet and you can check if this sheet exists:
      If Not WorksheetExists("Whatever", Wb) Then
        MsgBox "Sheet not found"
        Exit Sub
      Else
        Set Ws = Wb.Worksheets("Whatever")
      End If
      
      'It is also possible to search in the workbook for a code name:
      Set Ws = GetCodenameSheet("Sheet18", Wb)
      If Ws Is Nothing Then
        MsgBox "Sheet not found"
        Exit Sub
      End If
      
      'Finally delete the rows
      Ws.Rows("6:104").Delete
    End Sub
    
    Private Function WorksheetExists(ByVal SheetNameOrIndex As Variant, _
        Optional ByVal Wb As Workbook = Nothing) As Boolean
      'True if worksheet SheetNameOrIndex exists
      On Error Resume Next
      If Wb Is Nothing Then Set Wb = ActiveWorkbook
      WorksheetExists = Not Wb.Worksheets(SheetNameOrIndex) Is Nothing
    End Function
    
    Private Function SheetExists(ByVal SheetNameOrIndex As Variant, _
        Optional ByVal Wb As Workbook = Nothing) As Boolean
      'True if sheet SheetNameOrIndex exists
      On Error Resume Next
      If Wb Is Nothing Then Set Wb = ActiveWorkbook
      SheetExists = Not Wb.Sheets(SheetNameOrIndex) Is Nothing
    End Function
    
    Private Function GetCodenameSheet(ByVal Codename As String, _
        Optional ByVal Wb As Workbook = Nothing) As Object
      'Return the sheet that has this codename
      Dim S As Object
      If Wb Is Nothing Then Set Wb = ActiveWorkbook
      For Each S In Wb.Sheets
        If StrComp(Codename, S.Codename, vbTextCompare) = 0 Then
          Set GetCodenameSheet = S
          Exit Function
        End If
      Next
    End Function
    
    Private Function GetWorkBook(ByVal WorkBookName As String) As Workbook
      'Return the workbook that name is like WorkBookName, Nothing if not open
      Dim fso As Object 'FileSystemObject
      Set fso = CreateObject("Scripting.FileSystemObject")
      'Path given?
      If Len(fso.GetParentFolderName(WorkBookName)) > 0 Then
        'Compare the full path of each open workbook
        For Each GetWorkBook In Workbooks
          If StrComp(GetWorkBook.FullName, WorkBookName, vbTextCompare) = 0 Then
            Exit Function
          End If
        Next
      ElseIf InStrRev(WorkBookName, ".") > 0 Then
        'We must exact match if an extension is given
        On Error GoTo ExitPoint
        Set GetWorkBook = Workbooks(WorkBookName)
      Else
        'Without an extension it can be a new file too
        On Error GoTo SearchIt
        Set GetWorkBook = Workbooks(WorkBookName)
        Exit Function
    SearchIt:
        On Error GoTo ExitPoint
        If (InStr(WorkBookName, "?") > 0) Or (InStr(WorkBookName, "*") > 0) Then
          For Each GetWorkBook In Workbooks
            If fso.GetBaseName(GetWorkBook.Name) Like WorkBookName Then
              Exit Function
            End If
          Next
        Else
          For Each GetWorkBook In Workbooks
            If StrComp(fso.GetBaseName(GetWorkBook.Name), WorkBookName, vbTextCompare) = 0 Then
              Exit Function
            End If
          Next
        End If
      End If
    ExitPoint:
    End Function
    

    • Marked as answer by py1 Wednesday, March 30, 2016 2:12 PM
    Wednesday, March 30, 2016 10:30 AM
  • thanks for the feedback Andreas.
    Wednesday, March 30, 2016 2:12 PM