Save range as image?

Charley

Charley

Member
Joined
Jul 29, 2017
Messages
64
Reaction score
0
Points
6
Excel Version(s)
365
I haven't done much VBA programming for a while, but I need to save multiple ranges as images. Do we still need to use that kludgy process of working with the chart object? Or does VBA FINALLY offer a better way to save a range as an image?

Charley
 
.
Here is one way :

Code: 
Option Explicit


Sub Example1()
Dim i As Integer
Dim intCount As Integer
Dim objPic As Shape
Dim objChart As Chart


'copy the range as an image
Call Sheet1.Range("A1:E12").CopyPicture(xlScreen, xlPicture)


    'remove all previous shapes in sheet2
    intCount = Sheet2.Shapes.Count
        For i = 1 To intCount
            Sheet2.Shapes.Item(1).Delete
        Next i
    'create an empty chart in sheet2
    Sheet2.Shapes.AddChart
    'activate sheet2
    Sheet2.Activate
    'select the shape in sheet2
    Sheet2.Shapes.Item(1).Select
    Set objChart = ActiveChart
    'paste the range into the chart
    objChart.Paste
    'save the chart as a JPEG
    objChart.Export ("C:\Users\My\Desktop\TestImage.Jpeg")  'change path as needed
    
End Sub
 
Not that complicated ?

Code: 
Sub M_snb()
  Application.screenupdating=false    

  With Sheets(1)
    For j = 1 To 6
     With .Range("A1:K10").Offset(, 5 * (j - 1))
       sn = Array(ThisWorkbook.Path & "\" & replace(.Address,":","_"), .Width, .Height)
       .CopyPicture
     End With
            
     If Sheets(1).ChartObjects.Count = 0 Then Sheets(1).ChartObjects.Add 100, 0, sn(1), sn(2)
            
     With Sheets(1).ChartObjects(1)
       .Width = sn(1)
       .Height = sn(2)
       With .Chart
         .Paste
         .Export sn(0) & ".gif"      ' sn(0) & ".png" ; sn(0) & ".jpg" ; sn(0) & ".bmp"    ' ad lib.
       End With
     End With
   Next
   .ChartObjects(1).Delete
  End With
End Sub
 
Last edited:
It has always had a way since 32 bit came along. I have not figured out a way to do it in 64 bit Excel. If you want to see the 32 bit API method, let me know. Basically, it saves it as a BMP file.

For use in Outlook body, no external file is needed. One can use WordEditor or htmlBody.
 
Logit & snb...Thanks for your sample code. I'm sure I'll be able to do what I need with your two examples.

Kenneth...Thanks for your ideas. My goal is to have an efficient way to save the images as jpg or gif for use on a web site. So I think I've got it covered.
 
You must log in or register to reply here.
Back
Top