Delete PDF Files from Workbook Folder Based on File Name Length

[rickincanada]

I'm attempting to create a macro that runs during the Workbook_Open event which deletes all the PDF files from the directory the workbook is in where the file name has 53 characters before the ".PDF". Any help you could provide me with would be greatly appreciated!

 

[Mike Blackman]

Hi Rick,

Something like this?

    Dim objFSO As New FileSystemObject
    Dim fldTarget As Folder
    Dim fle As File
    
    '//// Ensure you set reference to Microsoft Scripting Runtime
    
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set fldTarget = objFSO.GetFolder(ThisWorkbook.Path)
    
    For Each fle In fldTarget.Files
        With fle
            If Len(.Name) = 57 And LCase(Right$(.Name, 3)) = "pdf" Then .Delete
        End With
    Next fle

 

[rickincanada]

Hi Mike - thanks for your help! The only problem with this is that I have over 100 different workbook users therefore ensuring that Microsoft Scripting Runtime is referenced is not possible. Is there anyway to automate this?

 

[Mike Blackman]

The Reference to Microsoft Scripting Runtime is made in the workbook you initiate this code from, once you've done it in the file in question it'll be done for everybody

 

[rickincanada]

Excellent! It works like a charm - thanks so much!!