Print Screen Automation – Take Screenshot, Save to File and Attach in Excel

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.

1. Press Alt+Tab to activate next window in queue.
2. Press Alt+Print Screen button.
3. Paste it in MSPAINT and save it as Image file.
4. 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).

Step 3:
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.

Step 4:
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?

Limitations:

[adsense]

1. 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.
2. 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().

More Tricks:

• Import Emails from Outlook to Excel.
• Password Protect Office Documents – Simplest Method
• Read List of Folder Names and Folder Size with Excel Macro

Additional Reference – Print Screen Automation:

• http://msdn.microsoft.com/en-us/library/windows/desktop/dd375731(v=vs.85).aspx
• http://stackoverflow.com/questions/18232987/export-pictures-from-excel-file-into-jpg-using-vba