Sign in to follow this  
Followers 0
Spiff59

_ExcelAttach() functionality

13 posts in this topic

#1 ·  Posted (edited)

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 by Spiff59

Share this post


Link to post
Share on other sites



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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

Here is the revised code...

#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

Share this post


Link to post
Share on other sites

#6 ·  Posted (edited)

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 by Spiff59

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

_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!

:P


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites

#9 ·  Posted (edited)

_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 by Spiff59

Share this post


Link to post
Share on other sites

#10 ·  Posted (edited)

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 by Spiff59

Share this post


Link to post
Share on other sites

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:

;===============================================================================
;
; 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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0