chavv Posted August 7, 2009 Share Posted August 7, 2009 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 FinishedThe 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 Link to comment Share on other sites More sharing options...
99ojo Posted August 7, 2009 Share Posted August 7, 2009 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 Link to comment Share on other sites More sharing options...
chavv Posted August 7, 2009 Author Share Posted August 7, 2009 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 >_< Link to comment Share on other sites More sharing options...
99ojo Posted August 7, 2009 Share Posted August 7, 2009 You're right, the problem is _ExcelSheetActivate($oExcel,"Tabelle2"). I have had no problems, if nothing is open. ;-)) Stefan Link to comment Share on other sites More sharing options...
Juvigy Posted August 7, 2009 Share Posted August 7, 2009 (edited) 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 Link to comment Share on other sites More sharing options...
99ojo Posted August 7, 2009 Share Posted August 7, 2009 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 Link to comment Share on other sites More sharing options...
chavv Posted August 7, 2009 Author Share Posted August 7, 2009 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... Link to comment Share on other sites More sharing options...
Juvigy Posted August 7, 2009 Share Posted August 7, 2009 (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 August 7, 2009 by Juvigy Link to comment Share on other sites More sharing options...
99ojo Posted August 7, 2009 Share Posted August 7, 2009 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 Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now