Results 1 to 5 of 5

Thread: Excel Interacting With Another Non-Office Application

  1. #1
    Junior Member
    Join Date
    Jul 2013
    Posts
    4
    Rep Power
    0

    Excel Interacting With Another Non-Office Application

    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?
    Last edited by Admin; 07-02-2013 at 07:39 AM.

  2. #2
    Member
    Join Date
    May 2013
    Posts
    31
    Rep Power
    0
    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
    Last edited by Kenneth Hobson; 07-02-2013 at 11:36 PM.

  3. #3
    Junior Member
    Join Date
    Jul 2013
    Posts
    4
    Rep Power
    0
    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.

  4. #4
    Junior Member
    Join Date
    Jul 2013
    Posts
    4
    Rep Power
    0
    One more thing, Kenneth, I don't understand this >

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

  5. #5
    Moderator
    Join Date
    Jul 2012
    Posts
    156
    Rep Power
    13

Similar Threads

  1. Add VBA Reference From Another Application Excel To PowerPoint
    By ds1001 in forum Rajan Verma's Corner
    Replies: 1
    Last Post: 06-02-2013, 02:43 PM
  2. Replies: 1
    Last Post: 02-14-2013, 11:08 AM
  3. Replies: 2
    Last Post: 12-04-2012, 02:05 PM
  4. Using Office.CommandBar
    By Rasm in forum Excel Help
    Replies: 2
    Last Post: 12-03-2011, 06:04 AM
  5. Excel Application.OnKey With Parameter
    By Excel Fox in forum Excel Help
    Replies: 0
    Last Post: 11-29-2011, 01:31 PM

Posting Permissions

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