调用屏幕截图

ggmmlol

wynnq 发表于 2019-9-29 17:00
哦，我还不知道这个问题。因为使用VBA模拟按键总是爱出问题，所以不想用，才想找内部代码的这个功能。
...

有了WshShell.SendKeys，那么，在利用Excel的内置功能时，是使用内部代码，还是模拟按键，就完全取决于代码的效率了。
通常情况下，模拟按键的效率相对较低，因为很多功能都需要“先选择对象”，再执行“方法”动作，而内部代码，可以省略前一步骤。但是，对于需要交互的操作，内部代码并无优势，这时，模拟按键反而有优势了，因为它是不会影响“撤销、恢复”功能的，而内部代码执行同一命令时，总会造成不能“撤消”。

所以，就本问题来说，模拟按键是比内部代码更优的解决办法。

takeeasy5

https://stackoverflow.com/questi ... c-area-in-same-file

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!

enter image description here

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.

You could try Application.Wait. This method seems to work when I test it, but I'd caution that it's also unreliable.
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
share  edit  follow
edited May 17 '17 at 17:10
answered May 11 '17 at 0:16

David Zemens
50.5k1010 gold badges6868 silver badges115115 bronze badges
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? – loco May 11 '17 at 7:06
use SendKeys for both monitors, instead of keybd_event. otherwise all code remains the same – David Zemens May 11 '17 at 8:28
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? – loco May 17 '17 at 14:19
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 May 17 '17 at 14:35
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. – loco May 17 '17 at 14:50