Problem Giving the focus back to Excel after calling another application. - (Moved)

[Momentum]

Hi
The following code works well for me with Microsoft access. WinActivate("Microsoft Access") Works
It is called from excel when the user clicks a cell that contains text that is passed to an application program "Spark" that causes it to display a chart. 

I now want to implement the same functionality into an Excel spreadsheet. When a user clicks on the active worksheet in Excel the focus is given to spark causing the chart to display. That works too.

However the focus remains with spark. As it stands the user has to click somewhere in Excel  (In Excel) to bring the focus back and then select a cell to repeat the process. The process is repeated most likely hundreds of times in a session. the need to click twice is very annoying.

The Excel filename constantly changes so cannot be used. I just want to give the focus back to the worksheet. 

For some reason WinActivate("Microsoft Excel") is not working for me. I would appreciate any suggestions.

---------------------------------------------------------------------------------------------------------------------------------------------------------

I have posted the VBA Calling code Below for those that are interested Getting the cell contents was also a little tricky

#requireadmin
#include <AutoItConstants.au3>
#include <MsgBoxConstants.au3>
Local $sText
Local $Var1
$Var1 = $CmdLine[1]

if ProcessExists("Spark.exe") then ;run code below
;WinWaitActive("Spark", "")
    If WinActivate("Spark", "") Then
        ;ConsoleWrite('****** Spark Window Activated ******   ' & @CRLF)

    Send("{space}")
            ;Sleep(100)
    Send($Var1)
            ;Sleep(100)
    Send("{enter}")
            ;Sleep(100)
    send("{Shiftdown}")
    Send("{enter}")
    send("{Shiftup}")
            ;Sleep(100)
    EndIf
EndIf
    ;Sleep(250)
WinActivate("Microsoft Excel")
 

'This code Works. However the focus is moved to the external program window
'This requires that the user click somewhare on the excel sheet
'to move the focus back to the Excel window before selecting
'the cell to get the code. this will then move the focus back to the external program.
'The external application is triggered by AUTOIT code. See the call in sub Spark()

Option Explicit
 
Public stAppName As String
Public stockcode As String
 
Private Type POINTAPI
    x As Long
    y As Long
End Type
 
Private Type MSG
    hwnd As Long
    Message As Long
    wParam As Long
    lParam As Long
    time As Long
    pt As POINTAPI
End Type
 
Private Declare Function PeekMessage Lib "user32" _
Alias "PeekMessageA" _
(ByRef lpMsg As MSG, ByVal hwnd As Long, _
ByVal wMsgFilterMin As Long, _
ByVal wMsgFilterMax As Long, _
ByVal wRemoveMsg As Long) As Long
 
Private Const PM_NOREMOVE = &H0
 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim Message As MSG
    'check for left-mouse button clicks.
    PeekMessage Message, 0, 0, 0, PM_NOREMOVE

    If Message.Message = 512 Then
        Debug.Print "You clicked cell: " & Selection.Address, Selection.Value
    End If
    
    stockcode = Selection.Value
    Call Spark
End Sub

Sub Spark()
Debug.Print "Spark: " & Selection.Address, Selection.Value
     'stAppName = "C:\Users\XXX\Desktop\AUTOIT\Spark test 10 first working.exe " & stockcode
     stAppName = "C:\Users\XXX\Desktop\AUTOIT\Spark test 10 Excel.exe " & stockcode
     Call Shell(stAppName, 1)
End Sub
 

[Melba23]

Moved to the appropriate forum, as the Developer General Discussion forum very clearly states:

Quote

General development and scripting discussions.

---

Do not create AutoIt-related topics here, use the AutoIt General Help and Support or AutoIt Technical Discussion forums.

Moderation Team

[Momentum]

Hi All

More research led to this partial solution.
If I understand it correctly this solution may not work properly if there are multiple Excel sheets open?
For one sheet it works fine and the VBA code too. No fuss just click a cell. 
The VBA Code does not restrict clicking to to a particular column or row in the spreadsheet
This means the wrong data may be passed to the application you are calling.
I will tidy up The VBA code to restrict the range of cells.

Maybe this can be done in Autoit also. I would be very interested to hear if it can?

Regards John.

OH! and Melba I might be older than you. Think Melbourne Premiers A long while ago. I was already double figures....

#RequireAdmin
#Region ;**** Directives created by AutoIt3Wrapper_GUI ****
#AutoIt3Wrapper_UseX64=y
#EndRegion ;**** Directives created by AutoIt3Wrapper_GUI ****
#include <AutoItConstants.au3>
#include <MsgBoxConstants.au3>
Local $sText
Local $Var1
$Var1 = $CmdLine[1]

