Excel Attachment issue, title method does not attach to current active sheet.

[cal]

Greeting everyone.

I've come across a problem with attaching to excel.  Its only seems to attach to the latest opened instance of excel.  What I need is to be able to open any excel file, press my hotkey and have it read the current active excel file.  The excel files I'm working with are generated on the fly so I'm trying to attach via getting the title of the active window and attaching.  I see no reason why this should not work.  I do get the proper title but it does not seem to attach to the active window.  It seems to always attach to the last instance of excel.  However I do not get an error when attaching by the title of an excel file that is NOT the last instance.

I'm using current versions,  so its office 365 and autoit 3.3.14.2 on a Win10 box.

The fact that I'm using an api to connect to the backend of a bigcommerce store and retrieving data to populate a spreadsheet on the fly does not matter to this issue.  It just means I want to connect to the active sheet of a file that may not be saved or have a filename.  Since I do not have a saved doc I need to use the title method and can't use the full path methods of attaching that I've used in the past.  The same issue happens with any excel file. Either new or an existing one I have previously saved when trying to attach via the title.

there is a stripped down stub file showing the issue at the end of this post.
To reproduce the issue do the following.

Open Excel.  Open a new blank workbook.  put 111 in cell A1.  Your title should default to "book1 - Excel"
open Excel.  Open a new blank workbook.  put 222 in cell A1.  Your title should default to "book2 - Excel"

Start the stub script.  Alt-tab to book1 and press F11 to run the function to be tested.

You will notice that the proper title is reported "book1 - Excel" but the value returned is "222".  It should have returned 111.

Open a third excel and put 333 in cell A1.  It does not matter what excel sheet is active.  It now returns the proper title of the active sheet but returns the value 333.
At this point you can close the 3rd copy and the function will starting returning 222 again.

I'm sure I'm missing some basic method here but I'm not seeing it.  What am I doing wrong?  I want to be able to alt-tab between several copies of excel and when I press the hotkey have it read from the current active sheet.

Cal.

 

Stub script.

 

expandcollapse popup

#include <Excel.au3>

HotKeySet('{F11}', 'convert_sheet')

are_we_done('excel.exe',5)




While 1
    sleep(1000)
WEnd



Func convert_sheet()


    ; problem with attaching.   Only attaches to the newest excel instance.

    $title = WinGetTitle("")
    $title1 = StringRight($title, 5)

    If $title1 <> 'Excel' Then
        MsgBox(0, @ScriptName,  'Line ' & @ScriptLineNumber & @CRLF & '--- excel not active ---')
        Return
    EndIf

    MsgBox(0, @ScriptName,  'Line ' & @ScriptLineNumber & @CRLF & '---' & $title & '---')

    $oWorkbook = _Excel_BookAttach($title, 'Title')
    If $oWorkbook = 0 Then
        MsgBox(0, @ScriptName,  'Line ' & @ScriptLineNumber & @CRLF & '--- attach fail ---')
        Return
    EndIf


    $line = _Excel_RangeRead($oWorkbook, Default, 'A1')
    MsgBox(0, @ScriptName,  'Line ' & @ScriptLineNumber & @CRLF & '---' & $line & '---')


EndFunc












Func are_we_done($process_name2, $timout_value)
    Global $process_name = ''
    $process_name = $process_name2
    AdlibRegister("are_we_done_2", $timout_value * 1000)
EndFunc   ;==>are_we_done
Func are_we_done_2()
    $pid = ProcessExists($process_name)
    If $pid == 0 Then Exit
EndFunc   ;==>are_we_done_2

 

 

lol.  Whats up with the code preview window.   I've got various colors popping up within the same var name.

edit...  I see it was just preview compose window.  It looks fine in the forum.

 

Anyhow.  Does anyone have any ideas where I'm going wrong and not attaching to the current  active window?

Edited July 5, 2016 by cal
added line.

[water]

By default the attach function searches all Excel instances. Maybe it's a bug. Will investigate tomorrow. 

[cal]

Thankyou.

[water]

This works fine for me:
Excel 2010 32 bit, Windows 7 64 bit.

#include <Excel.au3>

HotKeySet('{F11}', '_ConvertSheet')
While 1
    Sleep(1000)
WEnd

Func _ConvertSheet()
     ; Returns the active window's title
     $sTitle = WinGetTitle("[active]")
    If StringInStr($sTitle, " Excel ") = 0 Then Return MsgBox(0, @ScriptName, 'Line ' & @ScriptLineNumber & @CRLF & '--- excel not active ---')
    MsgBox(0, @ScriptName, 'Line ' & @ScriptLineNumber & @CRLF & '---' & $sTitle & '---')
    ; Create the Excel object
    Local $oExcel = _Excel_Open()
    If @error Then Exit MsgBox(0, @ScriptName, 'Line ' & @ScriptLineNumber & @CRLF & '--- Could not start Excel ---')
    ; Attach to the active Workbook
    $oWorkbook = _Excel_BookAttach($sTitle, 'Title')
    If @error Then Return MsgBox(0, @ScriptName, 'Line ' & @ScriptLineNumber & @CRLF & '--- Attach failed ---')
    ; Read a range
    $sCell = _Excel_RangeRead($oWorkbook, Default, 'A1')
    MsgBox(0, @ScriptName, 'Line ' & @ScriptLineNumber & @CRLF & '---' & $sCell & '---')
    ; Close the Excel object
    _Excel_Close($oExcel)
EndFunc   ;==>_ConvertSheet

Remember:
If a cell is currently being edited by the user then the attach will fail.

Edited July 7, 2016 by water

[cal]

I did not know about the cell editing, but no that is not a factor.  If I try that, the title is still returned correctly but yes the attach fails.

I took your version exactly as is.  I had to remove the space on the excel check so " Excel " became " Excel".  In my version the word is at the very end of the title so the stringinstring fails unless I take the extra space out.   Your version does not work for me.  Its still not attaching to the active instance as it will return values from sheets other then the one I have active.  There must be something then in either the office version or the Windows version that is causing this.

 

Is there a difference between these two lines?  Both work.

$sTitle = WinGetTitle("[active]")
$title = WinGetTitle("")

 

So....  One statement per line and yet you.....  so is the msgbox itself the return value?  It works.  sneaky and Interesting.

 

[water]

13 hours ago, cal said:

Is there a difference between these two lines?  Both work.

$sTitle = WinGetTitle("[active]")
$title = WinGetTitle("")

I think it only works by accident. WinGetTitle("") would return a window that matches the title string "" - which means every window would match.
The help file describes what happens then: "If multiple windows match the criteria, the most recently active window is used."
And this is exactly what WinGetTitle("[active]") does.