_ExcelAttach() functionality

[Spiff59]

I'm guessing this has been discussed previously, but I am having no luck finding a pertinent thread.

Is there a method to make _ExcelAttach search through all instances of Excel?

It appears the function only searches for the target workbook in the first instance of Excel that it finds?

I guess a more specific question would be:

Can you retrieve and then process multiple hits from an ObjGet("","Excel.Application") call?

Thanks.

Edited July 7, 2008 by Spiff59

[big_daddy]

Please explain in better detail what exactly it is your trying to accomplish.

[Spiff59]

I have an AutoIt application reading some Advantage Database Server tables, presenting a listview of rows/records from the tables, and allowing the user to select rows to be reformatted and inserted into an existing Excel 2003 spreadsheet.

The spreadsheet is open when the AutoIt application is launched, and the full path/filename is passed to the AutoIt program.

I attempt to access the spreadsheet via the _ExcalAtttach UDF function.

If the target spreadsheet is one open in the first instance of Excel running, the attach works. If there are multiple Excel windows open, and the target spreadsheet is not within the first iteration, then I'm getting a failure, a not found on the attach call.

Am guessing there would be a lot more work required to make _ExcelAttach find it's target regardless of how many instances of Excel are open? Would need to scan all running processes to see which ones are Excel, then loop and search each of those handles individually and see if the target worksheet can be found?

Thanks.

[big_daddy]

I've found a resolution to this so I will rewrite _ExcelAttach() and post back when it's complete.

[big_daddy]

Here is the revised code...

expandcollapse popup

#include <Word.au3>
#include <ExcelCOM_UDF.au3>

$sFilePath1 = @ScriptDir & "\Test1.xlsx"
$sFilePath2 = @ScriptDir & "\Test2.xlsx"

_WordErrorHandlerRegister()
$oExcel1 = _ExcelBookAttach($sFilePath1, "FilePath")
$oExcel2 = _ExcelBookAttach($sFilePath2, "FilePath")

_ExcelBookClose1($oExcel1, 0)
Sleep(5000)
_ExcelBookClose1($oExcel2, 0)


; #FUNCTION# ====================================================================================================================
; Name...........: _ExcelBookAttach
; Description ...: Attach to the first existing instance of Microsoft Excel where the search string matches based on the selected mode.
; Syntax.........: _ExcelBookAttach($s_string, $s_mode = "FilePath")
; Parameters ....: $s_string - String to search for
;                  $s_mode   - Optional: specifies search mode:
;                  |FileName - Name of the open workbook
;                  |FilePath - (Default) Full path to the open workbook
;                  |Title    - Title of the Excel window
; Return values .: Success   - Returns an object variable pointing to the Excel.Application, workbook object
;                  Failure   - Returns 0 and sets @ERROR = 1
; Author ........: Bob Anthony (big_daddy)
; Modified.......:
; Remarks .......:
; Related .......:
; Link ..........;
; Example .......;
; ===============================================================================================================================
Func _ExcelBookAttach($s_string, $s_mode = "FilePath")

    Local $o_Result, $o_workbook, $o_workbooks
    
    If $s_mode = "filepath" Then
        $o_Result = ObjGet($s_string)
        If Not @error And IsObj($o_Result) Then
            Return $o_Result
        EndIf
    EndIf

    $o_Result = ObjGet("", "Excel.Application")
    If @error Or Not IsObj($o_Result) Then
        ConsoleWrite("--> Warning from function _ExcelAttach, No existing Excel.Application object" & @CR)
        Return SetError(1, 0, 0)
    EndIf

    $o_workbooks = $o_Result.Application.Workbooks
    If Not IsObj($o_workbooks) Or $o_workbooks.Count = 0 Then
        ConsoleWrite("--> Warning from function _ExcelAttach, No existing Excel.Application windows" & @CR)
        Return SetError(1, 0, 0)
    EndIf

    For $o_workbook In $o_workbooks

        Switch $s_mode
            Case "filename"
                If $o_workbook.Name = $s_string Then
                    Return $o_workbook
                EndIf
            Case "filepath"
                If $o_workbook.FullName = $s_string Then
                    Return $o_workbook
                EndIf
            Case "title"
                If ($o_workbook.Application.Caption) = $s_string Then
                    Return $o_workbook
                EndIf
            Case Else
                ConsoleWrite("--> Error from function _ExcelAttach, Invalid Mode Specified" & @CR)
                Return SetError(1, 0, 0)
        EndSwitch
    Next
    
    ConsoleWrite("--> Warning from function _ExcelAttach, No Match" & @CR)
    Return SetError(1, 0, 0)
