Print/Convert to PDF and auto rename the file

I have found this Macro below. I would like see if i can leave it so that it picks up the text in cells A1, & A2 and adds it to the name.

Any suggestions?

Public Sub SaveAsA1()
ThisFile = Range("A1:A54").Value
ActiveSheet.SaveAs Filename:=ThisFile
End Sub


Private Sub CommandButton1_Click()


Application.DisplayAlerts = False
template_file = ActiveWorkbook.FullName
ActiveSheet.Range("A1:A54").Select


fileSaveName = Application.GetSaveAsFilename( _
InitialFileName:="C:\Users\lopezm\Desktop" + Range("A9").Value & " Invoice# " & Range("F3").Value & " Order ID#" & Range("F9").Value & " Mobile No." & Range("C15").Value & " " & Range("F13").Value & "-" & Range("F11").Value & ".pdf", _
fileFilter:=" (*.pdf), *.pdf")

If fileSaveName = False Then
Exit Sub
End If



ActiveWorkbook.SaveAs Filename:=fileSaveName, FileFormat:=xlTypePDF

Application.DisplayAlerts = True
End Sub

I wrote an article here at EE called How To Rename-Move a Batch of PDF Files Based on Contents of the Files:
https://www.experts-exchange.com/Software/Misc/A_11173-How-To-Rename-Move-a-Batch-of-PDF-Files-Based-on-Contents-of-the-Files.html

There are two ways to modify the program presented in that article to do what you want. First, if the different suffix is always in the same cell in the worksheet (such as A1 or C3), then the program can read the XLS (or XLSX) file that has the same name as the PDF file and do the mass renames that way. Second, if the different suffix is somewhere in the PDF file and always in the same location in the PDF files (such as line 1, column 10) or able to be located algorithmically (such as first page, after "Client Number:"), then it can do the mass renames that way. Regards, Joe

Hello,

How can the program read the XLS if it is asking for a column number of the PDF?

Also, my excel is more like a form and the name would be in a particular cell.

IS there a macro that can look at a particular cell in the spreadsheet and then save as a pdf with that cell content as the name of the file?

> How can the program read the XLS if it is asking for a column number of the PDF?

What I'm saying is that the program can be modified to read a cell in a spreadsheet. So instead of asking for a column number in the PDF, it would ask for a particular cell in the spreadsheet, such as A1 or C3 or whatever.

I just noticed in your second post that the suffix is in cells A1 and A2. So the program would look at every XLS (and/or XLSX) file, read cells A1 and A2, concatenate them, then rename the PDF file that has the same name as the Excel file with the concatenated A1 and A2 as a suffix. Example:

Excel file is John Smith.xls
Cell A1 is ABC
Cell A2 is XYZ
PDF file is John Smith.pdf

The program would rename the PDF file to:

John Smith ABCXYZ.pdf

Of course, it could put in separators if you want, such as:

John Smith ABC XYZ.pdf

John Smith ABC_XYZ.pdf

John Smith_ABC_XYZ.pdf

John Smith-ABC-XYZ.pdf

Regards, Joe

I used A1 and A2 as an example (Works fine like this). The actual name that contains the data is cell V16. (The excel is like a form template)

My question is:

What do I enter for "Enter the number of characters in the file name before the PDF extension?

The original file names lengths may vary .


What do i enter for starting column number if the cells to use for the name are not the only text in starting column 22 for column V

I am unable to enter an exact cell.

Hope i was clear and thank you.

Perhaps I haven't been clear, so let me try again. The program as published in that article will not work for you as-is. What I am trying to say is that the program could be modified to do what you need. The modified program (not the one currently in that article) would not ask for a column number in the PDF that contains the suffix – it would ask for a cell in the Excel spreadsheet that contains the suffix, which I guess you're saying is V16 (but instead of hard-coding the program for V16, it would be better to prompt for the cell so the program would be more general and work for folks who have a similar need, except that a cell other than V16 contains the suffix).

So, to be clear, you or I or someone would have to modify the program in the article to do what you're looking for. It should not be difficult to modify the program to do it (and most of the code will remain unchanged), but the program as published in that article will not work for you as-is. Regards, Joe

Hi IATA,
I modified the program to do what you need. Specifically, for each PDF file that it finds in the source folder, it looks for an XLS or XLSX file with a matching file name. It then looks in a specified cell in the matching Excel spreadsheet and uses that as a suffix to rename the PDF file. For example, let's say it finds a file named:

Order Number 12345.pdf

It then looks for a file named:

Order Number 12345.xls

or:

Order Number 12345.xlsx

It then reads a cell in that spreadsheet – in your case, you'd specify cell V16 when running the program. Continuing this example, let's say that cell V16 contains:

K56789

Then the PDF file would be renamed to:

Order Number 12345K56789.pdf

To make the new file name more visually pleasing, the program allows you to specify a separator string (any number of characters) that it places between the file name and the suffix. That way, the file could be renamed to:

Order Number 12345 K56789.pdf

or:

Order Number 12345_K56789.pdf

or:

Order Number 12345_Invoice K56789.pdf

Please confirm for me that the specifications above meet your requirements. I plan to submit the article (with the program) tomorrow for publication. I will post back here with a URL to the article as soon as EE publishes it. Regards, Joe

IATA-techops,
I haven't heard back from you on the specs above, but I went ahead and finished the article and program based on those specs and submitted it for publication. EE's Page Editors are usually fast, so I expect to be able to post the published article's URL here in the next day or two. Regards, Joe

Hi IATA-techops,
It's been a few days since my EE article was published, so I'm wondering if you've had a chance to try the solution proposed in (and attached to) the article. But if you don't think this is the right solution for you, please let me know...so I can stop pestering you. :)  Thanks, Joe

IATA-techops,
It's been a week and a half since the article was published. Have you tried it yet? Thanks, Joe

Hi IATA-techops,
I don't mean to be a pest, but I'm trying to clean up a number of open questions, and your last post on this one was nearly four months ago on 15-January. I hope that you read my article and tried the solution proposed in it, but in any case, I'd appreciate it if you close the question and/or provide a status update. Thanks much, Joe

I've requested that this question be deleted for the following reason:

Not enough information to confirm an answer.

There is absolutely enough information to confirm an answer! I wrote a 272-line custom program based on the specifications of the asker. It does exactly what the EE member asked for. I tested it thoroughly. If EE admin or the asker or anyone thinks that it doesn't do what the asker specified, I'd like an explanation of that, as I believe it does everything requested. And I must say that it's extremely aggravating to go the extra mile (or two or three) and write a custom program and provide complete source code for it, only to have your answer be deemed by EE as insufficient. I suspect that as a work-for-hire, this effort would have cost at least a thousand dollars, and even at that price, I doubt that a professional programming shop would include the source code. Deleting this question under the auspices of "Not enough information to confirm an answer" is beyond the pale. Regards, Joe

Netminder,
Thanks for that — I really appreciate it! Regards, Joe