Range_SaveAsImage

Exports range to an image, something we wanted to be able to do for a while now.
This one uses clipboard, as you may know, I hate using users' clipboards, but as of now, this will be updated once we find out different way.
It creates a chart temporarily, then moves the range into it after taking picture of it using Excel old method.

CodeFunctionName
What is this?

Public

Tested

Original Work

Sub Range_SaveAsImage(RangeAsString, ImageFullFile, Optional Shee = "Active", Optional WB = "This")
    Dim tmpChart As Chart, n As Long, shCount As Long, sht As Worksheet, sh As Shape
    Dim fileSaveName As Variant, pic As Variant
    ' Create temporary chart as canvas
    If WB = "This" Then WB = ThisWorkbook.Name
    If WB = "Active" Then WB = ActiveWorkbook.Name
    If Shee = "Active" Then Shee = ActiveSheet.Name
    Set sht = Workbooks(WB).Worksheets(Shee)
    sht.Range(RangeAsString).Copy
   
    sht.Pictures.Paste.Select
    Set sh = sht.Shapes(sht.Shapes.Count)
    Set tmpChart = Charts.Add
    tmpChart.ChartArea.Clear
    tmpChart.Name = "PicChart" & (Rnd() * 10000)
    Set tmpChart = tmpChart.Location(Where:=xlLocationAsObject, Name:=sht.Name)
    tmpChart.ChartArea.Width = sh.Width
    tmpChart.ChartArea.Height = sh.Height
    tmpChart.Parent.Border.LineStyle = 0
    ' Paste range as image to chart
    sh.Copy
    tmpChart.ChartArea.Select
    tmpChart.Paste
    ' Save chart image to file
    tmpChart.Export Filename:=ImageFullFile, FilterName:="jpg"
    ' Clean up
    sht.Cells(1, 1).Activate
    sht.ChartObjects(sht.ChartObjects.Count).Delete
    sh.Delete
    Set sht = Nothing
    Set sh = Nothing
    Set tmpChart = Nothing
End Sub

RangeAsString, ImageFullFile, Optional Shee = "Active", Optional WB = "This"

Views 96

Downloads 33

CodeID
DB ID