EndFunc   ;==>_ExcelBookAttach

;===============================================================================
;
; Description:      Closes the active workbook and removes the specified Excel object.
; Syntax:           _ExcelBookClose($oExcel, $fSave = 1, $fAlerts = 0)
; Parameter(s):     $oExcel - Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew()
;                   $fSave - Flag for saving the file before closing (0=no save, 1=save) (default = 1)
;                   $fAlerts - Flag for disabling/enabling Excel message alerts (0=disable, 1=enable) (default = 0)
; Requirement(s):   None
; Return Value(s):  On Success - Returns 1
;                   On Failure - Returns 0 and sets @error on errors:
;                       @error=1 - Specified object does not exist
;                       @error=2 - File exists, overwrite flag not set
; Author(s):        SEO <locodarwin at yahoo dot com>
; Note(s):          None
;
;===============================================================================
Func _ExcelBookClose1($oExcel, $fSave = 1, $fAlerts = 0)
    If Not IsObj($oExcel) Then Return SetError(1, 0, 0)
    $sObjName = ObjName($oExcel)
    
    If $fSave > 1 Then $fSave = 1
    If $fSave < 0 Then $fSave = 0
    If $fAlerts > 1 Then $fAlerts = 1
    If $fAlerts < 0 Then $fAlerts = 0
    
    ; Save the users specified settings
    $fDisplayAlerts = $oExcel.Application.DisplayAlerts
    $fScreenUpdating = $oExcel.Application.ScreenUpdating
    ; Make necessary changes
    $oExcel.Application.DisplayAlerts = $fAlerts
    $oExcel.Application.ScreenUpdating = $fAlerts
    
    If $fSave Then
        $oExcel.ActiveWorkBook.Save
    EndIf
    
    ; Restore the users specified settings
    $oExcel.Application.DisplayAlerts = $fDisplayAlerts
    $oExcel.Application.ScreenUpdating = $fScreenUpdating
    
    Switch $sObjName
        Case "_Workbook"
            ; Check if multiple workbooks are open
            ; Do not close application if there are
            If $oExcel.Application.Workbooks.Count > 1 Then
                $oExcel.Close
            Else
                $oExcel.Application.Quit
            EndIf
        Case "_Application"
            $oExcel.Quit
        Case Else
            Return SetError(1, 0, 0)
    EndSwitch
    
    Return 1
EndFunc   ;==>_ExcelBookClose1

[Spiff59]

Am getting odd behavior with the revised function.

It does find the target workbook regardless of which instance of Excel it is open in, but...

Whereas these two lines worked previously:

$oExcel = _ExcelAttach($FilePath)
    $oExcel.Visible = 1

These two lines result in a fatal error:

$oExcel = _ExcelBookAttach($FilePath)
    $oExcel.Visible = 1

Thanks for spending time on this.

PS - Would this revision also correct not being able to find an open workbook that is not within the first instance of Excel when you use the "filename" mode, instead of "filepath"?

Edited July 17, 2008 by Spiff59

[big_daddy]

These two lines result in a fatal error:

$oExcel = _ExcelBookAttach($FilePath)
    $oExcel.Visible = 1

_ExcelBookAttach does just as you'd expect, it attaches to a workbook object and returns it. The previous version attached to the workbook object, but then returned the application object. I did not like this behavior so I fixed it in _ExcelBookAttach. Also you will want to use the version that I provide for inclusion in the latest Excel.au3 as it fixes a bug.

With that said here is how to make it work.

$oExcel.Application.Visible = True

PS - Would this revision also correct not being able to find an open workbook that is not within the first instance of Excel when you use the "filename" mode, instead of "filepath"?

Adding an instance parameter is an enhancement I plan on making when I get a chance.

[PsaltyDS]

_ExcelBookAttach does just as you'd expect, it attaches to a workbook object and returns it. The previous version attached to the workbook object, but then returned the application object. I did not like this behavior so I fixed it in _ExcelBookAttach. Also you will want to use the version that I provide for inclusion in the latest Excel.au3 as it fixes a bug.

With that said here is how to make it work.

$oExcel.Application.Visible = True

Adding an instance parameter is an enhancement I plan on making when I get a chance.

You 'da man, big_daddy!

[Spiff59]

_ExcelBookAttach has become wild and crazy for me now.

The fault seems to be the behavior of ObjGet.

Please take a look at my "ObjGet Failing?" post in the "General Help" forum.

It's very strange, ObjGet behaved correctly for me previously, but not anymore...

Any ideas?

Thanks

Edited July 31, 2008 by Spiff59

[Spiff59]

I've had it explained to me (rather bluntly) in my other thread "ObjGet Failing?" that ObjGet behaves differently when called in "filename" mode than it does in "application" mode.

Apparently ObjGet is written so that:

If an "application" call does not get a hit, it returns an @error condition and ends.

If a "filename" call does not get a hit, it creates a new object and starts a process.

If that is correct, then I won't be able use _ExcelBookAttach in "filename" mode to determine if a worksheet is already open somewhere? Instead of returning an error condition telling me "not found" it will just create an object?

Using the "application" parameter mode of _ExcelBookAttach/ObjGet works, but only scans the first running instance of Excel? So if the worksheet is open in another instance of Excel, I won't know it.

Am thinking presently, I'll revert to the "application" type call to ExcelBookAttach and settle for only searching the first found instance of Excel.

Thanks.

Edited July 31, 2008 by Spiff59

[ehsanul]

Here is the revised code...

The _ExcelBookClose1() function you posted here was giving me an error when saving was enabled, that is if _ExcelBookAttach() function was used to obtain the Excel object. I just made a slight change to the _ExcelBookClose1() function to take account of the possibility that a workbook object rather than an application object was used for the excel object parameter. I'm new at this, but it seems to be working fine for me, so here it is:

expandcollapse popup

;===============================================================================
;
; Description:      Closes the active workbook and removes the specified Excel object.
; Syntax:           _ExcelBookClose1($oExcel, $fSave = 1, $fAlerts = 0)
; Parameter(s):     $oExcel - Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew()
;               $fSave - Flag for saving the file before closing (0=no save, 1=save) (default = 1)
;               $fAlerts - Flag for disabling/enabling Excel message alerts (0=disable, 1=enable) (default = 0)
; Requirement(s):   None
; Return Value(s):  On Success - Returns 1
;                   On Failure - Returns 0 and sets @error on errors:
;                  @error=1 - Specified object does not exist
;                  @error=2 - File exists, overwrite flag not set
; Author(s):        SEO <locodarwin at yahoo dot com>
; Note(s):          None
;
;===============================================================================

Func _ExcelBookClose1($oExcel, $fSave = 1, $fAlerts = 0)
    If Not IsObj($oExcel) Then Return SetError(1, 0, 0)
    $sObjName = ObjName($oExcel)
   
    If $fSave > 1 Then $fSave = 1
    If $fSave < 0 Then $fSave = 0
    If $fAlerts > 1 Then $fAlerts = 1
    If $fAlerts < 0 Then $fAlerts = 0
   
    ; Save the users specified settings
    $fDisplayAlerts = $oExcel.Application.DisplayAlerts
    $fScreenUpdating = $oExcel.Application.ScreenUpdating
    ; Make necessary changes
    $oExcel.Application.DisplayAlerts = $fAlerts
    $oExcel.Application.ScreenUpdating = $fAlerts
   
    ; Restore the users specified settings
    $oExcel.Application.DisplayAlerts = $fDisplayAlerts
    $oExcel.Application.ScreenUpdating = $fScreenUpdating
   
    Switch $sObjName
        Case "_Workbook"
            If $fSave Then $oExcel.Application.ActiveWorkBook.Save
            ; Check if multiple workbooks are open
            ; Do not close application if there are
            If $oExcel.Application.Workbooks.Count > 1 Then
                $oExcel.Close
            Else
                $oExcel.Application.Quit
            EndIf
        Case "_Application"
            If $fSave Then $oExcel.ActiveWorkBook.Save
            $oExcel.Quit
        Case Else
            Return SetError(1, 0, 0)
    EndSwitch
   
    Return 1
EndFunc   ;==>_ExcelBookClose1

[ehsanul]

I might add that I realize I could've just used $oExcel.Application as the Excel object parameter in _ExcelBookClose1() instead of $oExcel, but that just doesn't seem very aesthetically pleasing. It seems that either I have to use $oExcel.Application as the Excel object parameter for all the concerned functions (which just looks wrong to me), or all those functions have to be modified to take in both application and worksheet objects.

[big_daddy]

The _ExcelBookClose1() function you posted here was giving me an error when saving was enabled, that is if _ExcelBookAttach() function was used to obtain the Excel object. I just made a slight change to the _ExcelBookClose1() function to take account of the possibility that a workbook object rather than an application object was used for the excel object parameter. I'm new at this, but it seems to be working fine for me, so here it is:

Also you will want to use the version that I provide for inclusion in the latest Excel.au3 as it fixes a bug.