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’.
Visual Basic1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859'Declare Windows API FunctionsPrivate 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 CodesPrivate Const VK_SNAPSHOT = &H2CPrivate Const VK_KEYUP = &H2Private Const VK_MENU = &H12Public Const VK_TAB = &H9Public Const VK_ENTER = &HDSub ScreenPrint()'Press Alt + TAB Keys -- Step1Alt_Tab'Press Print Screen key using Windows API -- Step2.keybd_event VK_SNAPSHOT, 1, 0, 0 'Print Screen key downkeybd_event VK_SNAPSHOT, 1, VK_KEYUP, 0 'Print key Up - Screenshot to Clipboard'Paste Image in Chart and Export it to Image file. -- Step3Charts.AddThisWorkbook.Charts(1).AutoScaling = TrueThisWorkbook.Charts(1).PasteThisWorkbook.Charts(1).Export Filename:="E:\ClipBoardToPic.jpg", FilterName:="jpg"'Supress warning message and Delete the ChartApplication.DisplayAlerts = FalseThisWorkbook.Charts(1).DeleteApplication.DisplayAlerts = True'Attach the file to Excel -- Step4Attach_FileThisWorkbook.ActivateThisWorkbook.Sheets(1).SelectEnd SubSub Alt_Tab()DoEventskeybd_event VK_MENU, 1, 0, 0 'Alt key downDoEventskeybd_event VK_TAB, 0, 0, 0 'Tab key downDoEventskeybd_event VK_TAB, 1, VK_KEYUP, 0 'Tab key upDoEventskeybd_event VK_ENTER, 1, 0, 0 'Tab key downDoEventskeybd_event VK_ENTER, 1, VK_KEYUP, 0 'Tab key upDoEventskeybd_event VK_MENU, 1, VK_KEYUP, 0 'Alt key upDoEventsEnd SubSub Attach_File()ActiveCell.SelectActiveSheet.OLEObjects.Add(Filename:="E:\ClipBoardToPic.jpg", Link:=False, _DisplayAsIcon:=True, IconFileName:= _"C:\Program Files\Internet Explorer\iexplore.exe", IconIndex:=10, IconLabel _:="ClipBoardToPic.jpg").SelectEnd 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
Additional Reference – Print Screen Automation:
Print Screen Automation – Take Screenshot, Save to File and Attach in Excel