PDF Files


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  



Selected Answer

VBA and/or macro for what?

Are you going to tell us what you're trying to do with the PDF files?



My apologies i am trying to get the names changed on the PDF list as a batch using excel
Bosh (rep: 6) Nov 7, '18 at 4:38 am
Add to Discussion

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 Sub

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



Hi there thanks for replying to me and for your assistance but as metioned in another comment i was not really clear enough.  What i am trying to do is take a list of files (PDF) and change them to another name which ever means possible.  I have tried many excel processes but to no avail.  There may even be a program that can be bought but i have not found one for Mac yet.

Thanks again
Bosh (rep: 6) Nov 8, '18 at 1:13 am
The code I have posted does exactly what you describe. Have you tried it?
Variatus (rep: 2523) Nov 8, '18 at 2:32 am
Yes i have tried a couple of times maybe i am entering wrong info i will try again today and let you know... Thanks again
Bosh (rep: 6) Nov 8, '18 at 8:43 am
It still does not work for me but i think it is because i am too new at VBA and Macros.  I will lhave to do some more work on it before i attempt again.  Thanks again for your help on this .
Bosh (rep: 6) Nov 9, '18 at 7:46 am
For the code to work without any change it must be installed in the module Module1 of the workbook you published. There shall not be any procedures in that module before the Enum Nws. There must be a folder named "PDF" on your Desktop. There must be a worksheet named "PDF" in the workbook with the code (your published workbook does have such a worksheet). At least some of the files listed in column A of the worksheet "PDF" must exist in the folder Desktop\PDF. They must have the ".pdf" extension. Note that the extension might be invisible in your Windows Explorer due to your settings. Even if so, the extension will be correct if they are normal PDF files.
Please don't thank me for my effort. Instead, mark my answer as "selected". However, I wouldn't be happy about that unless it actually works for you. Therefore, if you can't make it work, please advise the result you get, especially if you get an error message, or which difficulty or doubt you face, so that I can help you. You will learn faster if you accept/enable help, and it isn't really all that difficult, even if you are new to VBA.
Variatus (rep: 2523) Nov 9, '18 at 12:06 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login