2

I need to export my excel worksheet to an image with specific size (or at least aspect ratio).

I created a square grid by setting columns and rows to the same size in page layout mode. If I export the area I want by a script (see script below) or by just copy-pasting into paint, it has a different aspect ratio than I would expect.

Data from my experiments:

Row height: 0.38 cm
Column width: 0.38 cm
Area: 64 columns x 40 boxes --> Ratio of 1.6:1

Copy/Paste to paint:         897 x 601  (1.49:1)
VBA Export (Zoom-coef. 1):  1024 x 680  (1.51:1)
VBA Export (Zoom-coef. 2):  2048 x 1360 (1.51:1)

What creates this weird difference in aspect ratio?

The VBA Image Export Script:

Sub ExportImage()

    exportpath = "C:\Users\ ..."
    rr = "A1:BL40"
    Set xWs = ActiveWorkbook.ActiveSheet

    'Captures current window view
    sView = ActiveWindow.View

    'Sets the current view to normal so there are no "Page X" overlays on the image
    ActiveWindow.View = xlNormalView

    'Temporarily disable screen updating
    Application.ScreenUpdating = False

    'Selection Print Area
    xWs.PageSetup.PrintArea = xWs.Range(rr).Resize(xWs.Range(rr).Rows.Count, xWs.Range(rr).Columns.Count).Address

    'Export print area as correctly scaled PNG image, courtasy of Winand
    'Lukasz: zoom_coef can be constant = 0 to 5 can work too, but save is 0 to 4

    zoom_coef = 2

    Set area = xWs.Range(xWs.PageSetup.PrintArea)
    area.CopyPicture xlPrinter
    Set chartobj = xWs.ChartObjects.Add(0, 0, area.Width * zoom_coef, area.Height * zoom_coef)
    chartobj.Activate
    chartobj.Chart.Paste
    chartobj.Chart.Export exportpath, "png"
    chartobj.Delete

    'Returns to the previous view
    ActiveWindow.View = sView

    'Re-enables screen updating
    Application.ScreenUpdating = True

End Sub

edit: The problem seems to be Excels very stupid way of measuring row-height and column-width in totally different and cryptic units. See Blog Article

  • is [this](https://stackoverflow.com/questions/38351838/excel-vba-save-resized-image-to-file) an option? – Ricardo Diaz Mar 05 '20 at 16:36
  • I would actually really like to find a way to export an exact size in the first place, rather than resizing and compromising the quality afterwards. –  Mar 06 '20 at 15:34

0 Answers0