-
SendKeys really working
Hi,
I need to automate some Work. currently I'm typing a command in an Application "Amadeus Altea". Than I copy/paste the result in an Excel Sheet, where I do some other analysis there.
For the Analysis I have my macros and everything is working fine once the text got copied from Amadeus to excel.
The thing is Including the command I'm typing in Amadeus is a Date , and I need to repeat each time the same command for every day for the next 3 months. and all this weekly.
So far I managed to copy the command from excel to Amadeus (once it works fine I'll modify it so it generate the date automatic).
My Problem is I got it to work from Excel to Amadeus but not vice versa.
Here the code that work from Excel to Amadeus:
Code:
Sub Amadeus()
ActiveWorkbook.Activate
Sheets(1).Activate
Application.Wait Now + TimeValue("00:00:02")
Range("A1").Activate
Range("A1").Copy
AppActivate "Altea Reservation Desktop"
Application.Wait Now + TimeValue("00:00:02")
SendKeys ("^v")
SendKeys "{ENTER}"
End Sub
But I can't manage to get it work from Amadeus to Excel
Code:
Sub Amadeus()
AppActivate "Altea Reservation Desktop"
Application.Wait Now + TimeValue("00:00:02")
SendKeys ("^a")
SendKeys ("^c")
Application.Wait Now + TimeValue("00:00:01")
AppActivate "Microsoft Excel"
ActiveWorkbook.Activate
Application.Wait Now + TimeValue("00:00:02")
Set S2 = Worksheets("Result")
S2.Activate
Range("A1").Activate
S2.Paste
End Sub
Except the SendKeys ("^v") and SendKeys "{ENTER}" nothing works, I tried by the way SendKeys ("Test") and it did work, but I can't manage SendKeys ("^a") or SendKeys ("^c") to work.
In Amadeus you can hit Shift+Break and you would clear the entire Screen, even SendKeys ("+{BREAK}") didn't work.
I have been told to use Sleep, which I tried, even by adding
Code:
Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
but again nothing worked.
-
Sendkeys is all about timing and focus. Did you turn off UAC?
Something like this might help.
Code:
'http://www.mrexcel.com/forum/showthread.php?p=2872719
'vk_keys, http://msdn.microsoft.com/en-us/library/ms927178.aspx
Declare Sub keybd_event Lib "user32.dll" _
(ByVal bVk As Byte, ByVal bScan As Byte, ByVal dwFlags As Long, ByVal dwExtraInfo As Long)
Sub Test_keybd_event1()
'F5
Key 116
'Ctrl+T
'KeyPlusKey 17, Asc("T")
'or
KeyPlusChar 17, "T"
End Sub
Sub Test_keybd_event2()
'Send Ctrl+O
KeyPlusChar 17, "O" '11h or 17 - vk_control
'Send Shift+Tab
KeyPlusKey 16, 9 '10h or 16 = vk_Shift, 9 = vk_tab
'Send Down and then Up to set focus to first item in FileOpenDlg()
Key 40 '&H28 = 38, vk_down
Key 38 '&H26 = 40, vk_up
End Sub
' Use this to send key command key plus a command key. e.g. Shift+Tab
Sub KeyPlusKey(str1 As Variant, str2 As Variant)
KeyDown str1
Key str2
KeyUp str1
End Sub
' Use this to send key command plus a key combination. e.g. Ctrl+O
Sub KeyPlusChar(str1 As Variant, str2 As Variant)
KeyDown str1
Keys str2
KeyUp str1
End Sub
' KeyDown() and KeyUp() for each character string in str.
Sub Keys(str As Variant)
Dim i As Integer, s As String, j As Integer
For i = 1 To Len(str)
s = Mid(str, i, 1)
For j = 1 To 330
'Debug.Print j, Asc(s) - j
Next j
If Val(s) = 0 Then s = Asc(s)
DoEvents
Key Val(s)
Next i
End Sub
' Release a key
Sub KeyUp(str As Variant)
keybd_event str, &H9D, 2, 0
End Sub
' Press a key
Sub KeyDown(str As Variant)
keybd_event str, &H9D, 0, 0
End Sub
' Press and release a key
Sub Key(str As Variant)
KeyDown str
KeyUp str
End Sub
-
unfortunatly it didn't work with:
KeyPlusChar 17, "a" or KeyPlusChar 17, "c"
I see that the macro is doing something because the cursor in "Amadeus" change from initially █ to I
But he don't select nothing and no copy as well.
-
@Kenneth Hobs maybe I'm doing something wrong, I wanted to try with the KeyDown method..
like
Code:
Sub Test_keybd_event1()
ActivateWindow "Altea Reservation Desktop"
KeyDown 'Ctrl
KeyDown 'A
KeyUp 'Ctrl
KeyUp 'c
AppActivate "Microsoft Excel"
ActiveWorkbook.Activate
Application.Wait Now + TimeValue("00:00:02")
Set S2 = Worksheets("Result")
S2.Activate
Range("A1").Activate
S2.Paste
End Sub
In your example you use Str but how to declare that, for example Str1 is Ctrl and Str2 is "a" etc...
-
I just did several tests, Ctrl + v and Ctrl + c, and Enter all are working fine.. the problem remains with Ctrl+a he just don't do the Select.. is there an alternative???
-
Str is just the name a string variable.
This does nothing:
Code:
KeyDown 'Ctrl KeyDown 'A
KeyUp 'Ctrl
KeyUp 'c
KeyPlusChar() is what you should use.
If Ctrl+A does not work, try Ctrl+a in my routines. Another way is to use another approach. Silly I know but there you have it. IF the macro does not send the Ctrl+a, then try sending SHIFT+PgDown or some combination like that. Try to do it manually first to see what key combination marks (selects) what you need.
-
reduced the code:
Code:
Sub Amadeus()
ActiveWorkbook.Sheets(1).Range("A1").Copy
AppActivate "Altea Reservation Desktop"
Application.Wait Now + TimeValue("00:00:02")
SendKeys ("^v")
SendKeys "{ENTER}"
End Sub
-
@ Kenneth Hobs
A big Thank youuuuuuuuu. Finally it works the problem was "A" (Ctrl+A). I tried it before with KeyPlusChar()
Code:
KeyPlusChar 17, "a"
but it didn't work. I never thought that changing to the capital letter would make a difference.
As for:
Code:
KeyDown 'Ctrl KeyDown 'A
KeyUp 'Ctrl
KeyUp 'c
I knew it wouldn't work like this, I only don't know what to write after KeyUp or KeyDown. 17 ? or vk_control??
because in your example you did a str
[CODE]
Sub Key(str As Variant)
KeyDown str
KeyUp str
End Sub [/CODE]
How can I declare that str is Ctrl???
Thanks again..
-
Because a variable name is str, does not mean it was dimmed as a string. I dimmed str as a variant so you can send 17 or "^", or "ken", or whatever. vk_Control means nothing unless you define it. You could define it as a constant as it is commonly used in Microsoft's standard API and such.
You can test for built-in constants in VBE's Immediate window like this. Press enter key after this.
-
OK many thanks again, I'll play a little bit with this.
