Macro to change multiple file names (monthly)

Hi -

Please try this one and let me know;

Sub createPDFCopy()
Dim lastRow%, sourceFolder$, destinationFolder$
Dim fso, r As Range
lastRow = Cells(Rows.Count, 1).End(xlUp).Row
sourceFolder = "your source folder path" 'change this line
destinationFolder = "your destination folder path" 'change this line
Set fso = CreateObject("Scripting.FileSystemObject")
For Each r In Range("a2:a" & lastRow)
    fso.copyfile sourceFolder & "\" & r.Value & ".pdf", destinationFolder & "\" & r.Offset(, 1).Value & ".pdf"
Next
Set fso = Nothing
End Sub

Event

Originally Posted by event21

Hi -

Please try this one and let me know;

Sub createPDFCopy()
Dim lastRow%, sourceFolder$, destinationFolder$
Dim fso, r As Range
lastRow = Cells(Rows.Count, 1).End(xlUp).Row
sourceFolder = "your source folder path" 'change this line
destinationFolder = "your destination folder path" 'change this line
Set fso = CreateObject("Scripting.FileSystemObject")
For Each r In Range("a2:a" & lastRow)
    fso.copyfile sourceFolder & "\" & r.Value & ".pdf", destinationFolder & "\" & r.Offset(, 1).Value & ".pdf"
Next
Set fso = Nothing
End Sub

Event

I will try this & get back to you! Thank you!

Originally Posted by event21

Hi -

Please try this one and let me know;

Sub createPDFCopy()
Dim lastRow%, sourceFolder$, destinationFolder$
Dim fso, r As Range
lastRow = Cells(Rows.Count, 1).End(xlUp).Row
sourceFolder = "S:\OFFICE\Fran\Pricers\!! SALE PRICERS\2014\2. Feb 2014 Sale\Feb 2014 - Spirits" 'change this line
destinationFolder = "S:\OFFICE\Fran\Pricers\!! SALE PRICERS\2014\2. Feb 2014 Sale\Feb 2014 - Spirits" 'change this line
Set fso = CreateObject("Scripting.FileSystemObject")
For Each r In Range("a2:a" & lastRow)
    fso.copyfile sourceFolder & "\" & r.Value & ".pdf", destinationFolder & "\" & r.Offset(, 1).Value & ".pdf"
Next
Set fso = Nothing
End Sub

Event

Hello! After changing the source folder path (see above, I edited to my paths.. should I take the quotes out?) I'm getting "Run-time error '52' Bad file name or number" and then it highlights the "fso.copyfile..." line when I go to debug.

Originally Posted by HaHoBe

Hi, awc7,

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

Ciao,
Holger

I changed it. If that's not good enough please feel free to change it yourself.

Originally Posted by GreyGhost

Hi awc7

Would I be right in assuming this exercise will be a monthly one ?
Will it always be in the same workbook or a new workbook each month ?
Would the PDF files be renamed ...
eg, "FEB 2014 - ALL SPIRITS 1 Alize Liqueurs 750ml.pdf" ... as ... "Alize Liqueurs 750ml.pdf"
If so, trimming the file names would not require any re-writing to the worksheet

Changing code expressions in an Excel project on an ongoing basis (eg, each month) should be avoided because over time the file will become unstable.
Excel is famous for it.

There is no such thing as a simple macro.
Such an exercise you are wanting will have to consider any variations you might want to make in the future, particularly the address of the target folder of PDFs each month ... or changes to the starting list.

Hi GreyGhost!

Yes, this is a monthly task (each month I have 3 sets of files I will need to rename). It will be a new work book each month.
Sorry, the example I gave mashed both the old name & the new names together. The original would be "FEB 2014 - ALL SPIRITS 1.pdf" (then 2, 3, 4, so on), and the new name would be "Alize Liqueurs 750ml.pdf" (then Amarula Cream Liqueur 750ml, then Antica Sambucas 750ml, and so on).

And I only said "simple" because I found this exact question all around the web (with solutions), but couldn't tailor any of them for my own problem. I know what you all do isn't easy

Thanks so much for your help.

Hi -

You can isolate the issue I believe.
Create a simple local folder and then transfer few files in there and test the code.

event

Originally Posted by event21

Hi -

You can isolate the issue I believe.
Create a simple local folder and then transfer few files in there and test the code.

event

YES!! It worked! I just think the source folder was too complicated, perhaps. A simple folder on the desktop worked just fine. Thank you, thank you!

Hi -

Glad it works.

event

Hi AWC7

When manipulating files with VBA ...
If any file path (full-name) has a space in it, the full-name must be wrapped in extra double quotes.
eg, "Program Files" folder is notorious for causing errors and you cannot use "Windows Environment Variables" %ProgramFiles% in the string (in VBA) either.

"""C:\My Folder\my_file.pdf"""

-----
SIMPLE RENAMING (with VBA) no lists required
Your PDF files can be renamed (if the new "file-name" is derived from a consistent default name), without referring to an Excel sheet list.
Particularly if each batch of default PDFs are located in different folders each month.
You can use an Excel XLSM project with a button on it to ...

• Show a folder selector dialog, where you can browse to the holding folder.
• Msgbox (yes/No) showing count of PDFs with common prefix on all files + option to continue or cancel
  eg, Found 120 PDF files with prefix ""FEB 2014 - ALL SPIRITS 1"" vbLf Do you want to continue?"
  You could create an extra worksheet (named "REFS") with one named cell where you can change the "prefix" if ever needed
• after msgbox "Yes" ... all file renaming will be made to the existing PDF files
• this would negate having to manually create an extra field/column of short-names in adjoining cells
  For memory I think you will have 120 (approx) files to rename, a lot of typing, and a risk of creating typos

--OR--

If you need to list the files in an Excel sheet, subject to...
... a new book each month -OR- a new sheet in an existing book each month

NEW BOOK EACH MONTH
Pros:- short names can be a totally new name, unrelated to the old-name string
Cons:- a lot of typing to create shorter names + remember to alter code with folder-path to the PDF files

• Create a template file, with macros/VBA in it
• a sheet button (or ActiveX button) on the wsheet
• Load your list of default-named PDFs (how ever that is done?)
• sheet button to loop through list of default named PDFs and change the files names of each PDF file

NEW SHEET EACH MONTH
Pros:- short names can be a totally new name, unrelated to the old-name string
Cons:- a lot of typing to create shorter names + a lot of manual steps, risk of errors by user.

• create a new worksheet and name it "REFS"
• Name two cells (project scope)
  1. NR_FOLDER_PATH ... (name is my suggestion, NR = named range)
  2. NR_TARGET_SHEET_NAME ...
• Manually type new short names for all 120 files
• in named cell "NR_FOLDER_PATH" copy past folder path
• in named cell "NR_TARGET_SHEET_NAME" copy paste the target sheet-name
• run macro to rename all PDF files

For my money I would opt for no lists on wsheet and use the SIMPLE RENAMING (with VBA) no lists required to reduce risk of human error.