Excel Book Attach Problem...

[Iceman1988]

hello everyone

i am having troubles with ExcelCom UDF relating excel Attacht function.

You see i have a script that uses this function about like 5 times and it always worked for about 6 months but since last monday it just stoped working... aparently out of own free will xD

The code below is a test script i wrote just to try and find a way to at least get around the situation but i am having a hard time find the solution

#include <ExcelCOM_UDF.au3>
_ExcelBookOpen("D:\Documents and Settings\xctci02\Desktop\PS_AGD_FIM_DIA.xls")
Sleep(4000)
$oExcel = _ExcelAttach("PS_AGD_FIM_DIA.xls","filename")
Sleep(1000)
_ExcelBookOpen("G:\AGD\QAI & CIC - GLE\MACROS\QAI\Grafico\Graficos AIT.xls")
Sleep(5000)
_ExcelShow($oExcel)
Sleep(1000)
$oExcel = ""
$oExcel = _ExcelAttach("G:\AGD\QAI & CIC - GLE\MACROS\QAI\Grafico\Graficos AIT.xls","filepath")
Sleep(4000)
_ExcelShow($oExcel)
Sleep(1000)
_ExcelBookOpen("D:\Documents and Settings\xctci02\Desktop\Antecipaçoes APOIO.xls")
Sleep(4000)
_ExcelShow($oExcel)
Sleep(1000)
$oExcel = ""
$oExcel = _ExcelAttach("D:\Documents and Settings\xctci02\Desktop\Antecipaçoes APOIO.xls","filepath")
Sleep(4000)
_ExcelShow($oExcel)

Basically what i want is for it to attach to excel workbooks again like it did only a couple of days ago

or is there a new UDF i don't know about? I'm using EXCELCOM_UDF 1.5

i'm still a noob in autoit but is this stuff as been so unstable... it is like this with everyone or it's just with me ?

thanks and sorry for the bad english

[stormbreaker]

AutoIt itself contains an Excel UDF. Read the help file for _ExcelBookAttach

[Iceman1988]

Thank for the reply

I know but that one doesn't work weither...

It just keep saying "There were no open excel windows."

my problem ,also, is that i want it to attach to a workbook and later to activate it and bring it to the front... it always worked but now it doesn't... just like that...

[stormbreaker]

You should definitely read the help file examples. They can get you in right direction.

[Iceman1988]

I have tried every example in the hel files and the message is the same...

"There were no open excel windows."

Besides this worked for about 6 months without problems and now... just the same message over and over

[water]

The ExcelCOM UDF is not supported. I would use the Excel UDF that comes with AutoIt.

You need some error checking (check the @error macro, add a COM error handler ...).

[Iceman1988]

The ExcelCOM UDF is not supported. I would use the Excel UDF that comes with AutoIt.

You need some error checking (check the @error macro, add a COM error handler ...).

Then if i'm to use Excel UDF that comes with autoit how may i activate a workbook and bring it to the front?

Because i have like 3 or 4 workbooks opened at the same time and for example with winactive it just activate the last one active not the one i want

[water]

This code should work with the standard Excel UDF. It opens two workbooks, the second one is active. if you press OK the first one is activated.

#include <Excel.au3>
Global $oExcel1, $oExcel2, $sPath1, $sPath2, $sFName1, $sFName2

AutoItSetOption("WinTitleMatchMode", 2)

$sPath1 = "C:temp"
$sFName1 = "Test1.xlsx"
$sPath2 = "C:temp"
$sFName2 = "Test2.xlsx"

$oExcel1 = _ExcelBookOpen($sPath1 & $sFName1) ; Open the first Excel workbook
If @error Then Exit MsgBox(16, "Error", "Error " & @error & " on _ExcelBookOpen")
$oExcel2 = _ExcelBookOpen($sPath2 & $sFName2) ; Open the second Excel workbook
If @error Then Exit MsgBox(16, "Error", "Error " & @error & " on _ExcelBookOpen")
MsgBox(64, "Switch", "Press OK to switch to first workbook")
WinActivate($sFName1)

Edited May 31, 2012 by water

[Iceman1988]

Sorry for the late reply

Thanks a lot @Water

I adated that code to fit in my script and it worked just fine

[water]

Glad the problem could be solved

[MvL]

I used the following,

letting Excel activate the attached window.

Before, in Excel.au3

Func _ExcelBookAttach($s_string, $s_mode = "FilePath")
 Local $o_Result
 If $s_mode = "filepath" Then
  $o_Result = ObjGet($s_string)
  If Not @error And IsObj($o_Result) Then
   Return $o_Result
  EndIf
 EndIf
...

Into

