Consulting

Page 1 of 2 1 2 LastLast
Results 1 to 20 of 22

Thread: Solved: Sendkeys?

  1. #1
    VBAX Regular
    Joined
    Jun 2005
    Posts
    11
    Location

    Question Solved: Sendkeys?

    I work for an e-learning company which has recently started automated testing.

    The tool uses VBA to read, interpret, and execute scripts that are stored as rows of instructions in an Excel spreadsheet.

    When errors occur they would like a screenshot taken. I have heard of SendKeys in VB, but don't know if VBA supports this. Even so, I'm not certain I understand enough to be able to re-acquire the file in the clipboad to be able to save it to disk. The FileIO for VB I'll remember once I start looking at it again, I'll just need to set a filepath and name programmatically I think.

    So the question is 'How can I take a screenshot, and save it to disk from the clipboard?' using VBA, if it is supported

  2. #2
    Administrator
    VP-Knowledge Base
    VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,478
    Location
    Hi,
    Welcome to VBAX.
    Don't know about the second part of your question, but for the screen capture:
    From
    http://word.mvps.org/faqs/MacrosVBA/PrtSc.htm



    To capture the screen

    [VBA] 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
    End Sub



    [/VBA]
    To capture the active window [VBA]

    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 KEYEVENTF_KEYUP = &H2
    Private Const VK_SNAPSHOT = &H2C
    Private Const VK_MENU = &H12

    Sub AltPrintScreen()
    keybd_event VK_MENU, 0, 0, 0
    keybd_event VK_SNAPSHOT, 0, 0, 0
    keybd_event VK_SNAPSHOT, 0, KEYEVENTF_KEYUP, 0
    keybd_event VK_MENU, 0, KEYEVENTF_KEYUP, 0
    End Sub

    [/VBA]
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  3. #3
    VBAX Regular
    Joined
    Jun 2005
    Posts
    11
    Location

    Exclamation

    Hi,

    Thanks for the information. Now that I've thought about this for some additional time, I think if I could just capture to the clipboard, then take that clipboard image and paste it into a MS Word doc I'd be ok. So, if anyone has any info on this, please post, because after 5 hours of looking for this info on the web, I'm sure I could *PRINT* this data, but that is not the goal.

    Thanks all,

    -J

  4. #4
    Administrator
    VP-Knowledge Base VBAX Guru MOS MASTER's Avatar
    Joined
    Apr 2005
    Location
    Breda, The Netherlands
    Posts
    3,281
    Location
    Hi,

    For the API approach you have to consider the OS that the user is running.

    This code will do the screen capture and paste it in a newly created word document.
    The Word document is saved in same location as the workbook you execute the code from.

    The code:[VBA]
    'API Code by Colo
    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_LMENU = &HA4
    Private Const VK_SNAPSHOT = &H2C
    Private Const VK_CONTROL = &H11
    Private Const VK_V = &H56
    Private Const VK_0x79 = &H79
    Private Const KEYEVENTF_EXTENDEDKEY = &H1
    Private Const KEYEVENTF_KEYUP = &H2

    Sub ScreenCaptureSave()
    Dim sAppOs As String
    Dim oApp As Object

    'get oparating system
    sAppOs = Application.OperatingSystem
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False

    If Mid(sAppOs, 18, 2) = "NT" Then
    ' WinNT,Windows2000,WindowsXP - Using Win32API
    Call keybd_event(VK_LMENU, VK_V, KEYEVENTF_EXTENDEDKEY, 0)
    Call keybd_event(VK_SNAPSHOT, VK_0x79, KEYEVENTF_EXTENDEDKEY, 0)
    Call keybd_event(VK_LMENU, VK_V, KEYEVENTF_EXTENDEDKEY Or KEYEVENTF_KEYUP, 0)
    Call keybd_event(VK_SNAPSHOT, VK_0x79, KEYEVENTF_EXTENDEDKEY Or KEYEVENTF_KEYUP, 0)
    Else
    ' Windows95,Windows98,WindowsME
    Call keybd_event(VK_SNAPSHOT, 0, KEYEVENTF_EXTENDEDKEY, 0)
    Call keybd_event(VK_SNAPSHOT, 0, KEYEVENTF_EXTENDEDKEY Or KEYEVENTF_KEYUP, 0)
    End If

    DoEvents
    Set oApp = CreateObject(Class:="Word.Application")

    With oApp
    .Documents.Add
    .Selection.Paste
    DoEvents
    .ActiveDocument.SaveAs Filename:=ThisWorkbook.Path & "\myfile.doc"
    .ActiveDocument.Close False
    .Quit False
    End With

    Set oApp = Nothing

    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    MsgBox "Have a look at" & ThisWorkbook.Path & "\myfile.doc"
    End Sub
    [/VBA]

    Enjoy!
    _________
    Groetjes,

    Joost Verdaasdonk
    M.O.S. Master

    Mark your thread solved, when it has been, by hitting the Thread Tools dropdown at the top of the thread.
    (I don't answer questions asked through E-mail or PM's)

  5. #5
    VBAX Regular
    Joined
    Jun 2005
    Posts
    11
    Location
    wow..thanks!

    you guys are great!

    be safe all.

    -J

  6. #6
    VBAX Regular
    Joined
    Jun 2005
    Posts
    11
    Location
    Bah, for some reason I'm getting an error. Something about 'Only comments can follow end sub, end function, yada yada yada...'

    It won't compile. I've looked at it and there doesn't seem to be any issue with the code that I can see. Is it possible that VBA won't support this? I'll get someone else to look at the code later to make sure that I'm not missing something.

    Thanks

    -J

  7. #7
    Administrator
    VP-Knowledge Base
    VBAX Guru MOS MASTER's Avatar
    Joined
    Apr 2005
    Location
    Breda, The Netherlands
    Posts
    3,281
    Location
    Quote Originally Posted by Jadawin
    wow..thanks!

    you guys are great!

    be safe all.

    -J
    You're most welcome!
    _________
    Groetjes,

    Joost Verdaasdonk
    M.O.S. Master

    Mark your thread solved, when it has been, by hitting the Thread Tools dropdown at the top of the thread.
    (I don't answer questions asked through E-mail or PM's)

  8. #8
    Administrator
    VP-Knowledge Base
    VBAX Guru MOS MASTER's Avatar
    Joined
    Apr 2005
    Location
    Breda, The Netherlands
    Posts
    3,281
    Location
    Quote Originally Posted by Jadawin
    Bah, for some reason I'm getting an error. Something about 'Only comments can follow end sub, end function, yada yada yada...'

    It won't compile. I've looked at it and there doesn't seem to be any issue with the code that I can see. Is it possible that VBA won't support this? I'll get someone else to look at the code later to make sure that I'm not missing something.

    Thanks

    -J
    Can you attach the file cause this sounds like a simple syntax error?
    _________
    Groetjes,

    Joost Verdaasdonk
    M.O.S. Master

    Mark your thread solved, when it has been, by hitting the Thread Tools dropdown at the top of the thread.
    (I don't answer questions asked through E-mail or PM's)

  9. #9
    VBAX Regular
    Joined
    Jun 2005
    Posts
    11
    Location
    Bah..I'm stupid. It was totally me not moving the 'keyboard_event' stuff to the .bas file.
    I've gotten it up to the point now where I'm now at the point trying to execute:

    With oApp
    .Documents.Add
    .Selection.Paste
    DoEvents
    .ActiveDocument.SaveAs Filename:=ThisWorkbook.Path & "\myfile.doc"
    .ActiveDocument.Close False
    .Quit False
    End With

    I Error out at > .ActiveDocument.SaveAs Filename:=ThisWorkbook.Path & "\myfile.doc"

    Run Time Error '424'
    Object required.

    ***
    I did a ctrl - v, to paste what was in the clipboard to an instance of MSWord I opened, and it did paste something, but I'm unsure that what is there is what should be. I'll play around with it a bit. Prolly reboot and see what happens.

  10. #10
    Administrator
    VP-Knowledge Base
    VBAX Guru MOS MASTER's Avatar
    Joined
    Apr 2005
    Location
    Breda, The Netherlands
    Posts
    3,281
    Location
    Quote Originally Posted by Jadawin
    I Error out at > .ActiveDocument.SaveAs Filename:=ThisWorkbook.Path & "\myfile.doc"

    Run Time Error '424'
    Object required.
    Hi Jada,

    There's no such thing as being stupid! Keep on trying and you'll get there!

    This error tell's me the code has lost reference to the parent oApp (Being the Word instance you created)

    To avoid that I've recoded the code I made yah: [VBA]
    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_LMENU = &HA4
    Private Const VK_SNAPSHOT = &H2C
    Private Const VK_CONTROL = &H11
    Private Const VK_V = &H56
    Private Const VK_0x79 = &H79
    Private Const KEYEVENTF_EXTENDEDKEY = &H1
    Private Const KEYEVENTF_KEYUP = &H2
    Sub ScreenCaptureSave()
    Dim sAppOs As String
    Dim oApp As Object
    Dim oDoc As Object
    'get oparating system
    sAppOs = Application.OperatingSystem
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    If Mid(sAppOs, 18, 2) = "NT" Then
    ' WinNT,Windows2000,WindowsXP - Using Win32API
    Call keybd_event(VK_LMENU, VK_V, KEYEVENTF_EXTENDEDKEY, 0)
    Call keybd_event(VK_SNAPSHOT, VK_0x79, KEYEVENTF_EXTENDEDKEY, 0)
    Call keybd_event(VK_LMENU, VK_V, KEYEVENTF_EXTENDEDKEY Or KEYEVENTF_KEYUP, 0)
    Call keybd_event(VK_SNAPSHOT, VK_0x79, KEYEVENTF_EXTENDEDKEY Or KEYEVENTF_KEYUP, 0)
    Else
    ' Windows95,Windows98,WindowsME
    Call keybd_event(VK_SNAPSHOT, 0, KEYEVENTF_EXTENDEDKEY, 0)
    Call keybd_event(VK_SNAPSHOT, 0, KEYEVENTF_EXTENDEDKEY Or KEYEVENTF_KEYUP, 0)
    End If

    DoEvents
    Set oApp = CreateObject(Class:="Word.Application")
    Set oDoc = oApp.Documents.Add
    oApp.Selection.Paste
    DoEvents
    oDoc.SaveAs Filename:=ThisWorkbook.Path & "\myfile.doc"
    oDoc.Close False
    oApp.Quit False

    Set oDoc = Nothing
    Set oApp = Nothing

    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    MsgBox "Have a look at" & ThisWorkbook.Path & "\myfile.doc"
    End Sub
    [/VBA]

    See Attachment.
    I now set reference to a Document object so it won't loose it's reference that easily.

    Enjoy!
    _________
    Groetjes,

    Joost Verdaasdonk
    M.O.S. Master

    Mark your thread solved, when it has been, by hitting the Thread Tools dropdown at the top of the thread.
    (I don't answer questions asked through E-mail or PM's)

  11. #11
    VBAX Regular
    Joined
    Jun 2005
    Posts
    11
    Location
    ARGH!

    I'm sitting there looking at the code and realize it should be oApp.path...

    /sigh I really need a swift kick.

    Uhm..hrm..I just see that you had posted as well. I'll probably use that instead.

    Thanks again for all the help.

    Oh..btw..is this snapshot fullpage or active window?


    -J

  12. #12
    VBAX Regular
    Joined
    Jun 2005
    Posts
    11
    Location
    Oh Mos'M By the way,

    > sAppOs = Application.OperatingSystem

    Errored out for me as well, as unsupported.
    Since I know the platform will stay as 'NT' based, I just commented out all the logic trying to determine platform.

    I'm thinking I'm using some gimp or non standard version of VBA. Possibly older then dirt as well. Currently the code is running and I think it's taken a couple of screenies. I've modified the name a bit so that with the looping construct in the app I'll not overwrite the same file. I'll let you know soon.

    Again, my thanks.

    -J

  13. #13
    Administrator
    VP-Knowledge Base
    VBAX Guru MOS MASTER's Avatar
    Joined
    Apr 2005
    Location
    Breda, The Netherlands
    Posts
    3,281
    Location
    Quote Originally Posted by Jadawin
    ARGH!

    I'm sitting there looking at the code and realize it should be oApp.path...

    /sigh I really need a swift kick.

    Uhm..hrm..I just see that you had posted as well. I'll use that as well.

    Thanks again for all the help.
    -J
    HUH....Are you working from withing VB???

    Cause that's not how you do it in VBA.
    _________
    Groetjes,

    Joost Verdaasdonk
    M.O.S. Master

    Mark your thread solved, when it has been, by hitting the Thread Tools dropdown at the top of the thread.
    (I don't answer questions asked through E-mail or PM's)

  14. #14
    Administrator
    VP-Knowledge Base
    VBAX Guru MOS MASTER's Avatar
    Joined
    Apr 2005
    Location
    Breda, The Netherlands
    Posts
    3,281
    Location
    Quote Originally Posted by Jadawin
    Oh Mos'M By the way,

    > sAppOs = Application.OperatingSystem
    Again, my thanks.

    -J
    Aaaaaah mist this one..I shouldn't reply direct link from mail!

    Yes you are using VB I presume...
    Well of course your welcome.
    _________
    Groetjes,

    Joost Verdaasdonk
    M.O.S. Master

    Mark your thread solved, when it has been, by hitting the Thread Tools dropdown at the top of the thread.
    (I don't answer questions asked through E-mail or PM's)

  15. #15
    Administrator
    VP-Knowledge Base
    VBAX Guru MOS MASTER's Avatar
    Joined
    Apr 2005
    Location
    Breda, The Netherlands
    Posts
    3,281
    Location
    Quote Originally Posted by Jadawin
    Oh Mos'M By the way,

    > sAppOs = Application.OperatingSystem
    Oh btw in VBA that would be:[VBA]
    System.OperatingSystem[/VBA]
    _________
    Groetjes,

    Joost Verdaasdonk
    M.O.S. Master

    Mark your thread solved, when it has been, by hitting the Thread Tools dropdown at the top of the thread.
    (I don't answer questions asked through E-mail or PM's)

  16. #16
    Administrator
    VP-Knowledge Base
    VBAX Guru MOS MASTER's Avatar
    Joined
    Apr 2005
    Location
    Breda, The Netherlands
    Posts
    3,281
    Location
    Quote Originally Posted by Jadawin
    I Error out at > .ActiveDocument.SaveAs Filename:=ThisWorkbook.Path & "\myfile.doc"
    I did a ctrl - v, to paste what was in the clipboard to an instance of MSWord I opened, and it did paste something, but I'm unsure that what is there is what should be. I'll play around with it a bit. Prolly reboot and see what happens.
    Have been answering to much questions today not sharp anymore!

    You said I've used oApp.Path. (Which returns the path of the parent application)

    I've missed the Ctrl-v part in to an instance of Word.
    My code presumes you are automating from within EXCEL...And ThisWorkbook.Path returns the path of the workbook you execute the code from.

    The equivelant in Word would be: ActiveDocument.Path Or ThisDocument.Path

    But if this is running from within Word then the Whole code could be changed for a better performance. All Late binding stuff can come out.

    Do you want a recode for automation within WORD?
    _________
    Groetjes,

    Joost Verdaasdonk
    M.O.S. Master

    Mark your thread solved, when it has been, by hitting the Thread Tools dropdown at the top of the thread.
    (I don't answer questions asked through E-mail or PM's)

  17. #17
    VBAX Regular
    Joined
    Jun 2005
    Posts
    11
    Location
    Quote Originally Posted by MOS MASTER
    HUH....Are you working from withing VB???

    Cause that's not how you do it in VBA.
    I was *TOLD* this was VBA, and that there was not much difference between
    VB and VBA. Or that they were minor. I dunno, really.

    Oh..and I was looking at something and editted the two previous posts before this one. I'll look back at the code again. Is there some tell-tell sign that will for sure identify what I'm working with? VB/VBA?

    Thanks again

    -J

  18. #18
    Administrator
    VP-Knowledge Base
    VBAX Guru MOS MASTER's Avatar
    Joined
    Apr 2005
    Location
    Breda, The Netherlands
    Posts
    3,281
    Location
    Ok just for the record.

    Which App are you pasting your code in? (In the Editor ALT+F11 that is)
    Are you using a separate program that's not part of the Office suit? (That could be VB6.0)

    I do think you're in Office VBA buth I'm seeing clouds right now...
    _________
    Groetjes,

    Joost Verdaasdonk
    M.O.S. Master

    Mark your thread solved, when it has been, by hitting the Thread Tools dropdown at the top of the thread.
    (I don't answer questions asked through E-mail or PM's)

  19. #19
    VBAX Regular
    Joined
    Jun 2005
    Posts
    11
    Location
    Quote Originally Posted by MOS MASTER
    Have been answering to much questions today not sharp anymore!

    You said I've used oApp.Path. (Which returns the path of the parent application)

    I've missed the Ctrl-v part in to an instance of Word.
    My code presumes you are automating from within EXCEL...And ThisWorkbook.Path returns the path of the workbook you execute the code from.

    The equivelant in Word would be: ActiveDocument.Path

    But if this is running from within Word then the Whole code could be changed for a better performance. All Late binding stuff can come out.

    Do you want a recode for automation within WORD?
    Naw man, *I* used oApp.Path, which you indicate is very likely incorrect. Btw, the automation app is parcing out rows from Excel, to use as instructions, which then are sent to an engine to execute the test<s>. It all *LOOKS* like VB to me, but could be VBA. I was TOLD it was, I'll I'm doin is straight coding to a file (similar to txt or doc) and hoping it works usually. I have *NO* intelli-sense or helper files. Which is why I was asking for help.

    I *AM* getting some screenshots now. They are the active window, which in the case of a login failure doesn't really matter if it's full screen or not. In other failures I'm thinking there won't be a popup window so I'll get full window. I'll relook over what you have sent, it should do the trick. And my apologies for sending you confusing replies.

    Btw, scan some of my last previous posts. I made some edits and thinking back I'd have done better to just repost. I think that will clarify some of the confusion.

    Thanks again.

    -J

  20. #20
    Administrator
    VP-Knowledge Base
    VBAX Guru MOS MASTER's Avatar
    Joined
    Apr 2005
    Location
    Breda, The Netherlands
    Posts
    3,281
    Location
    Please no appoligies..I'm just tired I guess.

    Well I still don't get how your automation process is running but if its running then that's just fine by me. (If problems arise just howler and tell me which app is preforming the automation because vba can differ between apps)

    You're welcome and I'll see yah again..
    _________
    Groetjes,

    Joost Verdaasdonk
    M.O.S. Master

    Mark your thread solved, when it has been, by hitting the Thread Tools dropdown at the top of the thread.
    (I don't answer questions asked through E-mail or PM's)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •