Need help working with excel.au3

[chavv]

Hi, this is my second AI3 script so perhaps I miss something obvious...

I want to open an excel workbook with many sheets (let's say 10), read cell which serves as counter from every sheet, write few cells, update my "counter cell", save the workbook and close Excel.

Code:

#include "excel.au3"
#include "date.au3"

$sFilePath1 = "E:\Projects\test.xls"
$oExcel = ObjCreate("Excel.Application") ;crete excel object
$oExcel.Visible = 1

$oExcel = _ExcelBookAttach("test.xls", "FileName")
if @error=1 Then
    msgbox (0,"file not yet open",@error)
    ;not attached
    $oExcel = ObjCreate("Excel.Application") ;crete excel object
    $oExcel.Visible = 1
    $oExcel.WorkBooks.Open($sFilePath1)
    If @error = 1 Then
        MsgBox(0, "Error!", "Unable to Create the Excel Object")
        Exit
    ElseIf @error = 2 Then
        MsgBox(0, "Error!", "File does not exist - Shame on you!")
        Exit
    EndIf
EndIf

_ExcelSheetActivate($oExcel,"Sheet2")
    $sCellValue = int(_ExcelReadCell($oExcel, 1, 1))
    _ExcelWriteCell ($oExcel, $sCellValue + 1, 1,1) ; increase row for next access
    _ExcelBookSave($oExcel)
    _ExcelBookClose($oExcel, 1, 0)

As you may see I firstly try to check if Excel had already opened the file with

$oExcel = _ExcelBookAttach("test.xls", "FileName")

Then if it is not, open it. reason: if file is already opened, it opens in read-only which is not good.

Actually almost everything is fine if opening file.

But if its already open it gives error:

>Running:(3.3.0.0):C:\Program Files (x86)\AutoIt3\autoit3_x64.exe "E:\Projects\ГВД\excel_access.au3"

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

Local $iTemp = $oExcel.ActiveWorkbook.Sheets.Count

Local $iTemp = $oExcel.ActiveWorkbook^ ERROR

->09:36:54 AutoIT3.exe ended.rc:1

+>09:36:55 AutoIt3Wrapper Finished

The problem seems to be in

_ExcelSheetActivate($oExcel,"Sheet2")

- if I try to writecell directly into active sheet - it is written, but no operation with sheets is possible - SheetActivate, SheetList all of them fail.

And second, minor problem

_ExcelBookClose($oExcel, 1, 0)

It does not work. Neither saves, nor closes Excel. >_<

All this is currently running on Win7 RC1, AutoIt 3.3.0.0 and beta 3.3.1.1, Excel 2007

[99ojo]

Hi,

I'm having the same problems.

But i think the problem is

$oExcel = _ExcelBookAttach ($filename [uBound ($filename) - 1], "FileName")

I try this code:

#include "excel.au3"
#include "date.au3"
Opt("WinTitleMatchMode", 2)
$sFilePath = "c:\test.xls"
$filename = StringSplit ($sFilePath, "\")
If WinExists ($filename [UBound ($filename) - 1]) Then
    $oExcel = _ExcelBookAttach ($filename [UBound ($filename) - 1], "FileName")
    If @error Then msgbox (0,"",@error) ; this msgbox doesn't appear, only console error as chavv described
Else
    $oExcel = _ExcelBookOpen($sFilePath, 1)
EndIf

_ExcelSheetActivate($oExcel,"Sheet2")
$sCellValue = int(_ExcelReadCell($oExcel, 1, 1))
_ExcelWriteCell ($oExcel, $sCellValue + 1, 1,1) ; increase row for next access
_ExcelBookSave($oExcel)
_ExcelBookClose($oExcel, 1, 0)

WinXp SP3, AutoIT 3.3.0.0, German Office Prof 2000

;-((

Stefan

[chavv]

Hi,

I'm having the same problems.

But i think the problem is

$oExcel = _ExcelBookAttach ($filename [uBound ($filename) - 1], "FileName")

no, problem is not there - it works correctly attaching to opened file.

So far I worked around:

After attaching to already open file I close it with _ExcelBookClose and reopen.

The weird thing being that this way I'm able to change the file, although last _ExcelBookClose does not close excel and after end of script it stays open >_<

[99ojo]

You're right,

the problem is

_ExcelSheetActivate($oExcel,"Tabelle2").

I have had no problems, if nothing is open.

;-))

Stefan

[Juvigy]

Try this one:

$oExcel.Sheets("sheetname").Select

You may also need to select the open workbook

$oExcel.Application.Workbooks("workbook name").Activate

Edited August 7, 2009 by Juvigy

[99ojo]

Try this one:

$oExcel.Sheets("sheetname").Select

You may also need to select the open workbook

$oExcel.Application.Workbooks("workbook name").Activate

Thanks Juvigy,

this works:

#include "excel.au3"
#include "date.au3"
Opt("WinTitleMatchMode", 2)
$sFilePath = "E:\Projects\test.xls"
$filename = StringSplit ($sFilePath, "\")
If WinExists ($filename [UBound ($filename) - 1]) Then
    $oExcel = _ExcelBookAttach ($filename [UBound ($filename) - 1], "FileName")
Else
    $oExcel = _ExcelBookOpen($sFilePath, 1)
EndIf
$oExcel.Sheets("Sheet2").Select
$sCellValue = int(_ExcelReadCell($oExcel, 1, 1))
_ExcelWriteCell ($oExcel, $sCellValue + 1, 1,1) ; increase row for next access
$oExcel.Application.Workbooks($filename [UBound ($filename) - 1]).Save
_ExcelBookClose($oExcel, 1, 0)

;-))

Stefan

[chavv]

Try this one:

$oExcel.Sheets("sheetname").Select

You may also need to select the open workbook

$oExcel.Application.Workbooks("workbook name").Activate

10x, it helps, but still _ExcelBookClose($oExcel, 1, 0) does not work - doesn't save, and doesn't close Excel.

It returns 0 (error) and sets @error to 1 - ie @error=1: Specified object does not exist

Also, now attaching is ok, but saving fails:

>Running:(3.3.0.0):C:\Program Files (x86)\AutoIt3\autoit3_x64.exe "E:\Projects\ГВД\excel_access.au3"    
C:\Program Files (x86)\AutoIt3\Include\excel.au3 (286) : ==> The requested action with this object has failed.:
.ActiveWorkBook.Save
.ActiveWorkBook^ ERROR
->13:18:18 AutoIT3.exe ended.rc:1
+>13:18:20 AutoIt3Wrapper Finished

Successfuly close with

if $oExcel.Application.Workbooks.Count =1 Then

$oExcel.quit

I obviously do something incorrect...

Do i need to create $oExcel = ObjCreate("Excel.Application") ? It looks Stefan is not using it...

[Juvigy]

He is using

$oExcel = _ExcelBookOpen($sFilePath, 1)

which gets the object from just opened file.

You can try to save and close by using:

$oExcel.ActiveWorkBook.Saved = 1
$oExcel.Quit

The error you get is that $oExcel is not an object.

You can do it several ways._ExcelBookOpen or Objcreate or something else.

Edit:

PS.

I see you use cyrilic - where are you from?

Edited August 7, 2009 by Juvigy

[99ojo]

10x, it helps, but still _ExcelBookClose($oExcel, 1, 0) does not work - doesn't save, and doesn't close Excel.

It returns 0 (error) and sets @error to 1 - ie @error=1: Specified object does not exist

Also, now attaching is ok, but saving fails:

>Running:(3.3.0.0):C:\Program Files (x86)\AutoIt3\autoit3_x64.exe "E:\Projects\ГВД\excel_access.au3"    
C:\Program Files (x86)\AutoIt3\Include\excel.au3 (286) : ==> The requested action with this object has failed.:
.ActiveWorkBook.Save
.ActiveWorkBook^ ERROR
->13:18:18 AutoIT3.exe ended.rc:1
+>13:18:20 AutoIt3Wrapper Finished

Successfuly close with

if $oExcel.Application.Workbooks.Count =1 Then

$oExcel.quit

I obviously do something incorrect...

Do i need to create $oExcel = ObjCreate("Excel.Application") ? It looks Stefan is not using it...

Yes, I'm not using it.

My Excel is closed, if it is open before code execution or not. Changes are written successfully to sheet.

1) Have you just paste and copy my code and run it? It is maybe a problem of the office version.

2) Because mý code is running, please post yours.

Otherwise it could be difficult to solve.

I'm using german XP SP3 with office2000 prof.

Stefan