Shoretel - Excel VBA - Help!

[nym2530]

I need to write a quick VBA to dial a number through Shoretel Communicator by clicking on a cell in a spreadsheet. I cannot get the references right. Please help!

 

[BiocideJ]

In response to my other thread and your PM, here is what should hopefully be the full code you need to get it working.

Function CallNumber(phoneNum As String) As Boolean
 Dim hWnds() As Long
 Dim hwndSHORETEL As Long
 Dim hwndCALL As Long
 Dim hwndCALLNUMB As Long
 Dim hwndCALLNOTE As Long
 CallNumber = False
    
    FindWindowLike hWnds(), 0, "*- ShoreTel Communicator", "*", Null
    hwndSHORETEL = hWnds(1)
    If hwndSHORETEL = 0 Then GoTo CallFail
    
    FindWindowLike hWnds(), hwndSHORETEL, "*Call Cell Panel*", "*", Null
    hwndCALL = hWnds(1)
    If hwndCALL = 0 Then GoTo CallFail
    FindWindowLike hWnds(), hwndCALL, "*", "*EDIT*", Null
    hwndCALLNUMB = hWnds(1)
    If hwndCALLNUMB = 0 Then GoTo CallFail
    
    SendMessage hwndCALLNUMB, WM_SETTEXT, 0&, ByVal phoneNum
    SendMessage hwndCALLNUMB, WM_KEYDOWN, 13, 0 'Press Enter Key
    FindWindowLike hWnds(), hwndSHORETEL, "&Add to Call Note:", "*", Null
    hwndCALLNOTE = hWnds(1)
    Do Until CallActive(hwndCALLNOTE)
        DoEvents
        Sleep 50
        Debug.Print "Connecting... " & Now()
    Loop
    Do While CallActive(hwndCALLNOTE)
        DoEvents
        Sleep 1000
        Debug.Print "Call in Progress... " & Now()
        DoEvents
    Loop
    Debug.Print "Call Completed. " & Now()
CallNumber = True
Exit Function
CallFail:
End Function



Function CallActive(hWndST As Long) As Boolean

 CallActive = False 'default (in case hWnd is not discovered)

    If hWndST = 0 Then GoTo CheckFail
    
    lResult = GetWindowLong(hWndST, GWL_STYLE)
    
    If lResult = 0 Or (lResult And WS_DISABLED) Then
        CallActive = False
    Else
        CallActive = True
    End If
    
CheckFail:

End Function



Function FindWindowLike(hWndArray() As Long, ByVal hWndStart As Long, WindowText As String, Classname As String, ID) As Long
   Dim hWnd As Long
   Dim r As Long
   ' Hold the level of recursion:
   Static level As Long
   ' Hold the number of matching windows:
   Static iFound As Long
 Dim sWindowText As String
   Dim sClassname As String
   Dim sID
   ' Initialize if necessary:
   If level = 0 Then
   iFound = 0
   ReDim hWndArray(0 To 0)
   If hWndStart = 0 Then hWndStart = GetDesktopWindow()
   End If
   ' Increase recursion counter:
   level = level + 1
   ' Get first child window:
   hWnd = GetWindow(hWndStart, GW_CHILD)
   Do Until hWnd = 0
   DoEvents ' Not necessary
   ' Search children by recursion:
   r = FindWindowLike(hWndArray(), hWnd, WindowText, Classname, ID)
   ' Get the window text and class name:
   sWindowText = Space(255)
   r = GetWindowText(hWnd, sWindowText, 255)
   sWindowText = Left(sWindowText, r)
   sClassname = Space(255)
   r = GetClassName(hWnd, sClassname, 255)
   sClassname = Left(sClassname, r)
   ' If window is a child get the ID:
   If GetParent(hWnd) <> 0 Then
   r = GetWindowLW(hWnd, GWL_ID)
   sID = CLng("&H" & Hex(r))
   Else
   sID = Null
   End If
   ' Check that window matches the search parameters:
   If sWindowText Like WindowText And sClassname Like Classname Then
   If IsNull(ID) Then
   ' If find a match, increment counter and
   '  add handle to array:
   iFound = iFound + 1
   ReDim Preserve hWndArray(0 To iFound)
   hWndArray(iFound) = hWnd
   ElseIf Not IsNull(sID) Then
   If CLng(sID) = CLng(ID) Then
   ' If find a match increment counter and
   '  add handle to array:
   iFound = iFound + 1
   ReDim Preserve hWndArray(0 To iFound)
   hWndArray(iFound) = hWnd
   End If
   End If
