Results 1 to 10 of 10

Thread: Office 2007 Excel - populate opened application from userform

  1. #1

    Thread Starter
    Member
    Join Date
    Oct 2014
    Posts
    49

    Office 2007 Excel - populate opened application from userform

    Hello
    I am able to open a SQCS.exe from an excel userform I created. SQCS is basically a charting quality control exe.
    I want to populate it when it asks to login and equipment set and chart type etc.
    The SQCS form has a button to press for login and pulldowns for most everything else.
    For starters does anyone know of VBA code to press a login button or button of any kind for an opened exe?
    thanks for any help!
    Steve

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Office 2007 Excel - populate opened application from userform

    know of VBA code to press a login button or button of any kind for an opened exe?
    try
    findwindow
    findwindowex
    sendmessage
    and other
    API functions

    you will find many examples in this and the VB6 forums and many more on google
    one example here http://www.vbforums.com/showthread.p...+saveas+dialog

    it is hard to test for specific windows of less common programs
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  3. #3

    Thread Starter
    Member
    Join Date
    Oct 2014
    Posts
    49

    Re: Office 2007 Excel - populate opened application from userform

    Thanks for your help it got me in a better direction
    I tried this code to get the name of an opened window name

    "
    Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
    (ByVal lpClassName As String, ByVal lpWindowName As String) As Long

    Dim Ret As Long

    Sub Sample()
    Ret = FindWindow(vbNullString, "SQCS Application (Prod 2.22)")

    If Ret <> 0 Then
    MsgBox "Window Found"
    Else
    MsgBox "Window Not Found"
    End If
    End Sub
    "
    ... but it always comes up Window not found
    In the caption of the window there is an icon before the caption name
    I don't know if that is throwing it off

    Sorry I can't get this to format as real code

  4. #4
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Office 2007 Excel - populate opened application from userform

    findwindow should work like that, check the window caption for any extra or different characters
    test with other windows, to see if you get a result

    just about all windows have an icon, should not cause any issue
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  5. #5

    Thread Starter
    Member
    Join Date
    Oct 2014
    Posts
    49

    Re: Office 2007 Excel - populate opened application from userform

    Thanks for the help.
    I tried using this ...
    Code:
    Sub Sample()
        Dim Ret As Long, ChildRet As Long, OpenRet As Long
        Dim strBuff As String, ButCap As String
        
        Ret = FindWindow(vbNullString, "Test Form")
        'Ret = FindWindow(vbNullString, "SQCS_Application_(Prod 2.22)")
        'hwnd = FindWindow("ThunderDFrame", Me.Caption)
    
     
        If Ret <> 0 Then
            MsgBox "Window Found"
            '~~> Get the handle of the Button's "Window"
            ChildRet = FindWindowEx(Ret, ByVal 0&, "Button", vbNullString)
    
            '~~> Check if we found it or not
            If ChildRet <> 0 Then
                MsgBox "Child Window Found"
    
                '~~> Get the caption of the child window
                strBuff = String(GetWindowTextLength(ChildRet) + 1, Chr$(0))
                GetWindowText ChildRet, strBuff, Len(strBuff)
                ButCap = strBuff
            End If
        Else
            MsgBox "Window Not Found"
        End If
    End Sub
    I get msgbox "Window found" ... so some moderate success
    Last edited by Strato; Nov 2nd, 2014 at 12:59 PM.

  6. #6
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Office 2007 Excel - populate opened application from userform

    ChildRet = FindWindowEx(Ret, ByVal 0&, "Button", vbNullString)
    shouldn't that be the button's caption
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  7. #7

    Thread Starter
    Member
    Join Date
    Oct 2014
    Posts
    49

    Re: Office 2007 Excel - populate opened application from userform

    If I understand you correctly I changed this line to
    Code:
    ChildRet = FindWindowEx(Ret, ByVal 0&, "Login", vbNullString) ' Login being the button caption
    A few minor tweaks and I have
    Code:
    Sub Sample()
        Dim Ret As Long, ChildRet As Long, OpenRet As Long, hWnd As Long
        Dim strBuff As String, ButCap As String
        
        Ret = FindWindow(vbNullString, "Backgrind SQCS")
        'Ret = FindWindow(vbNullString, "Test Form")
        'Ret = FindWindow(vbNullString, "SQCS_Application_(Prod 2.22)")
        'hwnd = FindWindow("ThunderDFrame", Me.Caption)
    
     
        If Ret <> 0 Then
            MsgBox "Window Found"
            Debug.Print "Backgrind SQCS "; Hex(hWnd)
            '~~> Get the handle of the Button's "Window"
            ChildRet = FindWindowEx(Ret, ByVal 0&, "Login", vbNullString)
            Debug.Print "Login "; Hex(hWnd)
            
            '~~> Check if we found it or not
            If ChildRet <> 0 Then
                MsgBox "Child Window Found"
                
                '~~> Get the caption of the child window
                strBuff = String(GetWindowTextLength(ChildRet) + 1, Chr$(0))
                GetWindowText ChildRet, strBuff, Len(strBuff)
                ButCap = strBuff
                
                
            hWnd = FindWindowEx(hWnd, 0, "Button", "Close")
            SendMessage hWnd, BM_CLICK, 0, 0
    
            End If
        Else
            MsgBox "Window Not Found"
        End If
    End Sub
    but in my immediate window it have values of 0
    Backgrind SQCS 0
    Login 0
    I would assume they should be 1. And my output is still "Window found"

  8. #8
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Office 2007 Excel - populate opened application from userform

    Backgrind SQCS 0
    the previous line to get hwnd is commented out
    you should be printing the values of ret and childret

    if you want help with further testing, try with some common program, that i and others would also have, so we can test the code, then change to your application when the code is working
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  9. #9

    Thread Starter
    Member
    Join Date
    Oct 2014
    Posts
    49

    Re: Office 2007 Excel - populate opened application from userform

    For testing this I am using Excel 2007 vba that is all.

  10. #10
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Office 2007 Excel - populate opened application from userform

    on further investigation, i would believe your problem may be that the secondary window you are trying to find is not a child of the main application window, you would need to find more detail about the windows of your application, using spy++ or similar, else use a recursive enumeration like

    Code:
    Declare Function EnumChildWindows Lib "user32.dll" (ByVal hWndParent As _
    Long, ByVal lpEnumFunc As Long, ByVal lParam As Long) As Long
    Declare Function GetClassName Lib "user32" Alias "GetClassNameA" (ByVal hwnd As Long, _
       ByVal lpClassName As String, ByVal nMaxCount As Long) As Long
    
    Declare Function SendMessageS Lib "user32" Alias "SendMessageA" (ByVal hwnd As Long, _
       ByVal wMsg As Long, ByVal wParam As Long, ByVal lParam As String) As Long
    
    Public Function EnumChildProc(ByVal hwnd As Long, ByVal lParam As Long) As Long
    Dim slength As Long, wintext As String ' title bar text length and buffer
    
    Public Const WM_GETTEXT = &HD
    
    Dim retval As Long ' return value
    Static winnum As Integer ' counter keeps track of how many windows have been enumerated
    
    winnum = winnum + 1 ' one more window enumerated....
    ''''slength = GetWindowTextLength(hwnd) + 1 ' get length of title bar text
    ''''buffer = Space(slength) ' make room in the buffer
    ''''retval = GetWindowText(hwnd, buffer, slength) ' get title bar text
    ''''Debug.Print "Window #"; winnum; " : "; ' display number of enumerated window
    ''''Debug.Print Left(buffer, slength - 1) ' display title bar text of enumerated window
       Dim wClass As String, wText As String
       Dim j As Integer
       hchild = hwnd
       wClass = Space(64)
       j = GetClassName(hchild, wClass, 63)
       wClass = Left(wClass, j)
             
       wText = Space(256)
       j = SendMessageS(hchild, WM_GETTEXT , 255, wText)
       wText = Left(wText, j)
       
       ' at this point you can, based on some criteria determine if this is the window you want,
       ' then semdmessages to the window
       ' to prevent enumerating further windows, should conditionally make enumchildprocess = 0 (rather than 1)
       Debug.Print "Enum " & hchild; ", "; wClass;
       If Len(wText) Then Debug.Print ", """; wText; """";
       Debug.Print
    
    EnumChildProc = 1 ' return value of 1 means continue enumeration
    End Function
    
    ' call as below
    Sub xyz()
    
    ' *** Place this code wherever you want to enumerate the windows. ***
    Dim retval As Long ' return value
    Ret = FindWindow(vbNullString, "Ascii")  ' change to valid window title
    
    ' Use the above callback function to list all of the enumerated windows. Note that lParam is
    ' set to 0 because we don't need to pass any additional information to the function.
    retval = EnumChildWindows(Ret, AddressOf EnumChildProc, 0)
    
    End Sub
    here is some more API declares that may be required
    Code:
    Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
    'Dim CodeMod As VBIDE.CodeModule
    'Dim VBProj As VBIDE.VBProject
    'Dim VBComp As VBIDE.VBComponent
    
    Public Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
    (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
    
    Private 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
    
    Public Declare Function GetWindowText Lib "user32" Alias "GetWindowTextA" _
    (ByVal hwnd As Long, ByVal lpString As String, ByVal cch As Long) As Long
    
    Public Declare Function GetWindowTextLength Lib "user32" Alias _
    "GetWindowTextLengthA" (ByVal hwnd As Long) As Long
    
    Private Declare Function PostMessage Lib "User" (ByVal hwnd _
    As Integer, ByVal wMsg As Integer, ByVal wParam As Integer, _
    lParam As Any) As Integer
    
    Declare Function CloseWindow Lib "user32" (ByVal hwnd As Long) As Long
    
    Declare Function CloseHandle Lib "kernel32" (ByVal hObject As Long) As Long
    
    Private Declare Function SendMessage Lib "user32" Alias "SendMessageA" _
    (ByVal hwnd As Long, ByVal wMsg As Long, ByVal wParam As Long, lParam As Any) As Long
    i put this together, hacked from several examples found by google, that either were too complex, did not achieve the desired result or enumerated the windows multiple times
    i had the APIs declared in several modules, so i may have missed or duplicated some and have some that are not related to this code
    just about all of the code and comments are by others and cobbled together to achieve the desired result

    NOTE enumchildwindows will include all windows below the primary at any level
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

Posting Permissions

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



Click Here to Expand Forum to Full Width