Using Excel VBA Macro To Capture + Save Screenshot of Specific Area In Same File

vba code to take screenshot and paste in excel
how to automate excel reports using macros
record macro excel 2016
excel vba save screenshot as jpg
excel macro that inserts data with a button
vba code to take screenshot and paste in word
how to record a macro in excel
how to create a calculator in excel using macros

I'm trying to create a macro which uses an ActiveX control button (click) to take a screenshot of my desktop screen and save it within the same excel sheet as the button. How can I create a screenshot 800x600 in size (not full desktop view) and then have it pasted into the left hand side of the same sheet as the button? I have tried this numerous ways including sendkeys (simplest).

I saved the capture process in a module:

Sub PasteScreenShot()
Application.SendKeys "({1068})"
ActiveSheet.Paste
End Sub

And then call the sub in the ActiveX button code. The capture works, but I cannot figure out a way to manipulate its area grab or its pasted location on the sheet.

I am trying to automate with buttons rather than using the snipping tool.

Without using SendKeys
Option Explicit

Private Declare Sub keybd_event Lib "user32" (ByVal bVk As Byte, ByVal _
  bScan As Byte, ByVal dwFlags As Long, ByVal dwExtraInfo As Long)

Private Const VK_SNAPSHOT = &H2C

Sub PrintScreen()
    keybd_event VK_SNAPSHOT, 1, 0, 0
    ActiveSheet.Paste
End Sub