if ProcessExists("Spark.exe") then ;run code below
;WinWaitActive("Spark", "")
    If WinActivate("Spark", "") Then
        ;ConsoleWrite('****** Spark Window Activated ******   ' & @CRLF)

    Send("{space}")
            ;Sleep(100)
    Send($Var1)
            ;Sleep(100)
    Send("{enter}")
            ;Sleep(100)
    send("{Shiftdown}")
    Send("{enter}")
    send("{Shiftup}")
            ;Sleep(100)
    EndIf
EndIf
    ;Sleep(250)

AutoItSetOption ("WinTitleMatchMode", 2)
WinActivate("Excel")
WinWaitActive("Excel")

[water]

Everything you can do with VBA in Excel can be done with AutoIt too.
If you need to wait until a user clicks an Excel cell I suggest to use Excel events.

In general I would avoid to automate the GUI (Excel or Spark). Excel provides an API, how about Spark? Which tool are we exactly talking about?

[Momentum]

Hi Water

In answer to your question the re events. I tried many different coding solutions The code I posted is the best I can come up with. I am a private person doing my own research. There are dozens of posts around the net regarding issues with getting data from an Excel cell with a single click. without going into edit mode and without copying and pasting. I would be pleased to see my code simplified as long as it provides the same functionality. Calling up a chart in Spark Using Autoit is not a problem quite simple to do. 

Spark is a paid for stock market trading application. it does provide an API to link to excel. The current version uses DDE to provide  live (ASX) market data compatible with Excel, Open Office and Unofficially Libre Office. A little out of date. However it does provide a number of tools that are quite useful. From that perspective There is no real need for Autoit Automation. The same code can be modified to call charts from other programs, many are free.

Its going in the other direction, using Autoit automation and using Excel to trigger the display of a requested chart in Spark that has needed a fair bit of research to get the process running smoothly.  Now not quite but almost done, there are still some rough edges to tidy up.

The Excel spreadsheets I create use Microsoft access and VBA. The real number crunching is done there, Using Access VBA to Copy the data using a template into a nicely formatted and live spreadsheet. 

Apart from seeking a critical review of the code I have posted and getting possible solutions to some of the knotty, for me anyway problems that remain unsolved I thought disclosing all the code Needed by both Excel and Autoit might help someone else trying to solve the same problem.  Sharing is Good.

Many people I know use Excel to tabulate their research. There are 100's  of market linked charting programs some live some end of day, some are free. It is not possible to get direct access to stock exchange data server API's without paying huge fees. Autoit provides the ideal glue to join disparate programs together.   

[water]

Example script how to use events:
Whenever the user selcts a new cell the SelectionChange event function gets called.
The address of the selected cell is checked against $oRangeValid. A message gets displayed when the selection is outside the valid range.

expandcollapse popup

#include <Excel.au3>

Global $oExcel = _Excel_Open()                     ; Start up or connect to Excel
Global $oWorkbook = _Excel_BookNew($oExcel, 1)     ; Create a new empty workbook with a single worksheet
Global $oWorksheet = $oWorkbook.Sheets(1)          ; Access this worksheet
Global $oRangeValid = $oWorksheet.Range("A1:B2" )  ; Range the user can select from
_Excel_RangeWrite($oWorkbook, 1, "111", "A1")      ; Write example data to this worksheet
_Excel_RangeWrite($oWorkbook, 1, "222", "A2")
_Excel_RangeWrite($oWorkbook, 1, "333", "B1")

; *****************************************************************************
; Example Script
; Handle Worksheet SelectionChange event when the user selects a new cell.
; This script loops until Shift-Alt-E is pressed to exit.
; *****************************************************************************
HotKeySet("+!e", "_Exit") ;Shift-Alt-E to Exit the script
MsgBox(64, "Excel Example Script", "Hotkey to exit the script: 'Shift-Alt-E'!")

Global $oEvent = ObjEvent($oWorksheet, "oExcel_Events_")

While 1
    Sleep(10)
WEnd

; Worksheet SelectionChange event. See: https://docs.microsoft.com/en-us/office/vba/api/excel.worksheet.selectionchange
Func oExcel_Events_SelectionChange($oRange)
    Local $sAddress = $oRange.Address
    ConsoleWrite("User selected Cell: " & $sAddress & @CRLF)
    If IsObj($oExcel.Intersect($oRangeValid, $oRange)) Then
        ConsoleWrite("Cell value is:    : " & $oRange.Value & @CRLF)
    Else
        ConsoleWrite("The selected cell is outside the valid range!" & @CRLF)
    EndIf
EndFunc   ;==>oExcel_Events_SelectionChange

Func _Exit()
    Exit
EndFunc   ;==>_Exit

 

Edited September 26, 2021 by water

[robertocm]

On 9/26/2021 at 7:27 AM, Momentum said:

For some reason WinActivate("Microsoft Excel") is not working for me. I would appreciate any suggestions.

The title of the window can be different depending of the office version:

