Solved

Print/Convert to PDF and auto rename the file

Posted on 2014-01-15
18
819 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
ID: 39784207
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 53

Expert Comment

by:Joe Winograd, EE MVE
ID: 39784233
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
ID: 39784347
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 53

Expert Comment

by:Joe Winograd, EE MVE
ID: 39784363
> 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 53

Expert Comment

by:Joe Winograd, EE MVE
ID: 39784390
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
ID: 39784449
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 53

Expert Comment

by:Joe Winograd, EE MVE
ID: 39784462
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 53

Expert Comment

by:Joe Winograd, EE MVE
ID: 39793462
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
 
LVL 53

Expert Comment

by:Joe Winograd, EE MVE
ID: 39799134
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 53

Accepted Solution

by:
Joe Winograd, EE MVE earned 430 total points
ID: 39802057
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 53

Expert Comment

by:Joe Winograd, EE MVE
ID: 39808891
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 53

Expert Comment

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

Expert Comment

by:Joe Winograd, EE MVE
ID: 40040735
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 46

Expert Comment

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

Not enough information to confirm an answer.
0
 
LVL 53

Expert Comment

by:Joe Winograd, EE MVE
ID: 40393395
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 53

Expert Comment

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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Let’s list some of the technologies that enable smooth teleworking. 
This guide will walk you through the essential considerations and tech stack for building scalable websites. Know how to grow your business the smart way!
We often encounter PDF files that are pure images, that is, they do not have text characters, but instead contain only raster graphics. The most common causes of this are document scanning software and faxing software/services that create image-only…
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…

789 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