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
rPrint.CopyPicture Appearance:=xlScreen, _
Hi = rPrint.Height + 0 ‘adjustment for gridlines
Wi = rPrint.Width + 0 ‘adjustment for gridlines
MakeAndSizeChart ih:=Hi, iv:=Wi, wbChart:=wbChart
.Export Filename:=sSaveName, FilterName:=“GIF”
Application.StatusBar = False
Application.ScreenUpdating = True
Private Sub ImageContainer_init(ByRef wbChart As Workbook)
Set wbChart = Workbooks.Add
.Name = “GIFcontainer”
.ChartType = xlColumnClustered
.Location Where:=xlLocationAsObject, _
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, _
Vincrease = iv / Cht.Width
Shp.ScaleWidth Vincrease, _