How can I take + export a screenshot of a range of cells as an IMAGE (saved on my desktop)

[d0rian]

My file has 10 worksheets, the last of which is named TABLE and has a summary table of all data in range C2:G57. I need to be able to export an image of that summary table to frequent intervals each day. I'd like to be able to do it with a macro or single click. The only 2 methods I've employed so far are:

1) Manually select the TABLE worksheet, then open the Windows "Snipping" tool, highlighting the desired range, and then saving it as Capture.jpg on my desktop (overwriting the existing Capture.jpg file); OR
2) Manually select the Table worksheet, selecting C2:G57 with the cursor, then opening the Windows Paint 3D program, pasting the range, then cropping the picture area and saving it as an image to the desktop.

Both methods are pretty crude, and take 30-60 seconds. Is there a way for me to export the desired range as an image with a single click / macro?

 

[Yongle]

Try this
Do not run from VBA editor - run from the workbook

Place code in a module in the workbook containing the table

Sub CopyRangeToJpeg()
    Dim aChart As Chart, Rng As Range, Path As String
    Path = CreateObject("WScript.Shell").SpecialFolders("Desktop") & "\" & "Capture.jpg"
    Set Rng = Sheets("TABLE").Range("C2:G57")

    Call Rng.CopyPicture(xlScreen, xlPicture)
    With Sheets.Add
        .Shapes.AddChart
        .Activate
        .Shapes.Item(1).Select
        Set aChart = ActiveChart
        .Shapes.Item(1).Line.Visible = msoFalse
        .Shapes.Item(1).Width = Rng.Width
        .Shapes.Item(1).Height = Rng.Height
        aChart.Paste
        aChart.Export (Path)
        Application.DisplayAlerts = False
        .Delete
        Application.DisplayAlerts = True
    End With
    MsgBox "Saved to " & vbCr & Path, vbInformation, ""
End Sub

 

[d0rian]

This is awesome, thanks so much -- seems to do exactly what I want! Quick Q's:
- My file is one I've spent (literally) years optimizing for performance, and I'm always careful not to add code that might bloat / slow it down in any respect. Can you just briefly tell me what exactly this code is adding? When I run it, I see the screen flicker briefly, and reading the VBA it appears that it's adding a Sheet (or a Shape, or both, I'm not sure); I just want to make sure that whatever it's adding is getting removed in its entirety at the end of the code. Is that the case? (Is that what the .Delete line near the end does?) Just want to be sure that running it repeatedly isn't going to be bloating the file progressively over time.
- You said not to run it "from the workbook", but rather "in a module"...I'm not clear on the difference; all my existing code resides in modules in my workbook, so I just opened the VBA editor and pasted it into one of the existing modules, and created a shape/button in the workbook which runs the newly-added code. What did you mean when you wrote not to "run it from the workbook"?

Thanks again, this is really really helpful!

 

[Yongle]

    With Sheets.Add
        ...
        .Delete
    End With

No bloat

Why ?
- the table's image is taken
- a worksheet is added
- a chart is added in the NEW worksheet
- the table image is pasted into the chart
- the chart is exported as a jpg
- the ADDED worksheet (including the ADDED chart) is deleted

What did you mean when you wrote not to "run it from the workbook"?

I said quite the opposite
The code may fail if run from the VBA editor, which is why I wrote Do not run from VBA editor
and emphasised that by writing Run from the workbook

apologies for any confusion

 

[Gabriel222]

Old thread, but for anyone out there, this worked great for me, and beat some of the AIs out there !