Exporting ranges as images in excel 2010

Posted on 2012-09-20
Last Modified: 2012-09-21

I'm trying to export a range of cells in Excel to a PNG file using VBA.

I have a script that does that job, but when it resizes the chart object with " .Height = PicTemp.Height + 8" it squashes the table too... I can't see how I prevent this happening, essentially I just want the chart element with no white space - sized as it was in the Excel sheet.

Output screenshot attached.

I'm working with 2010.


Sub ExportCellsAsPicture()
    Const FName As String = "C:\temp\SavedRange.jpg"
    Dim pic_rng As Range
    Dim ShTemp As Worksheet
    Dim ChTemp As Chart
    Dim PicTemp As Picture
    Application.ScreenUpdating = False
    Set pic_rng = Worksheets("Sheet1").Range("B3:G7") 'Set your range here
    Set ShTemp = Worksheets.Add
    ActiveChart.Location Where:=xlLocationAsObject, Name:=ShTemp.Name
    Set ChTemp = ActiveChart
    pic_rng.CopyPicture Appearance:=xlScreen, Format:=xlPicture
    Set PicTemp = Selection
        With ChTemp.Parent
        .Width = PicTemp.Width + 8
        .Height = PicTemp.Height + 8
    End With
    ChTemp.Export Filename:="C:\temp\SavedRange.PNG", FilterName:="PNG"
    Application.DisplayAlerts = False
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub

Open in new window

Question by:bedsingar
    LVL 18

    Accepted Solution


    Add this  
    If PicTemp.Placement <> 3 Then PicTemp.Placement = 3

    Open in new window

    after Set PicTemp = Selection


    Author Closing Comment

    Perfect thanks


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
    Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
    This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    758 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now