200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Hi There
I have a list of PDF files in a folder on my desktop. I am trying to source/create a macro which i am told is available.
Can anyone provide a simple step by step way of doing this please. I am new to VBA and Macros but trying to learn attached is a file with old and new names
VBA and/or macro for what?
Are you going to tell us what you're trying to do with the PDF files?
Please try the code below in place of what you have programmed yourself so far.
Option Explicit
Private Enum Nws ' worksheet navigation
' 06 Nov 2018 ' modify as required
NwsFirstDataRow = 2
NwsOldName = 1 ' 1 = column A
NwsNewName = 2
End Enum
Sub RenameFiles()
' 06 Nov 2018
Dim PathName As String
Dim Ws As Worksheet
Dim OldName As String, NewName As String
Dim Tmp As String
Dim Rl As Long ' last used row
Dim R As Long
Tmp = "pdf" ' Folder name: change as appropriate
PathName = Environ("USERPROFILE") & "\Desktop\" & Tmp & "\"
Set Ws = ThisWorkbook.Worksheets("PDF") ' change as required
With Ws
' determine the last used row in the OldName column
Rl = .Cells(.Rows.Count, NwsOldName).End(xlUp).Row
' loop through all the OldNames in Ws
For R = NwsFirstDataRow To Rl
OldName = .Cells(R, NwsOldName).Value
NewName = .Cells(R, NwsNewName).Value
If Len(NewName) Then ' skip if no NewName
Tmp = PathName & OldName & ".pdf"
If Len(Tmp) Then ' skip if OldName doesn't exist
Name Tmp As PathName & NewName & ".pdf"
End If
End If
Next R
End With
End SubThe enumeration at the top of the code is designd to allow you to make changes easily. You may change the first data row or the location of the two columns. You can change the folder name and its location. Set Ws = ThisWorkbook.Worksheets("PDF") determines that the file names are in the same workbook as the code in a worksheet called "PDF". Avoid becoming confused by naming everything "PDF" and then forgetting that pdf files also have an extension ".pdf". More invention will prevent confusion.
The code will crash if there are invalid characters in the new file name. However, since it seems that you only want to remove the date from the existing file names the easier approach to the task might have been to loop through all files in the folder and remove the dates from their names.