Consulting

Results 1 to 6 of 6

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

  1. #1
    VBAX Regular
    Joined
    Jun 2016
    Posts
    49
    Location

    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.

    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

  2. #2
    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
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  3. #3
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,288
    Location
    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...
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  4. #4
    VBAX Regular
    Joined
    Jun 2016
    Posts
    49
    Location
    @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.

  5. #5
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,288
    Location
    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
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  6. #6
    VBAX Regular
    Joined
    Jun 2016
    Posts
    49
    Location
    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.

Posting Permissions

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