Rename pdf using excel

[ipbr21054]

Hi,
Not sure if this will work but thought I would ask.

I have several pdf files which I need to rename.
The name of each pdf is saved in the format like so.

Name, ref number, date.
Example.
JOHN SMITH ABC123 24-10-23.pdf

I am needing to rename file to
JOHN SMITH 24-10-23 ABC123.pdf
Basically just swap ref number & date around.

Not sure how this would work but could a code be run so excel would say import the pdf.
Look at the file name & then put each reference in a specific cell.
Example
Cell Z1 = Name JOHN SMITH
Cell Z2 = ref number ABC123
Cell Z3 = date 24-10-2023

Code would then save as pdf taking the cell ref values for the saved name of file.

So Z1 Z3 Z2
Output would then be,
JOHN SMITH
24-10-2023
ABC123.pdf

 

[Micron]

VBA has a file scripting object which can rename your files. IIRC, it also has basic things it can do directly on files without using FSO.
IF the pattern is exactly identified and constant then should be doable.
IF ABC123 24-10-23 is always like ABC123 24-10-23 and never like ABC 123 24-10-23 or ABC12324-10-23 that makes things simpler. You would not have to worry about putting file name portions in a sheet in order to handle this. To start, you should verify that no file name deviates from what you posted.

 

[ipbr21054]

Hi
The format is the same but each file will obviously be different.
Example.
Name & ref number.
Another file could be as,
TOM JONES
A1B2C2
23-10-2023.pdf

In excel I entered the values in specific cells when I created a sheet that I had to print.

My code to save the file took it’s value from the specific cells the values were entered.

So the pdf file name was taken from values in specific cells on the worksheet.

It’s now I see an issue when my saved files are getting bigger hence why I need to swap ref number & date around.

 

[ipbr21054]

VBA has a file scripting object which can rename your files. IIRC, it also has basic things it can do directly on files without using FSO.
IF the pattern is exactly identified and constant then should be doable.
IF ABC123 24-10-23 is always like ABC123 24-10-23 and never like ABC 123 24-10-23 or ABC12324-10-23 that makes things simpler. You would not have to worry about putting file name portions in a sheet in order to handle this. To start, you should verify that no file name deviates from what you posted.

Please can you advise more so i can take a look thanks

 

[Micron]

This is one vba method you can use IF your files and directories exist (cannot create new) and is usually simpler than FSO.

Name statement (VBA)

Office VBA reference topic

learn.microsoft.com

I don't know where your list is or if you even have one. If not, perhaps you can use msoFileDiaologFolderPicker (IMO it's more versatile than built in Excel method) or just Google it. Once you've provided the folder to your code you'd loop over the files. If they are all in the same folder, that's much easier than if there are also subfolders. Then you'll need recursive code that loops over all files in all sub directories. In between all of that you'd need a few lines to swap the file elements around. For that I would have to do some thinking first. F'rinstance if one regards this as a constant format
JOHN SMITH ABC123 24-10-23.pdf

but you have a case like
JOHN ALLAN SMITH ABC123 24-10-23.pdf

then it's not as simple as counting spaces to find the first element that needs to be moved. In that case, another f'rinstance could be to find six characters where the 1st 3 are alpha and the next 3 are numeric instead, but if an example can be: JOHN SMITH 123ABC 24-10-23.pdf then that is another issue. I know you've indicated things are just like you posted but in my experience, all too often a coded solution reveals anomalies that the OP didn't know existed. I can tell you from experience that it's not much fun revising code 2 or 3 (or more) times to deal with anomalies.

EDIT - all that is based on a left to right approach to sort things out. It may be safer to work from right to left but it would be more difficult.

 

[Micron]

Code that could swap the portions might look similar to below, but the input would not be static as shown. Also it would probably have to be a function, not a sub.

Sub swapFileNameParts()
'e.g. input is JOHN SMITH ABC123 24-10-23.pdf
'output should be JOHN SMITH 24-10-23 ABC123.pdf

Dim strOld As String, strDatePart As String, strExtension As String, strNextPart As String
Dim intPos1 As Integer, intPos2 As Integer

strOld = "JOHN SMITH ABC123 24-10-23.pdf"
intPos1 = InStrRev(strOld, " ")
intPos2 = InStrRev(strOld, " ", intPos1 - 1)
strExtension = Mid(strOld, InStrRev(strOld, "."))
strDatePart = Mid(strOld, intPos1 + 1, 8)
strNextPart = Mid(strOld, InStrRev(strOld, " ", intPos2) + 1, 6)

Debug.Print Left(strOld, intPos2) & strDatePart & " " & strNextPart & strExtension

End Sub

 

[ipbr21054]

Thanks for that but it looks way over my head.

Looking at it i would have to enter the strOld each time so i might as well just manually edit them one at a time

 

[Micron]

i would have to enter the strOld each time

Probably not. Perhaps I was not clear but that was intended to show how to reverse the parts, not how to deal with all the inputs. You didn't say where the file names would be coming from (e.g. looping over files in a directory; they are in a sheet column, something else?) so I didn't deal with that. If you need notes in the code I can provide them. One seldom knows the level of vba expertise when it's not revealed so I tend to assume too much at times I guess.

 

[ipbr21054]

No problem.
My signature does advise my condition.

Thanks anyway

 

[Micron]

Yes, I saw that but it doesn't mean you don't already know something, does it?
Good luck with your task. If you change your mind I'll likely see a notification and will do what I can.