Func _ExcelBookAttach($s_string, $s_mode = "FilePath")
 Local $o_Result, $PathArr, $ExcelFilename
 If $s_mode = "filepath" Then
  $o_Result = ObjGet($s_string)
  If Not @error And IsObj($o_Result) Then
   $PathArr = StringSplit($s_string, "\")
   $ExcelFilename = $PathArr[$PathArr[0]]
   ;MsgBox(4096, $ExcelFilename, $o_Result.Application.Activewindow.Caption)
   If $o_Result.Application.Activewindow.Caption <> $ExcelFilename Then
    $o_Result.Application.Windows($ExcelFilename).Activate
   EndIf
   Return $o_Result
  EndIf
 EndIf

...

Excel activates the attached window.

[water]

I'm about to the Excel UDF. All this problems should be solved then.

[MvL]

Ok nice,

you might also want to consider the following,

regards.

; Check if Excel is installed and if the Excel workbook is ready to run.
  If Not $o_Result.Application.Ready Then
   MsgBox(4096, "Error", "The Excel Application has not installed, or Excel is not ready because you are editing in a cell.")
   Exit
  EndIf

  ; Prevent AutoSave disturbance during the AutoIt - run
  If $o_Result.EnableAutoRecover Then
   $o_Result.EnableAutoRecover = False
   MsgBox(4096, "Autorecover", "The AutoRecover will be disabled for this Excel workbook", 5) ; show for a number of seconds
  EndIf
  
  
  ; Minimize the workbook, to speed up the AutoIt - run
  $o_Result.Application.WindowState = -4140 ; xlMinimized

[water]

Every function in the new Excel UDF will return an error code if an operation was not successfull. This includes Excel not being installed.

AutoSave feature will remain a feature which has to be enambled/disabled by the user (at the moment). How will AutoSave disturb an AutoIt script?

No need to minimize the Excel window with the new UDF. You can set "ScreenUpdate" when you start Excel using _Excel_Open.

[MvL]

First:

Ok, but when I was still editing a cell in the .xls (in the formula bar of using F2)

and then opened the workbook in Excel (went Ok)

and then tried to do something with the workbook,

only at that moment I got a message 'The requested action with this object has failed'.

It happened to me several times, it is not the code that was in error.

Below is put a piece of code, to show how I prevented the message ''The requested action with this object has failed''

from the Excel error handling function from firing, and check on this specific error.

Second:

I don't want my script to behave unexpected, and autosave on a .xls that is stored on a network disc often takes a lot of time,

leaving me wondering why that is.

So I disable the autosave, before I run.

Third:

Yes, this is much the same, but when I have al long run,

I sometimes want to see the progress (I update my .xls in the run),

so I start with a minimized window,

and I can restore or maximize the window to 'be in touch', and minizmize it back again for speed.

With ScreenUpdate = False, I don't have that option.

Piece of code to find out that the .xls is Not Ready

expandcollapse popup

AutoItSetOption("MustDeclareVars", 1) ; 0=No, 1=Variables must be declared.
Local $x_oMyError
Local $o_Result, $PathArr, $ExcelFilename
Local $s_string = "C:\yy.xls" ; Already open, or to be opened.

$x_oMyError = ObjEvent("AutoIt.Error", "MyErrFunc") ; Initialize a COM error handler


$o_Result = ObjGet($s_string)

If @error Then

MsgBox(4096, "Error", @error)

Else

If Not IsObj($o_Result) Then

MsgBox(4096, "Error in .xls", $s_string & " does not exist")

Else

If Not $o_Result.Application.Ready Then

MsgBox(4096, "Error", "The Excel Application has not started, Excel is not ready because you are editing in a cell.")

Exit

EndIf



$o_Result.Windows(1).Visible = 1; Set the worksheet in the workbook visible

$o_Result.Application.Visible = 1; Set the application visible

$PathArr = StringSplit($s_string, "\")

$ExcelFilename = $PathArr[$PathArr[0]]

If $o_Result.Application.Activewindow.Caption <> $ExcelFilename Then

$o_Result.Application.Windows($ExcelFilename).Activate ; point to this Excel file

EndIf

EndIf

EndIf



Exit

[MvL]

Sorry,

- and then opened the workbook in Excel (went Ok)

should be

- and then opened the workbook in the AutoIt script using ObjGet() (this went Ok)

[water]

First:

You can set property "Interactive" to False with function _Excel_Open to prevent the user from editing a cell.. This blocks all keyboard and mouse input by the user. If needed you can set this property to True whenever needed.

Second:

Will add the ability to enable/disable AutoSave to the UDF.

Third:

In this case I would suggest to display a progress bar so the user knows how far the processing has come.

[MvL]

Ok,

much obliged.

[water]

I have been re-thinking setting the EnableAutoRecover property. As this property is one of a long list of properties you can set I will leave it to the user to set the required properties after the Workbook has been opened or newly created.

Setting a property is just a one-liner so creating a function is not sensible.

That's the reason why the property get and set functions for the Excel application and the workbook have been removed.

What's your opinion?

[MvL]

Perhaps it is an idea to explain at one place in the help, how the properties can be found ?

I use the Excel Visual Basic macro recorder

(so I found how you must activate an Excel Window after ObjGet() when another Excel Winow has the focus)

or

directly in the Visual Basic Editor window by entering a dot after the objectname,

and than choosing the method from the call tip drop down list,

and again, to the lowest level of methods.

Sometimes you have to use in AutoIt the $oExcel.Application expression to go up to the Excel application level

sometimes directly $oExcel to stay on the Workbook level.