Print to PDF from Microsoft Excel without changed picture aspect ratio - Kopl.PRO

The picture from XLSX file printed to PDF got...

You created the XLSX sheet, perfectly finetuned and images you used were set the Aspect-Ratio 1 x 1. All looks fine, until you perform the print to PDF file. At that moment everything is still fine in the sheet, but the Aspect Ratio of the same image in the resulting PDF file is now instead of the square the rectangle, wasting your intentions and time you spent.

The trick to fix the issue is temporary height increasing and decreasing after the printout.

Did you considered the steps to fix it?

The fact is, that each image contained in the PDF printout from any EXCEL sheet, will got changed to a lower height no matter, how will be arranged the picture in the sheet before the printing to PDF. The trick, how to avoid distorted image dimensions in printout is following:

Before sending the sheet to PDF printer, change the image height, and increase it by 6 percents of original image height. After the printout, decrease the height back to the previous height of the image.

Now what? I need to automate that

OK, so consider the situation mentioned, and let's create an easy code in macro, that will perform following steps as expected.

Enumerate picture objects in your sheet.
Get the name of the last picture, the one, you would like to change the dimensions of.
Select the picture using previously found picture object's name.
Disable Aspect Ratio lock, so it is possible to adjust the height and width as well, having your custom dimensions.
Now, because picture objects uses points and percentages, change the picture's height by 6%. It means, you will improve the dimension by 1.06.
Perform all the tasks you need before printing to the PDF file, so it looks well.
Now, revert the height to its previous value in sheet, so the dimension of the picture is again square with same dimensions for width and height.

Sub Masterprocess()
Call dimensions("Up")
Call PrintSelectionToPDF 'The macro calls downscaling the height of image
End Sub

Sub PrintSelectionToPDF()
Dim NabidkaRozsah As Range
Dim pdfile As String
Dim Shex As Object
Set NabidkaRozsah = Range("A1:I81") 'Selection of the range to printo into PDF
pdfile = "C:\_DEV\filename.pdf" 'filepath to print to PDF
NabidkaRozsah.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=pdfile, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False
Set Shex = CreateObject("Shell.Application") 'Create Filesystem object
Shex.Open (pdfile) 'Open created PDF file in associated app
Call dimensions("Down")
End Sub

Sub dimensions(Smer As String)
Dim pic As Excel.Picture
Dim JmenoObrazku As String 'ImageName
Dim Ratio As Double

For Each pic In ActiveSheet.Pictures
ImageName = pic.Name
Next pic
If Smer = "Up" Then Ratio = 1.06 'height adjustment for proper printout Aspect-Ratio in PDF
If Smer = "Down" Then Ratio = 0.9434 'decrease to original height in Excel sheet
ActiveSheet.Shapes.Range(Array(ImageName)).Select
Selection.ShapeRange.LockAspectRatio = msoFalse 'Disable Aspect-Ratio lock.
Selection.ShapeRange.ScaleHeight Ratio, msoFalse, msoScaleFromTopLeft
Range("C65").Select 'Deselection of the picture selected.
End Sub

The values highlighted are percentages, and therefore applies to each image dimension of the height.

How to fix that uggly bug in picture PDFing?

It is long-time existing issue, whith printing the sheets containing the pictures from Microsoft Excel. So far it is not fixed. Therefore, here is a workaround for your VBA codes.

The picture from XLSX file printed to PDF got...

Did you considered the steps to fix it?

Now what? I need to automate that