Solved

Print/Convert to PDF and auto rename the file

Posted on 2014-01-15
18
733 Views
Last Modified: 2014-10-25
Dear Experts,

We have an excel with an invoice template. The invoice gets populated by a merge via macro.

We output 700 of these invoices and then they have to be renamed manually according to there unique code which creates their nomenclature (Fix prefix and different suffix)

Is there an available Macro that can pick up field from excel and then add it to the PDF name once converted?  

Or tool?

Even a tool that mass renames after they have been PDF'd? by merging via a csv?

Any advice would be appreciated.

ty
0
Comment
Question by:IATA-techops
  • 12
  • 3
18 Comments
 

Author Comment

by:IATA-techops
Comment Utility
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
0
 
LVL 51

Expert Comment

by:Joe Winograd, EE MVE
Comment Utility
I wrote an article here at EE called How To Rename-Move a Batch of PDF Files Based on Contents of the Files:
http://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
0
 

Author Comment

by:IATA-techops
Comment Utility
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?
0
 
LVL 51

Expert Comment

by:Joe Winograd, EE MVE
Comment Utility
> 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.
0
 
LVL 51

Expert Comment

by:Joe Winograd, EE MVE
Comment Utility
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
0
 

Author Comment

by:IATA-techops
Comment Utility
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.
0
 
LVL 51

Expert Comment

by:Joe Winograd, EE MVE
Comment Utility
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
0
 
LVL 51

Expert Comment

by:Joe Winograd, EE MVE
Comment Utility
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
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 51

Expert Comment

by:Joe Winograd, EE MVE
Comment Utility
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
0
 
LVL 51

Accepted Solution

by:
Joe Winograd, EE MVE earned 430 total points
Comment Utility
Hi IATA-techops,
The article, How To Rename-Move a Batch of PDF Files Based on the Contents of Corresponding Excel Files, has been published:

http://www.experts-exchange.com/Software/Misc/A_12872-How-To-Rename-Move-a-Batch-of-PDF-Files-Based-on-the-Contents-of-Corresponding-Excel-Files.html

Please let me know how the program performs for you and if you have any trouble at all with it, or if you would like any changes to it. Regards, Joe
0
 
LVL 51

Expert Comment

by:Joe Winograd, EE MVE
Comment Utility
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
0
 
LVL 51

Expert Comment

by:Joe Winograd, EE MVE
Comment Utility
IATA-techops,
It's been a week and a half since the article was published. Have you tried it yet? Thanks, Joe
0
 
LVL 51

Expert Comment

by:Joe Winograd, EE MVE
Comment Utility
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
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
I've requested that this question be deleted for the following reason:

Not enough information to confirm an answer.
0
 
LVL 51

Expert Comment

by:Joe Winograd, EE MVE
Comment Utility
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
0
 
LVL 51

Expert Comment

by:Joe Winograd, EE MVE
Comment Utility
Netminder,
Thanks for that — I really appreciate it! Regards, Joe
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

I use more than 1 computer in my office for various reasons. Multiple keyboards and mice take up more than just extra space, they make working a little more complicated. Using one mouse and keyboard for all of my computers makes life easier. This co…
Skype is a P2P (Peer to Peer) instant messaging and VOIP (Voice over IP) service – as well as a whole lot more.
XMind Plus helps organize all details/aspects of any project from large to small in an orderly and concise manner. If you are working on a complex project, use this micro tutorial to show you how to make a basic flow chart. The software is free when…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

5 Experts available now in Live!

Get 1:1 Help Now