Check if Spreadsheets in a Folder are Signed

[ashaneharris]

[FONT=&quot]I do not know if this is possible but basically I would like to have the spreadsheet check a folder, then return excel file names and whether the document is signed. I've tried a few things that don't seem to work.[/FONT]
[FONT=&quot]
[/FONT]
[FONT=&quot]First I wrote this code to list all files within the folder:

[COLOR=#222222][FONT=&quot]Sub Test()[/FONT][/COLOR][/FONT][/COLOR]
[FONT=&quot]'Declared variables as objectsDim objFSO As ObjectDim objFolder As ObjectDim objFile As ObjectDim i As Integer'Create an instance of the FileSystemObjectSet objFSO = CreateObject("Scripting.FileSystemObject")'Get the folder object, declare the filepath to the folderSet objFolder = objFSO.GetFolder("G:\Cash Management\Accounts Payable\Vendor Statement Recons\FY19\Q3\January\Approved")i = 1'Loops through each file in the directory and prints their names and pathFor Each objFile In objFolder.Files'Print file nameCells(i + 1, 1) = objFile.Namei = i + 1Next objFile [/FONT][COLOR=#D7DADC][FONT=&quot][COLOR=#222222][FONT=&quot]End Sub[/FONT][/COLOR]

Now I need a way for the macro to go through and mark whether or not it was signed with a digital signature. All I have managed to think of is this but I don't know who to accurately have this test in the loop as well or have it tell me if they are signed:

[COLOR=#222222][FONT=&quot]If Sheet1.Signatures.Count = 0 Then[/FONT][/COLOR][/FONT][/COLOR]
[FONT=&quot]                                Sheet1.CommandButton1.Visible = True                 Else                                Sheet1.CommandButton1.Visible = False [/FONT][COLOR=#D7DADC][FONT=&quot][COLOR=#222222][FONT=&quot]                End If[/FONT][/COLOR]

[/FONT]

 

[gallen]

Hello,

Try this. I've quickly thrown it together and couldn't see a way of checking without opening the file.

Might point you in the right direction:


It assumes much, such as where it is going etc... will need a little tweaking by you

Sub CheckSigned()

    'sub is to be in same sheet as results to be written
    Dim sPath As String 'path of folder to check
    Dim sName As String 'name of file
    Dim wb As Workbook
    Dim nr As Long 'next row to write information
    
    nr = Range("A" & Rows.Count).End(xlUp).Row + 1
    
    sPath = "C:\Test\"
    
    sName = Dir(sPath)
    'disable stuff
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.DisplayAlerts = False
    
    'loop through folder
    Do While sName <> ""
        On Error Resume Next
        Set wb = Workbooks.Open(Filename:=sPath & sName, ReadOnly:=True, UpdateLinks:=False)
        On Error GoTo errHandle
        If Not wb Is Nothing Then
            Range("A" & nr) = sName
            Range("B" & nr) = IIf(wb.VBASigned, "Signed", "Not Signed")
            wb.Close
            nr = nr + 1
        End If
        sName = Dir
    Loop


errHandle:
    If Err.Number <> 0 Then MsgBox Err.Description
    'reenable stuff
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Application.DisplayAlerts = True
End Sub