'   Debug.Print "Window Found: "
'   Debug.Print "  Window Text  : " & sWindowText
'   Debug.Print "  Window Class : " & sClassname
'   Debug.Print "  Window Handle: " & CStr(hwnd)
   End If
   ' Get next child window:
   hWnd = GetWindow(hWnd, GW_HWNDNEXT)
   Loop
   ' Decrement recursion counter:
   level = level - 1
   ' Return the number of windows found:
   FindWindowLike = iFound
End Function

 

[BiocideJ]

Forgot to post all of my API declarations and public variables. This is from a much larger project that I use so only a small handful of these actually apply to the Shore tel portion, but I'm giving you everything so I don't miss any.

'***********************'
'PUBLIC API DELCARATIONS'
'***********************'

Public Declare Function EnumChildWindows Lib "user32" _
    (ByVal hWndParent As Long, ByVal lpEnumFunc As Long, ByVal lParam As Long) As Long

Public Declare Function EnumWindows Lib "user32" _
    (ByVal lpEnumFunc As Long, ByVal lParam As Long) As Long

Public Declare Function FindWindow Lib "user32.dll" Alias "FindWindowA" _
    (ByVal lpClassName As Any, ByVal lpWindowName As Any) As Long

Public Declare Function FindWindowX 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 GetClassName Lib "user32" Alias "GetClassNameA" _
    (ByVal hWnd As Long, ByVal lpClassName As String, ByVal nMaxCount As Long) As Long

Public Declare Function GetCursorPos Lib "user32" _
    (lpPoint As POINTAPI) As Long

Public Declare Function GetDesktopWindow Lib "user32" () As Long
  
Public Declare Function GetDlgCtrlID Lib "user32" _
    (ByVal hWnd As Long) As Long

Public Declare Function GetForegroundWindow Lib "user32" _
    () As Long
    
Public Declare Function GetFocus Lib "user32" () As Long
    
Public Declare Function GetParent Lib "user32.dll" _
    (ByVal hWnd As Long) As Long

Public Declare Function GetTopWindow Lib "user32" (ByVal hWnd As Long) As Long

Public Declare Function GetWindow Lib "user32" _
    (ByVal hWnd As Long, ByVal wCmd As Long) As Long
    
Public Declare Function GetWindowLong Lib "user32" Alias "GetWindowLongA" _
    (ByVal hWnd As Long, ByVal nIndex As Long) As Long

Public Declare Function GetWindowLW Lib "user32" Alias "GetWindowLongA" (ByVal hWnd As Long, ByVal nIndex As Long) As Long
  
Public Declare Function GetWindowRect Lib "user32" _
    (ByVal hWnd As Long, lpRect As RECT) 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
  
Public Declare Function GetWindowThreadProcessId Lib "user32" _
    (ByVal hWnd As Long, lpdwProcessId As Long) As Long

Public Declare Function IsIconic Lib "user32" (ByVal hWnd As Long) As Integer

Public Declare Function IsWindow Lib "user32" _
    (ByVal hWnd As Long) As Long

Public Declare Function IsWindowVisible Lib "user32" _
    (ByVal hWnd As Long) As Long

Public Declare Function IsWindowEnabled Lib "user32" _
    (ByVal hWnd As Long) As Long

Public Declare Sub mouse_event Lib "user32" _
    (ByVal dwFlags As Long, ByVal dx As Long, ByVal dy As Long, ByVal cButtons As Long, ByVal dwExtraInfo As Long)

Public Declare Function OpenProcess Lib "kernel32" _
    (ByVal dwDesiredAccess As Long, ByVal bInheritHandle As Long, ByVal dwProcessId As Long) As Long

Public Declare Function PostMessage Lib "user32" Alias "PostMessageA" _
    (ByVal hWnd As Long, ByVal wMsg As Long, ByVal wParam As Long, lParam As Any) As Long

Public Declare Function SendMessage Lib "user32" Alias "SendMessageA" _
    (ByVal hWnd As Long, ByVal wMsg As Long, ByVal wParam As Long, lParam As Any) As Long

Public Declare Function SetCursorPos Lib "user32" _
    (ByVal x As Long, ByVal y As Long) As Long
    
Public Declare Function SetFocus Lib "user32.dll" _
    (ByVal hWnd As Long) As Long
    
