2

Is it possible to take screenshots of all sheets in Excel (by a script for example)?

Look at this Example. I want somehow export the data from the green rectangle to the JPG (or another format) and do this for all sheets.

I found this solution: excel vba: to export multiple excel sheet into its designated jpg file and it looks quite good but there is set range Range("B5:M60"). Is it possible to do it more dynamically?

1 Answer 1

2

I have slightly modified the mentioned code to do the following.

Loop thru all Sheets

Ask for use Input Range to Capture the Screenshot

Save the Sheetname.jpg at C:\Temp folder. File name is Sheetname with .jpg extension

Do note that this won't capture any embedded objects e.g. an Inserted Shapes or a Picture etc. It will only capture the screenshot of Range of Cells you input for each and every sheet. Moreover no strict validations are in place. If you just cancel the Input, code will result into a run time error.

The code is as follows. Do check if this meets your requirement. Original Code is at this SU Post

Sub exportpic()

Dim WS As Worksheet
Dim rgExp As Range
Dim CH As ChartObject


   For Each WS In ThisWorkbook.Sheets
            WS.Activate
            Set rgExp = Application.InputBox(prompt:="Select Input Range", Type:=8)
            rgExp.CopyPicture Appearance:=xlScreen, Format:=xlBitmap
            Set CH = WS.ChartObjects.Add(Left:=rgExp.Left, Top:=rgExp.Top, Width:=rgExp.Width, Height:=rgExp.Height)
            CH.Chart.ChartArea.Select
            CH.Chart.Paste
            CH.Chart.Export "C:\Temp\" & WS.Name & ".jpg"
            CH.Delete


    Next WS

End Sub
3
  • Thanks! It looks very good. One more thing: is it possible enable copy & paste in that input box? Oct 24, 2017 at 16:16
  • I am not too sure what you exactly mean by Copy-Paste. When you Right Click on the text box, if there's something in Clipboard 'Paste' option should appear.
    – patkim
    Oct 24, 2017 at 16:22
  • Yes, sorry. I tested it on sheets that were read-only and it was disabled. Now it's ok. Oct 24, 2017 at 16:29

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .