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

[illogic]

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.



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

[gmayor]

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:


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

[macropod]

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...

[illogic]

@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.

[macropod]

I believe you could replace:

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

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

I'd also be inclined to replace:

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:

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

[illogic]

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

Creating the export folder:


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.


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


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


Moving the PDF to the PDF Folder


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.