print JPEG to PDF, set paper size

Question

Using VBA, for a given JPEG (or GIF or BMP), I want to print it to a PDF, and set the page size before printing. I've looked at a couple of different ways to print, but no one of them seems to be able to do what I want:

• Use the Acrobat SDK to call AVDoc.Open(), AVDoc.GetPDDoc(), and then PDDoc.Save(): can't choose page size using this method

• Declare WinAPI functions SetDefaultPrinter and ShellExecute, use SetDefaultPrinter to set printer to PDF print driver, then call ShellExecute(1,"print",filepath,"",rootdirectory,1): I haven't found a way to set page size with this method either

Page size can be set using the methods associated with an Office document object, e.g. Worksheet.PageSetup.PaperSize = xlPaper11x17, but this only sets page size for that object, not for the JPEG that I want to print.

Answer 1

You could just import it into your Excel workbook and print it from there. Here's a rudimentary example:

Dim ws As Worksheet
Dim pic As Picture

Set ws = ActiveSheet
ws.PageSetup.PaperSize = xlPaperA4
'Can also specify margins, etc.

ws.Range("A1").Activate
Set pic = ws.Pictures.Insert("C:\mypic.jpg")   
'Set picture size.
With pic.ShapeRange
    .LockAspectRatio = msoFalse
    .Height = Application.CentimetersToPoints(20)
    .Width = Application.CentimetersToPoints(15)
    'Or you could match the size to the paper margins from above.
End With

ws.ExportAsFixedFormat Type:=xlTypePDF, _
    Filename:="C:\mypic.pdf", OpenAfterPublish:=True

Tested in Excel 2010.