We produce quotes in Excel and print them to a PDF print driver. PDF makes a lovely representation of the document, in my opinion. The only problem that I have is that some of my customers can’t open the PDF. From what I can tell, this is a version issue, namely that they have v7 and I have v6. You’d think it would be 100% backward compatible, but it must not be.
Actually, the version issue is just a theory and may not actually be correct. Believe it or not, my customers don’t want to take the time to troubleshoot the problem with me, they just want to get a bleeping quote via email. Whatever the problem, it’s a problem that I need to fix.
There’s nothing particularly complicated in the spreadsheet, so I decided to start sending the quotes as html. What’s more portable than Portable Document Format? Html, that’s what. I can publish an Excel range and it will produce horribly bloated html, but at least my customers will know how much I want to charge them. Not so fast. I have an image on the spreadsheet — my logo. Html doesn’t support images, only links to images. To use this method, I’d have to put my logo on a web server, edit the html to point to that, and hope every customer has Internet access at the moment they’re viewing my quote. Not good enough.
I need something with the visual abstraction of a pdf, but the universal portability of html. Taking a picture sounds like it would fill the bill. Everyone has image viewing software and a gif is a gif is a gif, right? I use Stephen Bullen’s PastePicture class module to create the picture that goes on my computer’s desktop, so the code is half done. As far as I can tell, PastePicture only creates bmp files. My quotes came out to about 2.5 megs vs. 50k for a pdf. I knew GIF was a leaner image format, so I went searching for some code to create a gif.
David McRitchie has some code created by Harald Staff called XL2GIF which looked good. I change the code around a little, which I reproduce below. This code produced a GIF quickly and easily, and at about 25k I thought I was in business. I have two problems however. First, the printout has a border around it and is of generally crappy quality. If this was an internal document I would have no problem with it. But I’m trying to woo customers here, so it’s kind of a deal breaker. I have no idea why there’s a thin border around the image – it must be the chart’s border – but I don’t like it. Second, printing this image from a standard Windows install is a joke. While my computer has IrfanView, a normal image rendering and printing program, not all computers do. My customers who don’t have special image software will likely be opening the file in Windows Picture and Fax Viewer. To print something out of this piece-of-junk software requires a four-step wizard. To print. Unbelievable.
So I’m back to sending pdfs until I can figure out a better way. Here’s the modified code – all the logic is Harald’s, I just got rid of those pesky Selects and Activates.
‘XL2GIF_module — GIF_Snapshot
‘Modified by Dick Kusleika 9/27/06
Public Sub GIF_Snapshot(rPrint As Range, sSaveName As String)
Dim Hi As Integer
Dim Wi As Integer
Dim wbChart As Workbook
ImageContainer_init wbChart
rPrint.CopyPicture Appearance:=xlScreen, _
Format:=xlBitmap
Hi = rPrint.Height + 0 ‘adjustment for gridlines
Wi = rPrint.Width + 0 ‘adjustment for gridlines
MakeAndSizeChart ih:=Hi, iv:=Wi, wbChart:=wbChart
With wbChart.Sheets(1).ChartObjects(1).Chart
.Paste
.Export Filename:=sSaveName, FilterName:=“GIF”
End With
Avbryt:
Application.StatusBar = False
Application.ScreenUpdating = True
wbChart.Close False
End Sub
Private Sub ImageContainer_init(ByRef wbChart As Workbook)
Set wbChart = Workbooks.Add
With wbChart.Sheets(1)
.Name = “GIFcontainer”
With Parent.Charts.Add
.ChartType = xlColumnClustered
.SetSourceData Source:=Worksheets(1).Range(“A1”)
.Location Where:=xlLocationAsObject, _
Name:=“GIFcontainer”
‘.ChartArea.ClearContents
End With
End With
End Sub
Sub MakeAndSizeChart(ih As Integer, iv As Integer, wbChart As Workbook)
Dim Hincrease As Single
Dim Vincrease As Single
Dim Cht As ChartObject, Sh As Worksheet, Shp As Shape
Dim sChtName As String
Set Sh = wbChart.Sheets(1)
Set Cht = Sh.ChartObjects(1)
Set Shp = Sh.Shapes(Cht.Name)
Hincrease = ih / Cht.Height
Shp.ScaleHeight Hincrease, _
msoFalse, msoScaleFromTopLeft
Vincrease = iv / Cht.Width
Shp.ScaleWidth Vincrease, _
msoFalse, msoScaleFromTopLeft
End Sub
I think you should figure out why your clients can’t read your PDF file. Which PDF print driver do you use? I think there are several available (I use Primo), and you should be able to find one that’s more compatible. Even if you have to pay for it (gasp!).
Another (stupid) option is to save your workbook as an MHT file. This is a single-file browser format that can be viewed only in Interenet Explorer (I think). Everybody has IE.
Solving the PDF problem makes more sense to me.
And furthermore…. if someone sent me a proposal in the form of a GIF file, I’d think they were a computer moron. :)
>> if someone sent me a proposal in the form of a GIF file, I’d think they were a computer moron.
That was my thought too.
I agree with John about the choice of PDF drivers, I use PrimoPDF too. Nobody has ever had a problem with any PDF I’ve sent.
I use Adobe Standard 6.0 – bought and paid for.
Hi Dick,
Since you already have Adobe, I can see why you may not want to go this route, but personally I’m a big fan of SourceForge’s PDF Creator. XL-Dennis has already posted about it here, actually. (http://www.dailydoseofexcel.com/archives/2006/05/12/excel-pdfcreator/)
It’s freeware, can create documents in a variety of formats, including PDF, JPG, BMP and TIF, among others. Sadly it doesn’t support GIF for your quotes. (I wouldn’t recommend TIF either, btw.)
The good news is that to date, I have never had any issue with someone trying to read a file created in PDFCreator. I’ve never asked their versions, as I’ve never needed to.
The best thing about PDFCreator, though, it is that it supports VBA automation, so you can set a routine up and not have to worry about fooling with it later. (You’re probably already doing this with acrobat now.) There’s a few examples of how to code it on my site if you’re interested: http://www.excelguru.ca/taxonomy/term/21
Hi Dick,
Long time reader and first time poster.
Why don’t you change the PDF version compatability to either ver 5/4/3 (depending on what works best for your clients). You can change the job options in Distiller or in PDFWriter via the Printer properties (I prefer ver 5 for its clean look and its — for me — lack of any issues with clients).
Cheers!
I don’t want to be a computer moron. Actually, I don’t want people to think I’m a computer moron: whether I actually am is of less importance. So I’m inclined to follow John and Jon and Kens advice and fix my pdf problem. Is there a least common denominator for pdfs? Comma Separated Values, CSV, is what I would consider the LCD for Excel. You don’t get any of the benefits of Excel, but you know that any version can open — in fact any version of any spreadsheet software can open it. My question is this: If I go with Primo or pdfcreator, will I be better off than Adobe 6.0. Will these third party programs give me the LCD that Adobe won’t?
Dick,
You might want to take a look at this service:
Web site PDF Online lets you convert a bevy of document types to PDF online, sending the resulting PDF to your email address as an attachment. Convert to PDF for Free >>
You can convert these following formats to PDF for Free:
DOC PPT XLS JPG GIF TIFF
RTF PPS HTML BMP PNG EMF
TXT PUB
9/27/2006 — Clipped From the web document : Create PDF Online free, PDF API, PDF .NET Server,
http://www.pdfonline.com/index.htm#why_use
https://www.pdfonline.com/track.asp
Hi Dick,
As far as PDF goes, I think that the only versioning is truly in what version you save it in. I long ago abandoned Adobe’s writers, so I’m working on memory here, which may be wrong. I’m pretty sure that if your users are reading in Acrobat 7.0, they should be able to read anything created in Acrobat v4.0 or higher, I believe. (Could be 5.0, can’t remember for sure.) You may want to try just saving your v6.0 pdf as a v5.0 pdf and sending that to see if they can read it.
Chances are that the issue is on their side, not yours. Regardless, it may be worth a try creating in another app or two to see.
Without question, though, it will be very difficult to get this solved if your customer won’t work with you…
Regarding Ken’s comments re: the saving of PDF; the actual PDF version relates to the original creation of the PDF document. There is no ability to reduce the PDF’s version to an earlier version via ‘Save’ or ‘Save As’ actions (although you can update an earlier PDF to a later PDF).
I suggest printing via PDFWriter as a very simply test as it is very basic and writes PDF files using the 1.2 specs (it cannot be changed contrary to my earlier comment). It was originally intended for basic documents without graphics and heavy formatting but does very well for simple logo/text jobs with basic TTF fonts. Distiller gives you the option of changing the version of the PDF spec within its job options (and hence the version of the PDF file itself). Note that PDFWriter is not installed under the default Acrobat installation but can installed via the Custom install option (install with not issue if done after your original install).
It should take no more than 5 minutes to setup specific Distiller job options for all PDF specs from 3-6 (although I would stay away from ver 4 as it is quite stinky — larger files and intermittent font issues). Please email me if you need any assistance and/or want a few samples from ver 3/4/5/6 as I still have all versions on my test PC (used for archiving personal, corporate tax returns and financial statements).
“There is no ability to reduce the PDF’s version to an earlier version via ‘Save’ or ‘Save As’ actions”
I always thoought Adobe’s “Reduce File Size” option did this, as it asks you hww high a version you want the reduced file to be compatible with. However, since Dick says that his clients have version 7 and he has version 6, saving to an earlier version’s format, if possible, would not seem to be the issue.
I send and receive lots of PDFs and have used both version 6 and 7, and can’t recall any problems with people not being able to open them, and have not had that problem. I also suspect the problem has to do with something else on the customer’s end.
For “Reduce File Size” notes see this link (halfway down the page):
http://www.adobe.com/support/techdocs/328355.html
The key is to have the file created with the PDF spec lower at the lowest common version between yourself and clients; although I haven’t had any issues going up the chain (vers. 3-6 at home / ver. 7 at work), using the simplest pure creation method (Adobe PDFWriter since Dick owns ver 6) will allow him to eliminate Adobe as an issue.
Cheers!
Dick,
You say about html emails:
“To use this method, I’d have to put my logo on a web server, edit the html to point to that, and hope every customer has Internet access at the moment they’re viewing my quote. Not good enough.”
There is a way around that if you are prepared to have IIS running on the box you are sending the email from:
http://www.enhanceddatasystems.com/ED/Pages/ExcelSendChart.htm
Keep up the good work with the site.
I use the same tool as ken for PDF creation, I have heard that working with Acrobat is like pulling teeth.
I stopped using the reader a while ago, It bugged me how bloated and slow it was, i use this now, had no problems:
http://www.foxitsoftware.com/pdf/rd_intro.php
but if the report is quite fixed, can’t you just write some HTML and send the image along with the file, you could even use Word!… Or send your clents a Excel workbook with the qoute in, but yeah not a nice as a PDF.
P.s
Andy pope has some good “to image” sw
“I have heard that working with Acrobat is like pulling teeth.”
… like pulling your own teeth.
Dick:
Considering you’re an excel kind of guy, why not send this particular client an Excel document? There’s a free Excel Viewer available if you’re worried they might not have a copy of Excel loaded…
Just a thought.
– Kevin
“… like pulling your own teeth.”
Yes! it’s more like that i’d guess! ;-)
I’m a daily user of Acrobat and still have my teeth (and most of my hair)… :)
I haven’t looked at PDFCreator in a while, but I do put it on users’ machines that don’t have Acrobat. I believe it doesn’t allow me to do many of the things I use Acrobat for. For example, can I merge multiple PDFs into one PDF, insert pages from one PDF into another or edit a PDF? If so, that would be great to know.
Hi Doug,
When you’re printing PDF’s using PDFCreator, you can print from multiple (raw) docs into one PDF. Once it’s done, though, I don’t believe you can manipulate it, although I’ve never actually tried. It’s pretty much PDF creation software, not editing software. I guess that may be why Acrobat can justify the price tag on their package. ;)
This may or may not be of interest; everyone has PDF reader, but nearly everyone has Ms Word.
If you wrap an A4 sized bitmap (well over 1Mb) in a Word doc (just paste it in), Word will compress it down and give you a .doc between 70 and 120k.
When working with Excel/PowerPoint and PDFs you get much better results if you use the commandbar button that acrobat builds in Excel/PowerPoint to create the pdf vs. printing to pdf using the print command.
Also, you should be able to choose a prior version format (as the default) in Acrobat preferences. That should get you past any prior version issues if the client is on say v4 or 5 and you are using v6.
I recently had to do a bit of work with Acrobat pro at work. The PA in our office has it. I could not work out for the life of me why anyone would want to edit the PDF. If you have control of the socure, it only makes sense to edit that and [produce a new PDF.
Unless there is some other reason that i cant imagine!
> If you have control of the socure, it only makes sense to edit that and [produce a new
And if you don’t?
We had a privacy policy on our company website once, written by our former web site designers, that was very long. We’d moved to a new designer, and a new host, but had just ported that document. Then the management company changed, and the legal company name needed to be updated. While we were no doubt legally entitled to go back and get the source from our original designers, it was far less effort to get someone to edit the PDF, and far more cost effective than retyping it.
(Only time I’ve ever had to edit a PDF though.)
:)
I edit PDFs on a somewhat regular basis. It’s just a tradeoff of whether it’s easier to go back to the original, make a small edit and recreate the PDF or just make a small edit in the PDF. Anything that requires editing over a line-wrap is probably where I draw the line, as that can have pretty wacky effects in a PDF.
I took a look at the same problem, and found a solution to the border problem… I built upon your code to be able to export charts, ranges, and objects. Check it out.
http://www.consultantninja.com/2007/11/exporting-excel-charts-shapes-ranges.html