Excel Interacting With Another Non-Office Application

**AZSteve** · 07-02-2013, 12:10 AM

I want to run reports in another application (Avaya) and paste results into Excel. I did this several years ago with Excel 2003 in an earlier OS but can't get it to work now.

I manually open four reports in Avaya, and when I click the Excel button (on the sheet I want the reports pasted to) it checks if the reports are open. If any aren't, it tells me to, then to restart.

If successful checking for the necessary Avaya reports, Excel then prompts me for a date, and sends the date to the first Avaya report with Sendkeys and DoEvents, then runs the report and copies the results to the clipboard. Then it goes back to Excel, paste the results, and asks for the next set of Avaya data.

The problem seems to be that the lingo used in Excel 2003 to interact with another application does not work with Excel 2010, or does not work with Windows WP Professional and Windows 7 Enterprise (one on each computer), or both.

Apparently part of my Excel 2003 code that isn't understood by what I'm using now

Code:

AppActivate Calls1
    Do While FindWindow(vbNullString, Calls1) = 0
    DoEvents
    Loop
    SendKeys "%r+%r", True
    DoEvents
    SendKeys "{tab 2}" + When, True
    DoEvents
    SendKeys "%w+{tab}", True
    DoEvents

"AppActivate Calls1" does indeed activate the appropriate window of the Avaya application. But Excel apparently doesn't understand the "FindWindow(vbNullString, Calls1) = 0" statement. When I put the cursor on FindWindow and press F1 I get the message "Object library invalid or contains references to object definitions that could not be found." Tools/References in the VB window shows no obvious problems.

What I am trying to do is put the focus on another app window (Calls1), then send keystrokes to it. The problem is that the SendKeys statement is sending those commands to the VB window, not to the Calls1 application that was activated (which it had done correctly in Excel 2003). Any ideas?

**Kenneth Hobson** · 07-02-2013, 01:50 AM

Obviously, you have posted a snippet. For the FindWindow() API routine to work, it needs to be defined at the top of a Module before other code. That code can vary depending on 32bit or 64bit computers.
e.g. for 32 bit:

Code:

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

For SendKeys() to work properly, it that is even possible, be sure that you turn off Windows, UAC.

For a more robust API example:

Code:

#If VBA7 Then
    Dim mhwndForm As LongPtr            'The userform's window handle
    Private Declare PtrSafe Function FindWindow32 Lib "USER32" Alias "FindWindowA" (ByVal lpClassName As String, _
    ByVal lpWindowName As String) As LongPtr
    Private Declare PtrSafe Sub SetWindowPos Lib "USER32" (ByVal hwnd As LongPtr, ByVal hWndInsertAfter As LongPtr, _
    ByVal X As Long, ByVal Y As Long, ByVal cx As Long, _
    ByVal cy As Long, ByVal wFlags As Long)
    Private Const HWND_TOPMOST As LongPtr = -1
    Private Const HWND_NOTOPMOST As LongPtr = -2
#Else
    Dim mhwndForm As Long             'The userform's window handle
    Private Declare Function FindWindow32 Lib "USER32" Alias "FindWindowA" (ByVal lpClassName As String, _
    ByVal lpWindowName As String) As Long
    Private Declare Sub SetWindowPos Lib "USER32" (ByVal hwnd As Long, ByVal hWndInsertAfter As Long, _
    ByVal X As Long, ByVal Y As Long, ByVal cx As Long, _
    ByVal cy As Long, ByVal wFlags As Long)
    Private Const HWND_TOPMOST As Long = -1
    Private Const HWND_NOTOPMOST As Long = -2
#End If
Private Const SWP_NOSIZE             As Long = &H1
Private Const SWP_NOMOVE             As Long = &H2
Private Const SWP_NOACTIVATE             As Long = &H10
Private Const SWP_SHOWWINDOW             As Long = &H40

**AZSteve** · 07-02-2013, 02:40 AM

My system is 32 bit.

I have almost exactly what you show in the first code, except for >

"Private" in front of "Declare Function", and "lpWindowName As String" instead of "lpWindowName As Long"

I also have this statement >

Private Declare Function GetKeyboardState Lib "user32" (pbKeyState As Byte) As Long

Note that I don't really understand most of this. Years ago (with Excel 2003 and an earlier OS) I pasted a question in a forum like this, got an answer, and with adaptations for the App names I had and the entries I wanted to make, it worked.

That being said, I will try pasting in the "more robust" code, see what happens, and report back.