Excel Macro Screenshot

Question

We have created a spreadsheet where the cell's colours etc are conditional based on the state of an external source (PLC tags using RSLinx). We have written a macro to take a screen shot of a specific range and save it as a JPG. We have then created a website to display an image of the spreadsheet that will update every 30 seconds or so to give a 'live' representation of whats going on. The macro is shown below:

Option Explicit

Private Sub SaveRngAsJPG(Rng As Range, FileName As String)
Dim Cht As Chart, bScreen As Boolean, Shp As Shape
bScreen = Application.ScreenUpdating
Application.ScreenUpdating = False
Set Cht = Workbooks.Add(xlChart).Charts(1)
Cht.ChartArea.Clear
Rng.CopyPicture xlScreen, xlPicture
Cht.Paste
With Cht.Shapes(1)
    .Left = 0
    .Top = 0
    .Width = Cht.ChartArea.Width
    .Height = Cht.ChartArea.Height
End With


Cht.Export FileName, "PNG", False
Cht.Parent.Close False
Application.ScreenUpdating = bScreen
End Sub

Sub TestIt2()
Dim Rng As Range, Fn As String
Set Rng = Range("A3:AQ40")
Fn = "C:\Users\Desktop\Website\LHImage.png"
SaveRngAsJPG Rng, Fn
End Sub

The problem we have is that the image is saved with the dimensions 981x659. This distorts the image and when I try to alter the size in the .width and .height lines it will resize the subject of the image but leave white space around it to a full size of 981x659.

Is there any way I can alter the size of the image to keep its aspect ratio prior to saving it?

Any help would be greatly appreciated!

Why is there a chart involved? Do you not just want to obtain a copy of the range? — Andy G, Aug 03 '17 at 19:40
Yes that's exactly what we need to do, but as my macro skills are not the best, we had to result to searching google for options and that's all that came back that seemed to carry out the correct function — evorg88, Aug 03 '17 at 19:44
Thanks, I've just tried: `Cht.ChartArea.Width = 100 Cht.Paste` but it says the chart is locked and cannot be resized — evorg88, Aug 03 '17 at 20:18
I get the impression that you are adding a chart as a chart-sheet; an embedded, re-sizeable, chart object would seem more appropriate. — Andy G, Aug 03 '17 at 20:23

score 0 · Answer 1 · answered Aug 03 '17 at 21:00

This approach creates a chart like you did and pastes the range that you want in to the chart. in the chtemp.parent With loop, change the dimensions to fit whatever size your picture is. May take some playing around with.

dim rng as range
dim chtemp as chart
Set rng = Range("A3:AQ40")
rng.CopyPicture Appearance:=xlScreen, Format:=xlPicture
'--------------------------------------------------------- 
Charts.add

'Creates a chart where the final picture will be moved on for picture export
'-----------------------------------------------------------------
ActiveChart.Location WHere:=xlLocationAsObject, Name:='Current worksheet'.Name
    Set chtemp = ActiveChart
    With chtemp.Parent
        .Top = [Y1].Top
        .Left = [Y1].Left
        .Width = [Y10:AU55].Width
        .height = [Y10:AU55].height
    End With

rng.CopyPicture Appearance:=xlScreen, Format:=xlPicture

    chtemp.Paste
    chtemp.Export Filename:='filename, FilterName:="png"
    Application.DisplayAlerts = False

Thanks for taking the time to answer. I have just tried this but it just displays the image as a chart on the spreadsheet without saving the image to the folder. There is also a section of the image that is just white. — evorg88, Aug 03 '17 at 21:13

Excel Macro Screenshot

1 Answers1