https://social.msdn.microsoft.com/Forums/Lync/en-US/4213b04f-870c-4118-8472-d79992aa1993/ms-project-2013-vba-macro-appactivate-quotmicrosoft-excelquot-runtime-error-5-invalid?forum=appsforoffice

i had these notes for VBA AppActivate:

'I have found a solution. WhenExcel 2007 is launched the title bar opens as "Book1 - Microsoft Excel".
'When Excel 2013 is launched the title bar opens as "Book 1 - Excel". I changed:
'AppActivate "Microsoft Excel"
'to
AppActivate "Excel"

 

[Momentum]

Thank you Water
I am going to try your code. The Microsoft Access Application that created the spreadsheet copied the data into an elaborately formatted workbook that contains 4 separate sheets, This template makes making changes relatively simple.  
 

[Momentum]

Thank You Robert

I ended up using
AutoItSetOption ("WinTitleMatchMode", 2)
WinActivate("Excel")
WinWaitActive("Excel")
Mod t will find "Excel" as a substring
 

Full code below

#RequireAdmin
#Region ;**** Directives created by AutoIt3Wrapper_GUI ****
#AutoIt3Wrapper_UseX64=y
#EndRegion ;**** Directives created by AutoIt3Wrapper_GUI ****
#include <AutoItConstants.au3>
#include <MsgBoxConstants.au3>
Local $sText
Local $Var1
$Var1 = $CmdLine[1]

if ProcessExists("Spark.exe") then ;run code below

    If WinActivate("Spark", "") Then
        ;ConsoleWrite('****** Spark Window Activated ******   ' & @CRLF)
WinWaitActive("Spark", "")
    Send("{space}")
            ;Sleep(100)
    Send($Var1)
            ;Sleep(100)
    Send("{enter}")
            ;Sleep(100)
    send("{Shiftdown}")
    Send("{enter}")
    send("{Shiftup}")
            ;Sleep(100)
    EndIf
EndIf
    ;Sleep(250)

AutoItSetOption ("WinTitleMatchMode", 2)
WinActivate("Excel")
WinWaitActive("Excel")

[water]

BTW: Could you please use code tags (the "<>" icon of the editor) when posting code?
Makes reading much easier

[water]

Updated example that starts up Excel and a second application (could be Spark). It brings Excel to the front and waits. When the user selects a new cell function oExcel_Events_SelectionChange is called.

This function displays the selected cell, checks if the address is within the valid range.
If yes, the value of the cell is read and displayed on the Console. Then the function switches to Notepad (could be Spark), waits 2 seconds and then switches back to Excel.

expandcollapse popup

#include <Excel.au3>

Run("notepad.exe")                                 ; Start a second application
Global $oExcel = _Excel_Open()                     ; Start up or connect to Excel
Global $oWorkbook = _Excel_BookNew($oExcel, 1)     ; Create a new empty workbook with a single worksheet
WinActivate($oExcel.Caption)                       ; Activate Excel
Global $oWorksheet = $oWorkbook.Sheets(1)          ; Access this worksheet
Global $oRangeValid = $oWorksheet.Range("A1:B2" )  ; Range the user can select from
_Excel_RangeWrite($oWorkbook, 1, "111", "A1")      ; Write example data to this worksheet
_Excel_RangeWrite($oWorkbook, 1, "222", "A2")
_Excel_RangeWrite($oWorkbook, 1, "333", "B1")

; *****************************************************************************
; Example Script
; Handle Worksheet SelectionChange event when the user selects a new cell.
; This script loops until Shift-Alt-E is pressed to exit.
; *****************************************************************************
HotKeySet("+!e", "_Exit") ;Shift-Alt-E to Exit the script
MsgBox(64, "Excel Example Script", "Hotkey to exit the script: 'Shift-Alt-E'!")

Global $oEvent = ObjEvent($oWorksheet, "oExcel_Events_")

While 1
    Sleep(10)
WEnd

; Worksheet SelectionChange event. See: https://docs.microsoft.com/en-us/office/vba/api/excel.worksheet.selectionchange
Func oExcel_Events_SelectionChange($oRange)
    Local $sAddress = $oRange.Address                                          ; Get the selected address
    ConsoleWrite("User selected Cell: " & $sAddress & @CRLF)
    If IsObj($oExcel.Intersect($oRangeValid, $oRange)) Then                    ; Check if the selected cell is inside the valid range
        ConsoleWrite("Cell value is:    : " & $oRange.Value & @CRLF)
        WinActivate("[CLASS:Notepad]", "")                                     ; Activate Notepad
        Sleep(2000)                                                            ; Sleep 2 seconds
        WinActivate($oExcel.Caption)                                           ; Activate Excel
    Else
        ConsoleWrite("The selected cell is outside the valid range!" & @CRLF)
    EndIf
EndFunc   ;==>oExcel_Events_SelectionChange

Func _Exit()
    Exit
EndFunc   ;==>_Exit