CODEWITHSUNDEEP
excelvbascreenshotpowerview
Amit Kohli
Amit Kohli

Reputation: 2950

How can I capture PowerView image?

I am generating Excel PowerView dashboards. I thought it would be useful for users that only want a quick view to see a screenshot of the dashboard, with a link where more analytical people can download the excel file to repivot or drill down to their heart's contentment.

I found a macro that allows me to capture the screenshot and save it to a local file, but the screenshot is my entire desktop, or active application. Both of these look terrible, I need to capture ONLY the PowerView (see image below): enter image description here

Is there any way to capture only certain parts of the screen from VBA?

Upvotes: 0

Views: 389

Answers (1)

Amit Kohli
Amit Kohli

Reputation: 2950

crickets....

Ok, I eventually figured it out:

Step 1) Install [Imagemagick][1] on your computer. MAKE SURE you include the COM component so that you can use it from VBA.

Step 2) Register the Reference in VBE (Tools -> References -> Find the ImageMagick and enable it)

Step 3) Close excel and reopen it.

Step 4) Create the following macro in VBA, and assign it to a button on your quicklaunch bar.

Sub AltPrintScreen()
Dim a, b

    ' First, take a screenshot and save it
    a = Replace(ActiveWorkbook.Name, ".xlsx", "")
    Set b = ActiveWorkbook
    keybd_event VK_MENU, 0, 0, 0
    keybd_event VK_SNAPSHOT, 0, 0, 0
    keybd_event VK_SNAPSHOT, 0, KEYEVENTF_KEYUP, 0
    keybd_event VK_MENU, 0, KEYEVENTF_KEYUP, 0

    Dim chtObj As ChartObject

    With b.Worksheets(1)

    .Activate
    
    ' My resolution is 1366 x 768, adjust this part as per ur rez
    Set chtObj = .ChartObjects.Add(100, 30, 1000, 568)
    chtObj.Name = "TemporaryPictureChart"

    ActiveSheet.ChartObjects("TemporaryPictureChart").Activate
    ActiveChart.Paste

    ActiveChart.Export Filename:=ThisWorkbook.Path & "\" & a & ".jpg", FilterName:="jpg"
End With
chtObj.Delete

'Next, use ImageMagick to crop the image to get only the part you want.
Dim img
Set img = CreateObject("ImageMagickObject.MagickImage.1")
img.Convert ThisWorkbook.Path & "\" & a & ".jpg", "-crop", "845x645+270+62^", "+repage", ThisWorkbook.Path & "\" & a & ".jpg"
Set img = Nothing
Sheets("Power View1").Activate
    
End Sub

Step 5) Then, from the sheet you want to screenshoot, click on the little button that runs the macro. that should do it (although you'll probably have to tweak the parameters).

SIDE NOTE: It would have been so much easier to go from the screenshot to ImageMagick directly, like this:

Dim a, img
a = Replace(ActiveWorkbook.Name, ".xlsx", "")
Selection.CopyPicture xlScreen, xlBitmap
Set img = CreateObject("ImageMagickObject.MagickImage.1")
img.Convert "clipboard:myimage", "-crop", "845x645+270+62^", "+repage", ThisWorkbook.Path & "\" & a & ".jpg"

But unfortunately, you can't capture a range in a PowerView tab. Also, make sure to use xlBitmap if you're using ImageMagick, since it doesn't recognize xlPicture (not a bitmap). [1]: https://www.imagemagick.org/script/download.php

Upvotes: 0

Related Questions