Delete a specific file in a folder when the filename contains a matching criteria.

Hello everyone,
i am having an issue with a word vba macro where i try to find a file that matches a criteria and delete that file
In short summary:
If i save a Word Document, a macro runs which then exports that document as a .PDF file.
The file recieves its filename from multiple variables. example: Prüfprotokoll_YUB2342_1_20210324
The last part of the filename is a date format (yyyymmdd).
This file gets created in the active documents folder and is then moved to a pdfExport folder.
If the file gets saved twice, the existing file will be deleted and the new file will takes its place.
The problem is, if the document gets saved on another day, i will get duplicate documents because of the date in the filename.
So what i am trying to do is to check if a file contains a matching string and determine this file as the already existing file to delete.

Here is a part of my actuall code which already results in a file not found, when i try to delete the file.

Code:

---

Dim fso As Object
Dim myPath As String, currentFolder As String, fileName As String, outputFileName As String
Dim oldPath As String, newPath As String, fileExt As string, thisFile As String, fileToDelete As String


'Create PDF-Export Folder if it does not exist.
exportFolder = ActiveDocument.Path & "\pdfExports"
Set fso = CreateObject("scripting.filesystemobject")
    If Not fso.FolderExists(exportFolder) Then
        fso.CreateFolder (exportFolder)
    End If


'Create PDF document from active document
myPath = ActiveDocument.FullName
currentFolder = ActiveDocument.Path
fileName = Mid(myPath, InStrRev(myPath, "\") + 5, InStrRev(myPath, ".") - InStrRev(myPath, "\") - 5)
ActiveDocument.ExportAsFixedFormat outputFileName:=currentFolder & "Prüfprotokoll_" & fileName & "_" & Format(Date, "yyyymmdd") & ".pdf", ExportFormat:=wdExportFormatPDF, _
OpenAfterExport:=False, OptimizeFor:=wdExportOptimizeForPrint, Range:= _
wdExportAllDocument, From:=1, To:=1, Item:=wdExportDocumentContent, _
IncludeDocProps:=True, KeepIRM:=True, CreateBookmarks:= _
wdExportCreateNoBookmarks, DocStructureTags:=True, BitmapMissingFonts:=True, UseISO19005_1:=False


'Move document from active document folder to export folder
oldPath = ActiveDocument.Path
newPath = exportFolder
fileExt = "*.pdf"
thisFile = exportFolder & Application.PathSeparator & "Prüfprotokoll_" & fileName & "_" & Format(Date, "yyyymmdd") & ".pdf"


If Not InStr(thisFile, fileName) <> 0 Then
    fso.MoveFile Source:=oldPath & fileExt, Destination:=newPath
Else
    fileToDelete = Dir(exportFolder & Application.PathSeparator & "Prüfprotokoll_"  fileName & "*" & ".pdf")
    fso.DeleteFile (fileToDelete) 'This results in file not found.
    fso.MoveFile Source:=oldPath & fileExt, Destination:=newPath
End If

---

I can not directly compare "thisFile" variable and check if it exists, because this will only delete the file if the date matches.
I struggle to find a way to check the PDF Documents for just there "fileName" variable (example: YUB2342_1) and then delete the matching file regardless of their date in the filename.
Hopefully i explained the subject well enough.

Does anyone have an idea on how i could resolve this problem?

Greetings
Manuel

If I understand the requirement correctly i.e. save as PDF in a folder, while deleting any similarly named PDF file with a different date string in that folder, then try the following:

Code:

---

Sub Macro1()
'Graham Mayor - https://www.gmayor.com - Last updated - 24 Mar 2021
Dim fso As Object
Dim exportFolder As String, sFile As String, sFilename As String
Dim bExists As Boolean
    'Create PDF-Export Folder if it does not exist.
    exportFolder = ActiveDocument.path & "\pdfExports\"
    Set fso = CreateObject("scripting.filesystemobject")
    If Not fso.FolderExists(exportFolder) Then
        fso.CreateFolder exportFolder
    End If


    sFilename = Left(ActiveDocument.Name, InStrRev(ActiveDocument.Name, Chr(46)) - 1)


    sFile = Dir$(exportFolder & "Prüfprotokoll_" & sFilename & "_" & "*.pdf")
    While sFile <> ""
        fso.DeleteFile exportFolder & sFile
        bExists = True
        sFile = Dir$()
    Wend


    ActiveDocument.ExportAsFixedFormat outputFileName:=exportFolder & "Prüfprotokoll_" & sFilename & "_" & Format(Date, "yyyymmdd") & ".pdf", _
                                      ExportFormat:=wdExportFormatPDF, _
                                      OpenAfterExport:=False, OptimizeFor:=wdExportOptimizeForPrint, Range:= _
                                      wdExportAllDocument, From:=1, To:=1, Item:=wdExportDocumentContent, _
                                      IncludeDocProps:=True, KeepIRM:=True, CreateBookmarks:= _
                                      wdExportCreateNoBookmarks, DocStructureTags:=True, BitmapMissingFonts:=True, UseISO19005_1:=False
    Set fso = Nothing
End Sub

---

Since there's only supposed to be one version of each file in the folder, one wonders why you're including the date as part of the filename - much easier to manage without it...

@gmayor - Thanks for your assistance. With your code, i got it working the way i wanted after a while.

@macropod - Yes you are right, that would have been much easier. I also had that part working already, but the date in the filename became a requirement, so i had to adapt.

Thanks for your replies.

I believe you could replace:

Code:

---

    sFile = Dir$(exportFolder & "Prüfprotokoll_" & sFilename & "_" & "*.pdf")    While sFile <> ""
        fso.DeleteFile exportFolder & sFile
        bExists = True
        sFile = Dir$()
    Wend

---

with:

Code:

---

    Kill exportFolder & "Prüfprotokoll_" & sFilename & "_" & "*.pdf"

---

I'd also be inclined to replace:

Code:

---

    ActiveDocument.ExportAsFixedFormat outputFileName:=exportFolder & "Prüfprotokoll_" & sFilename & "_" & Format(Date, "yyyymmdd") & ".pdf", _                                      ExportFormat:=wdExportFormatPDF, _
                                      OpenAfterExport:=False, OptimizeFor:=wdExportOptimizeForPrint, Range:= _
                                      wdExportAllDocument, From:=1, To:=1, Item:=wdExportDocumentContent, _
                                      IncludeDocProps:=True, KeepIRM:=True, CreateBookmarks:= _
                                      wdExportCreateNoBookmarks, DocStructureTags:=True, BitmapMissingFonts:=True, UseISO19005_1:=False

---

with:

Code:

---

    ActiveDocument.SaveAs FileName:=exportFolder & "Prüfprotokoll_" & sFilename & "_" & Format(Date, "yyyymmdd") & ".pdf", _
        FileFormat:=wdFormatPDF, AddToRecentFiles:=False

---

My solution now looks like this, it is working, so i am happy with it. =)

Creating the export folder:

Code:

---

exportFolder = currentFolder & "pdfExports\"
Set fso = CreateObject("scripting.filesystemobject")
      If Not fso.FolderExists(exportFolder) Then
        fso.CreateFolder (exportFolder)
      End If
timeStamp = Format(Now, "yyyymmdd")

---

Reducing the filename to the sought-after string.

Code:

---

myPath = dd1.FullName
fileName = Mid(myPath, InStrRev(myPath, "\") + 5, InStrRev(myPath, ".") - InStrRev(myPath, "\") - 5)
strFilename = currentFolder & "Prüfprotokoll_" & fileName & "_" & timeStamp & ".pdf"
exportFilename = exportFolder & "Prüfprotokoll_" & fileName & "_" & timeStamp & ".pdf"

---

Creating the PDF

Code:

---

ActiveDocument.SaveAs fileName:=strFilename, FileFormat:=wdFormatPDF, AddToRecentFiles:=False

---

Moving the PDF to the PDF Folder

Code:

---

existingFile = exportFolder & "Prüfprotokoll_" & fileName & "_" & "*" & ".pdf"


testString = Dir(existingFile)
    If testString = "" Then
        FileSystem.FileCopy strFilename, exportFilename
        FileSystem.Kill strFilename
    Else
        FileSystem.Kill exportFolder & "*" & fileName & "*"
        FileSystem.FileCopy strFilename, exportFilename
        FileSystem.Kill strFilename
    End If

---

Thanks again for the assistance.