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.
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
- 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