Rename PDF files with VBA

[dhubz]

Hello,
I have a folder containing email attachments, but they are all labelled as REF XXXXXXXX.PDF I have used some software to generate an excel file with email data, one column is the name of the attachment, the other is the subject. I know there is a way to do it through VBA, as I've seen numerous renaming posts, I just can't seem to modify and make it work for the info I have.

All the attachments are in one folder "C:\source files\", I am fine if they overwrite within the same folder or output to a different folder. The new name needs to be a combination of the Sender and the Subject. If it's easier to do as an direct swap.

Attachment Name	Sender	Subject
REF 1785988.pdf	John Doe	Task 1 - Date - REF 1785988.pdf
REF 1785999.pdf	Steve Smith	Task 5 - Date - REF 1785999.pdf
REF 1786210.pdf	Jane Doe	Task 9 - Date - REF 1786210.pdf

I could easily convert the info I have into 2 columns, an input path name, and an output path name if that make the programming easier

Input Files	Output Files
C:\Source Files\REF 1785988.pdf	C:\Output Files\John Doe Task 1 - Date - REF1785988.pdf
C:\Source Files\REF 1785999.pdf	C:\Output Files\Steve Smith Task 5 - Date - REF1785999.pdf

 

[Dan_W]

I know there is a way to do it through VBA, as I've seen numerous renaming posts, I just can't seem to modify and make it work for the info I have.

Ok. What code do you have so far? There doesn't seem to be anything particular about your data/requirements that any of the solutions in the posts on this forum or findable through Google can handle, so maybe it's something quickly fixable in a version of the code you've tried?

 

[dhubz]

I've tried this, I've modified my data to match, and all files are in the same folder as the worksheet. I am working with 4 sample files as a test of the function, the sample files match the names in the table. There are no headers in the table data.

I get a Complile Error- Invalid use of New keyword, it highlights FS As New Filesearch

Sub Test()
  Dim Path As String
  Dim R As Range
  Dim FS As New FileSearch
  Dim i As Long
  Dim OldName As String, NewName As String
  
  'Get the path from this file
  Path = ThisWorkbook.Path
  If Right(Path, 1) <> "\" Then Path = Path & "\"
  
  With FS
    'Search here
    .LookIn = Path
    'for PDF files
    .Filename = "*.pdf"
    'Do it
    .Execute msoSortByFileName, msoSortOrderAscending
    'For each cell in column A
    For Each R In Range("A1", Range("A" & Rows.Count).End(xlUp))
      i = i + 1
      'Rename the file
      OldName = .FoundFiles(i)
      NewName = Path & R.Value & ".pdf"
      Name OldName As NewName
    Next
  End With
End Sub

 

[Dan_W]

Ahh - well - I'm not sure exactly when it was deprecated, but the FileSearch method has not available in Excel for a long time. What version of Excel of are using, out of curiosity? I don't see that info in your profile details.

But whatever the case, from what I can tell, you don't need to search for the files - you already know the file path. All you really need to do:

(1) work out the path/name of current file - you know this already, so ... done
(2) come up with the target filename - which you said you can easily do, so ... done(?)
(3) use the VBA Name command to the change the filename.

For example:

Name C:\TEMP\OriginalFilename.PDF As C:\Temp\NewFilename.PDF

Is that broadly in line with what you're wanting to accomplish?

 

[dhubz]

Ahh - well - I'm not sure exactly when it was deprecated, but the FileSearch method has not available in Excel for a long time. What version of Excel of are using, out of curiosity? I don't see that info in your profile details.

But whatever the case, from what I can tell, you don't need to search for the files - you already know the file path. All you really need to do:

(1) work out the path/name of current file - you know this already, so ... done
(2) come up with the target filename - which you said you can easily do, so ... done(?)
(3) use the VBA Name command to the change the filename.

For example:

Name C:\TEMP\OriginalFilename.PDF As C:\Temp\NewFilename.PDF

Is that broadly in line with what you're wanting to accomplish?

Sorry for the delay, I had a few days off. Yes, this is what I am looking for, I'm usually OK at reading and copy/paste and modifying existing code. Not so good at building new code from scratch. I understand the Name function will take care of the rename, but how do I set the source input range, and then the 2nd column as the output.

 

[Dan_W]

Fortunately, you have all the code you need, it just takes a little adjustment. This is a very basic method of accomplishing the above:

Sub BatchRename()
    Dim WS As Worksheet, OldName As String, NewName As String
    Dim SheetName As String, TargetCell As Range, OutputColumn As Long
    SheetName = "WhatIsYourSheetName"
    Set WS = ThisWorkbook.Sheets(SheetName)
    OutputColumn = 2
    For Each TargetCell In WS.Range("A1", WS.Range("A" & WS.Rows.Count).End(xlUp))
        OldName = TargetCell.value
        NewName = WS.Cells(TargetCell.Row, OutputColumn).value
        If OldName = vbNullString Or NewName = vbNullString Then Exit For
        Name OldName As NewName
    Next
End Sub

Basically, it assumes that you have organised the oldfilenames and new filenames as you give in the final example above - the old filenames are in Column A, and you can then set the new filenames to whichever column you prefer by adjusting the OutPutColumn variable in the code. It is currently set to 2, meaning that it will try and get the new filename from the same row as the old filename, column B (the 2nd column).

I've also added a variable for you to add the sheet name - it's best to qualify which worksheet VBA needs to be looking at. This is one way of doing that. Finally, I added an autoescape for the code in case VBA encounters an empty value in either the old filename or the new filename cells.

Is that what you were after?