One Click – Save Screenshot to an Image File and Attach in Excel.
This topic explains some basics about creating a Print screen Automation Tool in Excel VBA Macro.
Lets’ assume, Excel Sheet is active window in your desktop. You want to take a screen shot of a window at background and attach screenshot image in excel as an object.
How to do it the Manual Way? – Take Print screen and Attach to Excel.
- Press Alt+Tab to activate next window in queue.
- Press Alt+Print Screen button.
- Paste it in MSPAINT and save it as Image file.
- From Excel add object (shortcut Alt+I+O) and add the image.
Also Read: How to create User Defined Functions in Excel – Create Your own Excel Formulas and Functions ?
How to do it in VBA? – Each Step is coded.
Explain each Manual Step to Excel in the language it understands, giving us a Automation Tool.
Step 1 and 2:
Windows API function Keybd_Event function is used to automate keystrokes for the Keys ALT, TAB, PRINTSCREEN etc.,. Virtual Key Codes of each Key Press and Release is passed as parameter to this function. (To get a complete list of Virtual Key Codes available refer the MSDN link in additional references).
There is a deviation in process here. Instead of pasting the screen shot in MSPAINT we will be pasting it in Excel Chart and export it as image file.
To get code for this step, you can record a macro on your own for attaching an object and use it or use the code in the function “Attach_File()” which is also a code that I recorded and edited.
Note: Refer the limitations listed after the code, to avoid the thought ‘It is not working as I expected’.
'Declare Windows API Functions Private Declare Sub keybd_event Lib "user32" (ByVal bVk As Byte, ByVal _ bScan As Byte, ByVal dwFlags As Long, ByVal dwExtraInfo As Long) 'Declare Virtual Key Codes Private Const VK_SNAPSHOT = &H2C Private Const VK_KEYUP = &H2 Private Const VK_MENU = &H12 Public Const VK_TAB = &H9 Public Const VK_ENTER = &HD Sub ScreenPrint() 'Press Alt + TAB Keys -- Step1 Alt_Tab 'Press Print Screen key using Windows API -- Step2. keybd_event VK_SNAPSHOT, 1, 0, 0 'Print Screen key down keybd_event VK_SNAPSHOT, 1, VK_KEYUP, 0 'Print key Up - Screenshot to Clipboard 'Paste Image in Chart and Export it to Image file. -- Step3 Charts.Add ThisWorkbook.Charts(1).AutoScaling = True ThisWorkbook.Charts(1).Paste ThisWorkbook.Charts(1).Export Filename:="E:\ClipBoardToPic.jpg", FilterName:="jpg" 'Supress warning message and Delete the Chart Application.DisplayAlerts = False ThisWorkbook.Charts(1).Delete Application.DisplayAlerts = True 'Attach the file to Excel -- Step4 Attach_File ThisWorkbook.Activate ThisWorkbook.Sheets(1).Select End Sub Sub Alt_Tab() DoEvents keybd_event VK_MENU, 1, 0, 0 'Alt key down DoEvents keybd_event VK_TAB, 0, 0, 0 'Tab key down DoEvents keybd_event VK_TAB, 1, VK_KEYUP, 0 'Tab key up DoEvents keybd_event VK_ENTER, 1, 0, 0 'Tab key down DoEvents keybd_event VK_ENTER, 1, VK_KEYUP, 0 'Tab key up DoEvents keybd_event VK_MENU, 1, VK_KEYUP, 0 'Alt key up DoEvents End Sub Sub Attach_File() ActiveCell.Select ActiveSheet.OLEObjects.Add(Filename:="E:\ClipBoardToPic.jpg", Link:=False, _ DisplayAsIcon:=True, IconFileName:= _ "C:\Program Files\Internet Explorer\iexplore.exe", IconIndex:=10, IconLabel _ :="ClipBoardToPic.jpg").Select End Sub
Also Read: How to lock System with Excel at Scheduled Interval?
- Sometimes if the active window is smaller in size or not in maximized size, then the exported screenshot image will not be proper or it will have Chart displayed at the background. The Automation explained in this article would fail. Refer additional reference to get code for exporting only image that is of actual size of screenshot.
- If this code is run multiple times, then the image will be imported multiple times at the same cell. Change the destination cell to import the image in Attach_File().
- Import Emails from Outlook to Excel.
- Password Protect Office Documents – Simplest Method
- Read List of Folder Names and Folder Size with Excel Macro
11 thoughts on “Print Screen Automation – Take Screenshot, Save to File and Attach in Excel”
Great macro example! You could also take a screen capture by adding the camera shortcut to the quick access ribbon.
Thanks Nick for the Suggestion. Yes, It would be easy for users to Click if the excel has a GUI Button or Quick Shortcut. I will try to add it.
cannot see the print screen taken, it shows as ClipBoardToPic.jpg and not the actual image. please advise.
This is great! However, I am interested in attaching the screenshot as is on my excel worksheet. Is this possible?
Try this. Instead of calling Attach_File function, use these lines.
I have not tested this. Please reply if it works fine.
Is there a way where we can add a button for capture and stop so that user can take multiple screenshots by clicking on capture and then once he clicks on stop button, all the captured screenshots will be saved to excel? (If possible, 1st 2 screenshots side by side, 2nd 2 screen shots below it, so on in the excel document)
how can i create button to take a screenshot of a Worksheet in Excel with VBA code and then to save it in a specified path, automatic name generator?
Vba code to read images and images extract to excel
I do have 64 Bit Operating system and this code is not working there , as there are some problem while declaring the sub.
i want to take screen shot from SAP.
Please convert the declarations that would be compatible with 64 bit machine. Refer this page to get some hints.