Consulting

Results 1 to 6 of 6

Thread: VBA code to determine if a specific excel-based tool is used as add-in and locked?

  1. #1

    VBA code to determine if a specific excel-based tool is used as add-in and locked?

    Hello to the group members!

    Can someone help me with VBA code for determining if an excel file has 'specific excel-based add-ins' (i have a list of specific add-ins) loaded and password protected? Any help is appreciated.

    Note: I have the below code to determine if macros stored under VBAProject are locked or not. But this code is not able to determine if there are add-ins that are locked (as these add-ins are not stored under VBAProject but just outside of VBAProject).

    --------------
    
    Sub wsht_macro_protect(Ana_Name, sht_Count, pro_Prot, sht_Prot_cnt, sht_Prot)
      Dim sh As Worksheet
      Dim wb As Workbook
      
      Application.Calculation = xlCalculationManual
      Application.ScreenUpdating = False
      Application.DisplayAlerts = False
      Application.AskToUpdateLinks = False
    
    
      Set wb = Workbooks(Ana_Name) 'Input workbook name here or use do ...loop
      'Initialization
      sht_Count = 0
      sht_Prot_cnt = 0
      sht_Prot = ""
      pro_Prot = ""
      
      wb.Activate
      sht_Count = Sheets.Count
      
      If wb.VBProject.Protection = vbext_pp_locked = True Then
        pro_Prot = "Protected"
    
    
      End If
      
      For Each sh In wb.Worksheets
          If sh.ProtectContents = True Then
            sht_Prot_cnt = sht_Prot_cnt + 1
            sht_Prot = "Protected"
    
    
        End If
        
      Next sh
      
      Application.Calculation = xlCalculationAutomatic
      Application.ScreenUpdating = True
      Application.DisplayAlerts = True
      Application.AskToUpdateLinks = True
      
    End Sub
    
    ------------------
    Any help is appreciated.

    Thank you
    Ram.
    Last edited by xld; 04-22-2019 at 03:22 PM. Reason: Added code tags

  2. #2
    Knowledge Base Approver VBAX Guru
    Joined
    Apr 2012
    Posts
    4,461
    And when you have got this inventory, what's next ?

  3. #3
    Knowledge Base Approver VBAX Guru
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    4,440
    You may be able to compare some of the properties with your list, eg.:
    Sub blah()
    Set yyy = Application.AddIns
    For i = 1 To yyy.Count
      Debug.Print yyy(i).Title, yyy(i).Name, yyy(i).Installed
    Next i
    End Sub
    You'd probably have to open the file first though.
    p45cal - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
    If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  4. #4
    Thank you, p45cal.

    yyy = application.AddIns is pulling only *.XLAM type of add-ins only.

    Is there any command to capture *.xla type of add-ins also?

    Thank you, Ram.

  5. #5
    snb, thank you for the message. Just need to list the add-ins used in files. Thank you.

  6. #6
    Knowledge Base Approver VBAX Guru
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    4,440
    try changing:
    Set yyy = Application.AddIns
    to:
    Set yyy = Application.AddIns2
    (Excel 2010 and later)
    p45cal - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
    If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •