Attaching to wrong excel

[wth]

This may not be an issue attaching as much as it's an issue with it pulling the data from the correct workbook. I'm able to attach to the Object but the non active workbook is getting used. Here is the main file and the excel selector is below.

expandcollapse popup

#Region ;**** Directives created by AutoIt3Wrapper_GUI ****
#AutoIt3Wrapper_outfile=..\ICQA\bin_switching.exe
#EndRegion ;**** Directives created by AutoIt3Wrapper_GUI ****
#include <Excel.au3>
#include <GUIConstants.au3>
#include <Misc.au3>
#include "excelWindowSelector.au3"

Opt("WinWaitDelay",100)
Opt("WinTitleMatchMode",2)
Opt("WinDetectHiddenText",1)
Opt("MouseCoordMode",0)

AutoItSetOption("MustDeclareVars", 1)

Global $cDelay, $cProgress, $cStart
Global Const $xlUp = -4162


_main()

Func _main()
    Local $hGui, $sMsg, $dll
    Local $iWidth = 300, $iHeight = 75

    $dll = DllOpen("user32.dll")
    $hGui = GUICreate("Bin Switching", $iWidth, $iHeight, 0, @DesktopHeight - $iHeight - 60)
    WinSetOnTop("BinSwitching", "", 1)

    GUICtrlCreateLabel("Speed:", 10, 10, 35, 20) ;1
    $cDelay = GUICtrlCreateCombo("Regular", 60, 10, 80, 20)
    GUICtrlSetData(-1, "Slow", "Regular")
    $cStart = GUICtrlCreateButton("Start", 160, 10, 80, 20)

    $cProgress = GUICtrlCreateLabel("", $iWidth/4, 40, $iWidth/2, 20)

    GUISetState(@SW_SHOW)

    While 1
        Sleep(15)
        $sMsg = GUIGetMsg()

        If _IsPressed("1B", $dll) Then
            TrayTip("", "User aborted process", 5)
            ExitLoop
        EndIf

        Switch $sMsg
            Case $GUI_EVENT_CLOSE
                Exit
            Case $cStart
                _binSwitch()
        EndSwitch
    WEnd
EndFunc

Func _binSwitch()
    Local $oExcel, $iRow, $iColumn, $aData, $i, $iNumItems, $sTitle

    $sTitle = _getExcelWindow()
    If $sTitle = -1 Then
        Exit
    EndIf

    $iColumn = InputBox("Starting Column", "Please enter the number of the starting cell's column." &@CRLF&@CRLF& _
                "For Column A, you would enter 1.  For Column B, you would enter 2", 1)
    If Not $iColumn > 0 Then
        Return -1
    EndIf

    $iRow = InputBox("Starting Row", "Please enter the starting row", 1)
    If Not $iRow > 0 Then
        Return -1
    EndIf

    $oExcel = _ExcelBookAttach($sTitle, "Title")
    If Not IsObj($oExcel) Then
        MsgBox(0,"Error", "Could not attach to Excel Window: " & $sTitle)
        Exit
    EndIf

    $iNumItems = $oExcel.Activesheet.cells(65535, Int($iColumn)).End($xlUp).Row - Int($iRow) + 1

    For $i = 0 To $iNumItems-1
        GUICtrlSetData($cProgress, "Switching: "&$i+1&" of "&$iNumItems)
        $aData = _ExcelReadArray($oExcel, Int($iRow+$i), Int($iColumn), 2)
        _singleBinSwitch($aData[0], $aData[1])

        _sleep(1500)
    Next

    SoundPlay("C:\WINDOWS\media\Windows XP Print complete.wav")

EndFunc

Func _singleBinSwitch($sSku, $sLocation)
    oracleActivate()
    Send($sSku)
    _sleep(1000)
    Send("^{F11}")
    _sleep(1000)
    Send("+{PGDN}")
    _sleep(2000)
    Send("^{UP}")
    _sleep(2000)
    Send("{ENTER}")
    _sleep(1000)
    Send($sLocation)
    _sleep(1000)
    Send("+{PGUP}")
    _sleep(1000)
    Send("^s")
    _sleep(1000)
    Send("{F11}")
EndFunc

Func oracleActivate()
    Local $oracle

    $oracle = "Oracle Applications - Production"
    If Not WinActive($oracle,"") Then WinActivate($oracle,"")
    WinWaitActive($oracle,"")
    sleep(1000)
EndFunc

Func _sleep($iTime)
    If GUICtrlRead($cDelay) = "Slow" Then
        $iTime = $iTime * 2
    EndIf
    Sleep($iTime)
EndFunc

Excel selector:

expandcollapse popup

#include <Array.au3>
#include <GUIListBox.au3>
#include <GuiConstantsEx.au3>
#include <WindowsConstants.au3>
#include <Constants.au3>

AutoItSetOption("MustDeclareVars", 1)

;_getExcelWindow()

