_ExcelBookAttach Issues (not working)

[Viscouse]

Hi there. Long time user, first time poster. I love using Autoit because 99% of the time I can figure out my own issues. But this time I'm just pain flummoxed.

It's a pretty simple deal involving Excel. I want to write in an Excel workbook, which I can do. What I REALLY want to do is write in an existing file, not a new file. That's the sticky part.

Here's what works:

#Include <Excel.au3>
$oExcel = _ExcelBookNew()
_ExcelSheetActivate($oExcel, 2)
_ExcelWriteCell($oExcel, "Goober", 2,2) ;_ExcelWriteCell($oExcel, $sValue, $sRangeOrRow, $iColumn = 1)
Exit

Here's what doesn't work (assume file is already open):

#Include <Excel.au3>
$oExcel = _ExcelBookAttach("Misson Balancer.xlsx","FileName")
_ExcelSheetActivate($oExcel, 2)
_ExcelWriteCell($oExcel, "Goober", 2,2) ;_ExcelWriteCell($oExcel, $sValue, $sRangeOrRow, $iColumn = 1)
Exit

The Output window in code #2 sez:

C:\Program Files\AutoIt3\Include\Excel.au3 (1086) : ==> The requested action with this object has failed.:

If $oExcel.ActiveWorkbook.Sheets.Count < $vSheet Then Return SetError(2, 0, 0)

If $oExcel.ActiveWorkbook^ ERROR

Since the error is coming from Excel.au3, I figured I was in over my head.

Misc stats:

Excel 2007

AutoIt v3.3.0.0

Excel.au3 v 1.5 (07/18/2008) which was the most recent I could find.

Can't find any reference to this being not compatible with Excel 2007. I know the rest of my code is kosher since it works with a new book. I don't know if I'm not declaring something, or what. I don't get it.

Anyone have any light to shed?

EDIT: I know I said that I'm trying to do this via "Attach" to an open file. If I open the Excel file via _ExcelBookOpen, it works.

Edited March 23, 2010 by Viscouse

[PsaltyDS]

Try opening the file, and displaying the values that are used by _ExcelBookAttach(), that will help in seeing why attach isn't finding a match:

#include <Excel.au3>

$sFile = "C:\YourDir\YourSubdir\Misson Balancer.xlsx"
$oExcel = _ExcelBookOpen($sFile)
$o_workbooks = $oExcel.Application.Workbooks
If Not IsObj($o_workbooks) Or $o_workbooks.Count = 0 Then
    ConsoleWrite("Failed to get workbooks collection." & @LF)
    Exit
EndIf

For $o_workbook In $o_workbooks
        ConsoleWrite("filename:  " & $o_workbook.Name & @LF)
        ConsoleWrite("filepath:  " & $o_workbook.FullName & @LF)
        ConsoleWrite("title:  " & $o_workbook.Application.Caption & @LF & @LF)
Next

[rosaz]

Hi, I think the problem is the object you're selecting. Below should work, if you have only that Excel workbook open:

#Include <Excel.au3>
$oExcel = ObjGet("", "Excel.Application")
_ExcelSheetActivate($oExcel, 2)
_ExcelWriteCell($oExcel, "Goober", 2,2) ;_ExcelWriteCell($oExcel, $sValue, $sRangeOrRow, $iColumn = 1)
Exit

I'm still a bit confused myself on how to specify that workbook if you have multiple workbooks open, but hopefully that will be a good start!

[picaxe]

This works for me

#Include <Excel.au3>
;$oExcel = _ExcelBookAttach("Misson Balancer.xlsx","FileName")
$oExcel = _ExcelBookAttach("test.xls", "FileName")
_ExcelSheetActivate($oExcel.Application, 2)
_ExcelWriteCell($oExcel.Application, "Goober", 2,2) ;_ExcelWriteCell($oExcel, $sValue, $sRangeOrRow, $iColumn = 1)
Exit

Similar issues in this thread.

[Viscouse]

@PsaltyDS

Nice! I found some interesting values.

filename: Misson Balancer.xlsx

filepath: E:\Misson Balancer.xlsx

title: Microsoft Excel - Misson Balancer.xlsx [Read-Only]

Interesting title. I wonder why the "read only" at the end. It's obviously not, unless it somehow denies access to Autoit. ?

I know that using the filename & filepath don't crash.

@rosaz

Works. Thanks! I'm a bit weak on objects, and I was playing with the new toy I found that was Excel.au3, so I couldn't see outside the Excel Box!

Ok. I could stop now. But I'm curious.

Here's some works/doesn't work snippets...

Works:

$oExcel = ObjGet("", "Excel.Application")

_ExcelSheetActivate($oExcel, 2)

_ExcelWriteCell($oExcel, "Goober", 2,2)

$oExcel = _ExcelBookAttach("Misson Balancer.xlsx","Filename")

_ExcelWriteCell($oExcel, "Goober", 2,2)

Notice the lack of the SheetActivate in the second example.

Doesn't Work:

$oExcel = _ExcelBookAttach("Misson Balancer.xlsx","Filename")

_ExcelSheetActivate($oExcel, 2)

_ExcelWriteCell($oExcel, "Goober", 2,2)

I get this error:

C:\Program Files\AutoIt3\Include\Excel.au3 (1086) : ==> The requested action with this object has failed.:

If $oExcel.ActiveWorkbook.Sheets.Count < $vSheet Then Return SetError(2, 0, 0)

If $oExcel.ActiveWorkbook^ ERROR

I'm confused. Now I think it's the SheetActivate that doesn't work. But it works in rosaz's code.

Ok, I'm off to look at the other thread.

[Viscouse]

Picaxe's thread worked.

Final solution;

$oExcel = _ExcelBookAttach("Misson Balancer.xlsx","Filename")
_ExcelSheetActivate($oExcel.Application, 2)
_ExcelWriteCell($oExcel, "Goober", 2,2)

Never would have figured that out. Thanks all.