Windows API, PowerShell, repeat keystrokes

This might be a dumb question but here goes. I have a windows-based application that has a pop-up window asking me to confirm all the changes or some of the changes and then click on OK to continue. This task is repeated around 10,000 times (once for each confirmation of a change). Is there anyway to program Windows API or PowerShell (not sure which one is correct in this context) via Excel vba to just click on the accept all changes button in the pop-up and then the OK button. Currently I have to stare in front of the screen for a day or so waiting for the popup to appear then I click Enter (default answer is already highlighted) >> Tab (highlight the OK button) >> Enter (to confirm).

If this is not possible then any other options are welcomed.

Thanks.

abousetta

Hello abousetta ,

Since this is a Windows based application and the default button is always selected, Excel may be able to answer for you automatically provided you are running a macro that clls this other application. If so then try adding Application.DisplayDialogs = False before you call the other program. Just remember to set it back to True before you exit the macro.

If the above does not apply, it may be possible to do with the Windows API. However, the code needed to activate buttons in a dialog box is quite lengthy and complex. I can not comment on the use of PowerShell to do this.

Hi Leith,

Thanks for looking at this for me. Here is some information I got from an automation program called AutoIt.

The information on the target window and buttons are as follows:

>>>> Window <<<<
Title:	Review Available Updates - [, 1969 #1745]
Class:	#32770
Handle:	0x0000000000680AC6

>>>> Control <<<<
Class:	Button
Instance:	1
ClassnameNN:	Button1
Name:	
Advanced (Class):	[CLASS:Button; INSTANCE:1]
ID:	3566
Text:	Update All Fields ->
Handle:	0x0000000000900706

>>>> Control <<<<
Class:	Button
Instance:	3
ClassnameNN:	Button3
Name:	
Advanced (Class):	[CLASS:Button; INSTANCE:3]
ID:	3553
Text:	Save Updates
Handle:	0x0000000000420796

Also this is something I picked up might or might not helpful but haven't gotten it to work in this situation:

For Each wnd In objShellWindows                              ' loop over all Shell-Windows
    If TypeName(wnd.Document) = "Window" Then          ' If the document is of type Window (this is probably wrong)
        If Left(wnd.Document, 53) = "Review Available Updates - [" Then    ' Leave, we found the right window
' CLICK ON BUTTONS
        End If
    End If
Next

Any help is highly appreciated.

Thanks.

abousetta

Hello abousetta,

The ShellWindows displays only a specific classes of Windows. It will not work for dialogs, which are special Window class. The information you posted about the dialog is very typical and basic. Unfortunately, it is not helpful with regard to the API. The class information #32770 is the class id for all dialogs. It does not provide me with any information about the parent object which would be needed to correctly identify it from any other dialog that is displayed. The handle is useful but handles change as windows are created and destroyed and can not used as a permanent id method in code.

Another potential problem with detection is if the dialog is not running in the same process as Excel. If not then a system hook would need to coded to detect the dialog. Unfortunately, system hooks written from Excel can be unstable and crash the system.

Is there any other methods this program offers to allow you better control of the interface? Are there any commands or rules that you can access via VBA or COM?

Hi Leith,

Here is all the information that I got on the mother window:

>>>> Window <<<<
Title:	EndNote X5 - [Heparin in Sepsis Copy.enl]
Class:	EndNote X5 Frame
Position:	-8, -8
Size:	1936, 1176
Style:	0x15CF8000
ExStyle:	0x00000110
Handle:	0x0000000000310A18

>>>> Control <<<<
Class:	
Instance:	
ClassnameNN:	
Name:	
Advanced (Class):	
ID:	
Text:	
Position:	
Size:	
ControlClick Coords:	
Style:	
ExStyle:	
Handle:	

>>>> Mouse <<<<
Position:	1601, 10
Cursor ID:	0
Color:	0xC7D5E3

>>>> StatusBar <<<<
1:	
2:	
3:	

>>>> ToolsBar <<<<

>>>> Visible Text <<<<
Heparin in Sepsis Copy.enl
Search
Search
Options
Search Whole Library
Match Case
Match Words
Author
Contains
+
-
And
Year
Contains
+
-
And
Title
Contains
+
-
Annals Internal Medicine
Ready


>>>> Hidden Text <<<<
And
23/08/2012
Preview
1.	Journal scan. High-dose antithrombin III in severe sepsis. A randomized controlled trial. Emergency Medicine Journal. 2002;19(2):149-.
Quick Edit
2002
Journal scan. High-dose antithrombin III in severe sepsis. A randomized controlled trial
Emergency Medicine Journal
19
2
149-149
1472-0205
2002111934. Language: English. Entry Date: 20020906. Revision Date: 20090410. Publication Type: journal article
Shock, Septic -- Drug Therapy
Sepsis -- Drug Therapy
Fibrinolytic Agents -- Therapeutic Use
Heparin -- Therapeutic Use
Survival
abstract; commentary. Original Study: Warren BL, Eid A, Singer P et al for the Kyber Sept Trial Study Group. JAMA 2001; 286: 1869-78. Journal Subset: Allied Health; Biomedical; Blind Peer Reviewed; Editorial Board Reviewed; Europe; Expert Peer Reviewed; Online/Print; Peer Reviewed; UK & Ireland. Special Interest: Emergency Care. NLM UID: 100963089.
http://proxy2.lib.umanitoba.ca/login?url=http://search.ebscohost.com/login.aspx?direct=true&db=cin20&AN=2002111934&site=ehost-live
cin20
EBSCOhost
Journal Article
0
100%
There are no PDFs attached to this reference
OK
Cancel
&Apply
Help

I don't know if this is what you were talking about or not. If this is a big task then I can try to just work around it but I generally would appreciate help automating this if possible.

abousetta

Hello abousetta,

Looks like I will have to download AutoIt onto my system. From the write ups it seems like it should be possible to interface it with VBA. If you need an answer faster than waiting for me then you may want to post this question on one of the AutoIt support forums.

HI Leith,

AutoIt seems to be a really neat free standalone program for creating a macro for windows operations. I have just started to play around with it yesterday to allow me to see what the information required for automating EndNote (the program I am using) via Excel vba. Also I should mention that EndNote has an API of its own but the documentation doesn't show the process I am after. Below I have provided the links for AutoIt, EndNote and the EndNote API if they help:

AutoIt: www.autoitscript.com

EndNote: www.endnote.com

EndNote API: http://endnote.com/en/endnote-api (download from here directly: https://www.dropbox.com/s/zcgssklram...ndNote_RDK.zip)

abousetta

Hi Leith,

Thanks for all your help and guidance. I finally got it working. I was able to search for the Class and Title of the dialog box using a series of API functions. I used FindWindowLike instead of FindWindow to give a fuzzy approach to the name of the dialog box since I only know that part of it will be a constant. All the code is in the attachment and is just a series of modified pieces of code from all over the internet. Finally, I added a simple userform to escape the loop when the user is done with the updates. I am posting the main code below for anyone who might be interested.

Thanks.

abousetta

Option Explicit

Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
Declare Function FindWindowEx Lib "user32" Alias "FindWindowExA" (ByVal hWnd1 As Long, ByVal hWnd2 As Long, ByVal lpsz1 As String, ByVal lpsz2 As String) As Long
Declare Function GetDesktopWindow Lib "user32" () As Long
Declare Function GetWindow Lib "user32" (ByVal hWnd As Long, ByVal wCmd As Long) As Long
Declare Function PostMessage Lib "user32" Alias "PostMessageA" (ByVal hWnd As Long, ByVal wMsg As Long, ByVal wParam As Long, ByVal lParam As Long) As Long
Declare Function SendMessage Lib "user32" Alias "SendMessageA" (ByVal hWnd As Long, ByVal wMsg As Long, ByVal wParam As Long, lParam As Any) As Long
Declare Function ShowWindow Lib "user32" (ByVal hWnd As Long, ByVal nCmdSHow As Long) As Long
Const WM_LBUTTONDOWN = &H201
Const WM_LBUTTONUP = &H202
Public CancelMe As Boolean

Sub Upate_EndNote_References()
  Dim parent_handle&, child_handle&, ClassName$, SearchName$
  ' Don't stop till the user clicks on the stop button
    CancelBtn.Show vbModeless
    CancelMe = False
  ' Window information
    ClassName = "#32770"
    SearchName = "Review Available Updates"
  ' Click on the update all fields buttons and save the changes
    DoEvents
    Do While CancelMe = False
  ' Find the correct window
    parent_handle = FindWindowLike(GetDesktopWindow(), SearchName, ClassName)
        DoEvents: If CancelMe = True Then Exit Do
        If parent_handle <> 0 Then
          ' Click the Update All Fields button
            child_handle = FindWindowEx(parent_handle, 0, "Button", "Update All Fields ->")
            Click_Button (child_handle)
          ' Click the Save Updates button
            child_handle = FindWindowEx(parent_handle, 0, "Button", "Save Updates")
            Click_Button (child_handle)
        End If
    Loop
    Unload CancelBtn
    MsgBox "Task completed...", vbInformation
End Sub

Private Sub Click_Button(child_handle As Long)
    DoEvents: If CancelMe = True Then Exit Sub
    Call PostMessage(child_handle, WM_LBUTTONDOWN, 0, 0)
    Call PostMessage(child_handle, WM_LBUTTONUP, 0, 0)
End Sub

Function FindWindowLike(hWndParent As Long, Caption As String, ClassName As String) As Long
  Dim hWnd&
  Const GW_HWNDNEXT = 2, GW_CHILD = 5
    DoEvents: If CancelMe = True Then Exit Function
  ' Find window using a like function
    hWnd = GetWindow(hWndParent, GW_CHILD)
    Do Until hWnd = 0
        If WindowText(hWnd) Like "*" & Caption & "*" Then
            FindWindowLike = hWnd
            Exit Do
        End If
        hWnd = GetWindow(hWnd, GW_HWNDNEXT)
    Loop
End Function

Function WindowText(hWnd As Long) As String
  Dim lng&, str$
  Const WM_GETTEXT = &HD, WM_GETTEXTLENGTH = &HE
    DoEvents: If CancelMe = True Then Exit Function
  ' Convert ID to text
    If hWnd <> 0 Then
        lng = SendMessage(hWnd, WM_GETTEXTLENGTH, 0&, 0&) + 1
        If lng > 0 Then
            str = String$(lng, vbNullChar)
            lng = SendMessage(hWnd, WM_GETTEXT, lng, ByVal str)
            If lng > 0 Then WindowText = Left$(str, lng)
        End If
    End If
End Function

Function apicShowWindow(strClassName As String, strWindowName As String, lngState As Long)
  Dim lngWnd&, intRet%
    DoEvents: If CancelMe = True Then Exit Function
  ' Use Findwindow and ShowWindow together to manipulate window
    lngWnd = FindWindow(strClassName, strWindowName)
    apicShowWindow = ShowWindow(lngWnd, lngState)
End Function

Hi,

I am attaching a newer version. This is the one I currently use. The added benefit is that you can see the name of the last citation that was updated. That way if EndNote crashes, you know where to begin from.

Hope this helps.

abousetta

Hello and thanks for your kind comments. For a while, I thought I was the only one that ever updated all their references in EndNote .

The only hiccup as you have noticed is that Endnote at time will crash. I've tried slowing down the macro with no change. The crash is random and I don't know why it happens. The only workaround I am doing is creating an extension to the above posted macro that will shut EndNote down if it crashes, restarts it and starts the process all over again. I'm not an IT pro so I do what I can, but so far everyone I know that is using it is managing OK.

So what's you field of work? I mainly lead and coordinate evidence-based knowledge synthesis projects.

abousetta

Also in case you are interested in the code that terminates Endnote, you can find it here.

Good luck.

abousetta