However, with this approach if you are using multiple monitors, it will only capture the active monitor, so further effort needs to be made if you need to capture the other monitor (this can probably be done with API calls but I haven't gotten that far).

NB: The AppActivate statement can be used to activate another (non-Excel) application and if you do this, then the keybd_event function will only capture that application, e.g;

AppActivate "Windows Command Processor" 'Modify as needed
keybd_event VK_SNAPSHOT, 1, 0, 0
ActiveSheet.Paste
Using SendKeys, Problem Solved:

While SendKeys is notoriously flaky, if you need to use this method due to limiations of the API method described above, you might have some problems. As we both observed, the call to ActiveSheet.Paste was not actually pasting the Print Screen, but rather it was pasting whatever was previously in the Clipboard queue, to the effect that you needed to click your button to call the macro twice, before it would actually paste the screenshot.

I tried a few different things to no avail, but overlooked the obvious: While debugging, if I put a breakpoint on ActiveSheet.Paste, I was no longer seeing the problem described above!

This tells me that the SendKeys is not processed fast enough to put the data in the Clipboard before the next line of code executes, to solve that problem there are two possible solutions.

  1. You could try Application.Wait. This method seems to work when I test it, but I'd caution that it's also unreliable.
  2. A better option would be DoEvents, because it's explicitly designed to handle this sort of thing:

DoEvents passes control to the operating system. Control is returned after the operating system has finished processing the events in its queue and all keys in the SendKeys queue have been sent.

This works for me whether I run the macro manually from the IDE, from the Macros ribbon, or from a button Click event procedure:

Option Explicit
Sub CopyScreen()

Application.SendKeys "({1068})", True
DoEvents
ActiveSheet.Paste

Dim shp As Shape
With ActiveSheet
    Set shp = .Shapes(.Shapes.Count)
End With

End Sub
How To Position, Resize & Crop the Image:

Regardless of which method you use, once the picture has been pasted using ActiveSheet.Paste it will be a Shape which you can manipulate.

To Resize: once you have a handle on the shape, just assign its Height and Width properties as needed:

Dim shp As Shape
With ActiveSheet
    Set shp = .Shapes(.Shapes.Count)
End With
shp.Height = 600
shp.Width = 800

To Position It: use the shape's TopLeftCell property.

To Crop It: use the shp.PictureFormat.Crop (and/or CropLeft, CropTop, CropBottom, CropRight if you need to fine-tune what part of the screenshot is needed. For instance, this crops the pasted screenshot to 800x600:

Dim h As Single, w As Single
h = -(600 - shp.Height)
w = -(800 - shp.Width)

shp.LockAspectRatio = False
shp.PictureFormat.CropRight = w
shp.PictureFormat.CropBottom = h

Using Excel VBA Macro To Capture + Save Screenshot of Specific , A macro is a code written in VBA (Visual Basic for Applications) that allows you to run a chunk of code whenever it is executed. Often, you will find people (� To run the VBA Macro click the Macros button to open the Macros window. Select the VBA Macro and click Run All that is left is to select the VBA Macro from the Macro window and to hit Run. Lastly name your destination file and hit Save There may a short pause for the macro to process the image, shortly after the Save As file dialog should appear.

How to Record a Macro in Excel, In this tutorial you'll take these first steps in using Excel VBA: Understand what Excel can do without macros; Think of a simple, repetitive, Excel� An excel macro is a set of VBA commands and functions that automates the tasks you do in excel. Set of VBA instructions = Macro = Automated task The best part is you don’t have to know anything about VB or VBA to automate a task you do in excel or to create excel macros.

Sub SavePicToFile(namefile)
 Selection.CopyPicture xlScreen, xlBitmap
 Application.DisplayAlerts = False
 Set tmp = Charts.Add
 On Error Resume Next
 With tmp
    .SeriesCollection(1).Delete
    .Width = Selection.Width
    .Height = Selection.Height
    .Paste
    .Export filename:=namefile, Filtername:="jpeg"
    .Delete
 End With
End Sub
foto = Application.ActiveWorkbook.Path & "\Foto" & ".jpeg"
ActiveWorkbook.Sheets(1).Range("A1:Z30").Select
SavePicToFile (foto)

How to Record and Test Excel Macro, I'm going to share with you the steps to just get that code into your Excel file and running. We aren't going to do any coding, just copy/pasting. Excel VBA Advanced Filter – A Complete Guide (Video) The Ultimate Guide to using VBA with Excel Tables (Video) Protected: Delete Me; The Ultimate Guide to VBA Arrays (Video) Recent Comments. Paul Kelly on VBA Tutorial – The Ultimate Guide for Beginners; Paul Kelly on Excel VBA Copy – The Complete Guide to Copying Data

How To Use A VBA Macro You Found On The Internet — The , The tutorial covers the essential basics of Excel macros. You will find how to record a macro and insert VBA code in Excel, copy macros from� Personal Macro Workbook is a file which can be the common storage place to store your all macros and use them. Although it’s a hidden file which opens when you start Excel in your system. And you don’t need to open it separately when you want to access any of the codes from it.

Excel VBA macro tutorial for beginners with examples, How to insert and run VBA code in Excel - tutorial for beginners Then add the following lines to get your macro to work faster (see the� Now, the user can see the Macro button as shown below. Clicking on that icon opens the Record Macro window. User can give a name to the Macro and press ok. Now the macro is recording. After completing the task, the user can go below the Excel sheet and click on that Macro button to stop recording the macro.

Insert and run VBA macros in Excel - step-by-step guide, You can use the macro recorder to record the macro or write your own macro script in task, you can record a macro with the Macro Recorder in Microsoft Excel. Macros and VBA tools can be found on the Developer tab, which is hidden by� We have created “GetComputerUserName” macro to find the computer and user name. We can run the macro by clicking “Submit” button. Macro will extract computer name in cell C10 and user name in cell C11. Code explanation. Environ Function. Environ function returns the string value associated with environment variable of the operating system.

Comments
  • David Zemens, I have not been able to test your solution yet. However, I failed to mention in my initial thread that it would be for a dual screen setup (thank you for reminding me). Hence, it would need to work when my excel sheet was in the right monitor and my desktop view would be in the left. Could you please advise?
  • use SendKeys for both monitors, instead of keybd_event. otherwise all code remains the same
  • David Zemens, I apologise for my late acknowledgement of your solution. If you have time: Every time I execute the above code (using sendkeys for the left monitor screenshot), the screenshot only refreshes the content on the left monitor after the button has been clicked twice. I.e the macro is not refreshing with the new left screen content until I do two clicks (I would like a single click for a new screenshot). It captures the old content. Is there a way to reset the macro's state every time the button is clicked so that I am always given the most current information on my screen?
  • Not sure I understand the problem but try adding Application.CutCopyMode = False at the beginning of the macro, this should clear the clipboard in Excel. If that doesn't do it, which of the two monitors is primary? (There is not really a "left" or "right", just a primary and secondary, which are oriented left & right)
  • David Zemens, "Primary" is the left monitor i.e the view which I want to capture screenshot info from. "Secondary" is right i.e the excel file view that I want the screenshot to be pasted into when I click the screenshot button. The contents of the clipboard does not refresh after each button click. It takes two button clicks for the new content in the Primary screen to be captured. I hope this is clearer. I tried your solution within the module sub at the top and also within the sheet sub I use to execute the module. Neither worked. Does this make sense? Thanks.
  • Could you please elaborate how and why this solves the OPs issue?
  • It should be modified, i just tried to offer another way to capture a screenshot of a selected range as a picture instead of using "sendkeys".