Sign in to follow this  
Followers 0
chavv

Need help working with excel.au3

9 posts in this topic

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

Share this post


Link to post
Share on other sites



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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

You're right,

the problem is

_ExcelSheetActivate($oExcel,"Tabelle2").

I have had no problems, if nothing is open.

;-))

Stefan

Share this post


Link to post
Share on other sites

#5 ·  Posted (edited)

Try this one:

$oExcel.Sheets("sheetname").Select

You may also need to select the open workbook

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

Edited by Juvigy

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

#8 ·  Posted (edited)

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

Share this post


Link to post
Share on other sites

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

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