Using VBA to move PDFs with specifications

[johnny51981]

Howdy!

My team receives a bunch of PDF file work orders by way of email, they are saved to a centralized network file location.

I currently use a pair of Excel Power Query workbooks to read all of the saved PDF files and put data in their specific columns (in one), create a complete and clean list (in the other)...boom..I'm off on my way. However, our IT Department has made some recent changes to our servers that have slowed down the speed at which the Power Query is able to read all of the PDF files.

What I am looking to do is to move the files to another folder once I've plucked all the necessary data from them. I have the following table (Table Name: NTPs_to_Move) that I would like to use as reference in some VBA that will:
1) Rename the Current File Name to the New File Name
2) Move the PDF (with the New File Name) from the Current Location to the New File Location
3) Rename the New File Name IF the New File Location already has a file with that name with the a (1) and (2) and so on sequentially. If that is not possible, the "Date last saved" date at the end of the file would also be a perfect solution.

That way I can just run the first workbook on the newer files and then tac them onto the end of my most recent complete and clean list....incremental adds rather than rewriting the whole thing each time.

Any help would be greatly appreciated.

Note: The file locations listed in the table currently point to my desktop for development and testing purposes.

 

[John_w]

Try this macro, though it doesn't do step 1) because it does the rename as part of step 2). Looks for the table on the active sheet.

Public Sub Move_Files_In_Table()

    Dim table As ListObject
    Dim r As Long
    Dim currentFile As String, newFile As String
    Dim num As Long
    
    Set table = ActiveSheet.ListObjects("NTPs_to_Move")
    
    With table
        For r = 1 To .DataBodyRange.Rows.Count
            currentFile = .DataBodyRange.Item(r, 2) & .DataBodyRange.Item(r, 1)
            newFile = .DataBodyRange.Item(r, 4) & .DataBodyRange.Item(r, 3)
            If Dir(currentFile) <> vbNullString Then
                If Dir(newFile) = vbNullString Then
                    Name currentFile As newFile
                Else
                    num = 1
                    While Dir(Replace(newFile, ".pdf", " (" & num & ").pdf", Compare:=vbTextCompare)) <> vbNullString
                        num = num + 1
                    Wend
                    newFile = Replace(newFile, ".pdf", " (" & num & ").pdf", Compare:=vbTextCompare)
                    Name currentFile As newFile
                End If
            End If
        Next
    End With

End Sub

 

[johnny51981]

Absolute perfection! Thank you SO much, @John_w