Func _getExcelWindow()
    Local $aWindows, $i, $sMsg
    Local $hGUI, $hListBox, $cOK
    Local $iWidth = 300, $iHeight = 175
    Local $sReturn

    $aWindows = WinList()

    $i=0
    While $i <= UBound($aWindows) - 1
        If Not StringInStr($aWindows[$i][0], "Microsoft Excel - ") > 0 Then
            _ArrayDelete($aWindows, $i)
        Else
            $i = $i + 1
        EndIf
    WEnd

    ; Create GUI
    $hGUI = GUICreate("Select window", $iWidth, $iHeight)
    $hListBox = _GUICtrlListBox_Create($hGUI, "", 2, 2, $iWidth-4, $iHeight - 24)
    $cOK = GUICtrlCreateButton("Ok", $iWidth - 32, $iHeight - 22, 30, 20)
    GUISetState()

    ; Add files
    _GUICtrlListBox_BeginUpdate($hListBox)
    _GUICtrlListBox_ResetContent($hListBox)
    _GUICtrlListBox_InitStorage($hListBox, 100, 4096)
    For $i = 0 to UBound($aWindows) - 1
        _GUICtrlListBox_AddString($hListBox, $aWindows[$i][0])
    Next
    _GUICtrlListBox_EndUpdate($hListBox)

    ; Loop until user exits
    While 1
        $sMsg = GUIGetMsg()

        Switch $sMsg
            Case $GUI_EVENT_CLOSE
                GUIDelete($hGUI)
                $sReturn = -1
                ExitLoop
            Case $cOK
                If _GUICtrlListBox_GetCurSel($hListBox) <> - 1 Then
                    $sReturn = _GUICtrlListBox_GetText($hListBox, _GUICtrlListBox_GetCurSel($hListBox))
                    ExitLoop
                Else
                    TrayTip("", "Please select a window", 3)
                EndIf
        EndSwitch
    WEnd
    GUIDelete()
    Return $sReturn
EndFunc

Another part of the issue is that if Excel shares the main DDE with another workbook, only one of the titles is shown in the selector. I'm not exactly sure what I should do to fix this and would appreciate any suggestions.

Thanks

[rdwray]

I am not positive about your problem, but I have found that using ActiveSheet or ActiveWookbook sometime fails. Try using Try using:

Workbooks("WorkbookName.xls").Worksheets("SheetName").Activate

Edited December 7, 2010 by rdwray

[Juvigy]

There is an option for excel to open every file in a new instance. Do that.

Otherwise you can use code to select the desired workbook.Something like this:

$oExcel=_ExcelBookAttach("2010.xls","FileName")

$oExcel.Application.Workbooks("4000.xls").Activate

[wth]

I am not positive about your problem, but I have found that using ActiveSheet or ActiveWookbook sometime fails. Try using Try using:

Workbooks("WorkbookName.xls").Worksheets("SheetName").Activate

I think I'll be able to use the selector to cut off the filename to specify the workbook. Then ActiveSheet might have better luck. Thanks.

There is an option for excel to open every file in a new instance. Do that.

Otherwise you can use code to select the desired workbook.Something like this:

$oExcel=_ExcelBookAttach("2010.xls","FileName")

$oExcel.Application.Workbooks("4000.xls").Activate

I used to have this set on my computer but I ran into issues because I'm not the only person running these. So now I try to code to our company's default install.

The thing that is most confusing to me is why the _ExcelBookAttach doesn't fully work when I use the Full Title with Title as the second option as listed in the help file. Is there an easy way to get the path for the file from the Title?

Thanks

[Juvigy]

This should work:

$oExcel = ObjGet("","Excel.Application") ;get the excel object.
$oExcel.Application.Workbooks("4000.xls").Activate ; activate the 4000.xls workbook.You may have many wbooks open.

Keep in mind that you have to have only 1 excel application opened. If you set excel to open every file in a new instance it wont work correctly.

[wth]

This should work:

$oExcel = ObjGet("","Excel.Application") ;get the excel object.
$oExcel.Application.Workbooks("4000.xls").Activate ; activate the 4000.xls workbook.You may have many wbooks open.

Keep in mind that you have to have only 1 excel application opened. If you set excel to open every file in a new instance it wont work correctly.

Thanks for your help. Along with adding this, I noticed that I wasn't getting every Excel Title either because only the Active Window would have Microsoft Excel - in the title. So I searched for .xls and then manually deleted the one with Microsoft Excel -. Now it seems to works.

If I add a WinActivate($sTitle) before I do the ObjGet, will that reliably return the obj that is in control of $sTitle?

[Juvigy]

With your current setup (only one excel window) you dont need WinActivate($sTitle).

You can get the filename for the currently selected wbook easy:

Try this small example you can also change .FullName to .Path :

$oExcel = ObjGet("", "Excel.Application")
           For $element In $oExcel.Application.Workbooks
               If $element.FullName <> "" Then
                   MsgBox(0,"",$element.FullName)
                   $element.Close
               Else  
                   $element.Activate
                   MsgBox(0,"",$element.FullName)
               EndIf
           Next
$oExcel.Application.Quit

[wth]

With your current setup (only one excel window) you dont need WinActivate($sTitle).

You can get the filename for the currently selected wbook easy:

Try this small example you can also change .FullName to .Path :

$oExcel = ObjGet("", "Excel.Application")
           For $element In $oExcel.Application.Workbooks
               If $element.FullName <> "" Then
                   MsgBox(0,"",$element.FullName)
                   $element.Close
               Else  
                   $element.Activate
                   MsgBox(0,"",$element.FullName)
               EndIf
           Next
$oExcel.Application.Quit

Thanks for your help!