Public Declare Function SetForegroundWindow Lib "user32" _
    (ByVal hWnd As Long) As Long

Public Declare Function ShowWindow Lib "user32" _
    (ByVal hWnd As Long, ByVal nCmdShow As Long) As Long

Public Declare PtrSafe Sub Sleep Lib "kernel32" _
    (ByVal dwMilliseconds As Long)
 
Public Declare Function WaitForSingleObject Lib "kernel32" _
         (ByVal hHandle As Long, ByVal dwMilliseconds As Long) As Long
 
Public Declare Function WindowFromPoint Lib "user32" _
    (ByVal xpoint As Long, ByVal ypoint As Long) As Long


'****************'
'PUBLIC CONSTANTS'
'****************'

Public Const BM_CLICK As Long = &HF5&

Public Const GW_CHILD As Long = 5
Public Const GW_HWNDNEXT As Long = 2

Public Const GWL_WNDPROC As Long = -4
Public Const GWL_HINSTANCE As Long = -6
Public Const GWL_HWNDPARENT As Long = -8
Public Const GWL_STYLE As Long = -16
Public Const GWL_EXSTYLE As Long = -20
Public Const GWL_USERDATA As Long = -21
Public Const GWL_ID As Long = -12

Public Const LVIF_INDENT As Long = &H10
Public Const LVIF_TEXT As Long = &H1
Public Const LVM_FIRST As Long = &H1000
Public Const LVM_SETITEM As Long = (LVM_FIRST + 6)

Public Const MOUSEEVENTF_LEFTDOWN As Long = &H2 ' left button down
Public Const MOUSEEVENTF_LEFTUP As Long = &H4 ' left button up

Public Const WM_CLOSE As Long = &H10
Public Const WM_SETTEXT As Long = &HC
Public Const WM_GETTEXT As Long = &HD
Public Const WM_GETTEXTLENGTH As Long = &HE
Public Const WM_KEYDOWN As Long = &H100
Public Const WM_KEYUP As Long = &H101
Public Const WM_CHAR As Long = &H102
Public Const WM_SYSKEYDOWN As Long = &H104

Public Const WS_DISABLED As Long = &H8000000

Public Const SYNCHRONIZE As Long = &H100000

Public Const SW_HIDE As Long = &H0
Public Const SW_SHOW As Long = &H5
Public Const SW_MAXIMIZE = 3
Public Const SW_MINIMIZE = 6

Public Const VK_BACKSPACE As Long = &H8
Public Const VK_MENU As Long = &H12    'ALT KEY (???)
Public Const VK_CONTROL As Long = &H17 'CONTROL KEY
Public Const VK_SPACE As Long = &H20


'************'
'PUBLIC TYPES'
'************'

Public Type LVITEM
   mask As Long
   iItem As Long
   iSubItem As Long
   state As Long
   stateMask As Long
   pszText As String
   cchTextMax As Long
   iImage As Long
   lParam As Long
   iIndent As Long
End Type

Public Type POINTAPI
    x As Long
    y As Long
End Type

Public Type RECT
    Left As Long
    Top As Long
    Right As Long
    Bottom As Long
End Type


'***********************'
'PUBLIC GLOBAL VARIABLES'
'***********************'

Public imm_Lookup As String         'used to specify the Class/Title name to Lookup
Public imm_ExactMatch As Boolean    'used to specify an exact class name is needed
Public imm_hWnd As Long             'used to output the hwnd of the found hWnd
Public imm_Success As Boolean       'used to verify returned hWnd is to a found control and not the last subcontrol (z-order)

 

[iDeals]

I know this thread is a few years old but I'd love to adapt this code to my needs. That said at a quick glance I cannot find where it is looking to pull the number from. Any guidance?

 

[welshgasman]

However you like, you pass the number to the function.?

=CallNumber(A2)

 

[iDeals]

However you like, you pass the number to the function.?

=CallNumber(A2)

But where would you put that? I imagine right now it is looking somewhere for call number.

 

[iDeals]

I'm tracking now, for my purposes it would be called under a command button click

CallNumber ("TextBox1")

That said, I'm having issues with the following:

"Function CallActive (WndST as Long) As Boolean

States lResult variable is undefined

***EDIT*** This was fixed by simply defining in he function

Dim lResult as Long

 

[welshgasman]

More likely CallNumber(Textbox1.Value)