Sign in to follow this  
Followers 0
Viscouse

_ExcelBookAttach Issues (not working)

6 posts in this topic

#1 ·  Posted (edited)

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

Share this post


Link to post
Share on other sites



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

:(


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

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!

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

@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.

Share this post


Link to post
Share on other sites

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.

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