everseeker Posted July 2, 2010 Share Posted July 2, 2010 (edited) ;Load States tab of CriticalIllnessRates.xls to Array _ExcelBookOpen($sInFilePath, 1);Open CriticalIllnessRates.xls File Local $oExcel = _ExcelBookAttach($sInFilePath) _ExcelSheetActivate($oExcel, "States") $aStateLims = _ExcelReadSheetToArray($oExcel, 2) ;Starting on the 2nd Row _ArrayDisplay($aStateLims, "State Limits") ;Load Annual Rates tab of CriticalIllnessRates.xls to Array _ExcelSheetActivate($oExcel, "Annual Rates") $aAnnualRates = _ExcelReadSheetToArray($oExcel, 2) ;Starting on the 2nd Row _ArrayDisplay($aAnnualRates, "RateSheet") _ExcelBookClose($oExcel, 1, 0) Produces this error <BR>>Running:(3.3.6.1):C:\Program Files (x86)\AutoIt3\autoit3_x64.exe "C:\temp\CIRTM_V2.au3" <BR>C:\Program Files (x86)\AutoIt3\Include\Excel.au3 (1063) : ==> The requested action with this object has failed.:<BR>Local $iTemp = $oExcel.ActiveWorkbook.Sheets.Count<BR>Local $iTemp = $oExcel.ActiveWorkbook^ ERROR<BR>->11:57:43 AutoIT3.exe ended.rc:1<BR>>Exit code: 1 Time: 3.357<BR> Including the source xls, so you can see the 3 tabs..... (By the way, the files are .xls, in compatability mode)Critical Illness rates - RatesBlanked.zip Edited August 24, 2010 by everseeker Everseeker Link to comment Share on other sites More sharing options...
everseeker Posted July 2, 2010 Author Share Posted July 2, 2010 By the way, that _ExcelBookClose($oExcel, 1, 0) simply locks up Excel... Everseeker Link to comment Share on other sites More sharing options...
everseeker Posted July 2, 2010 Author Share Posted July 2, 2010 (edited) After a good bit of digging on the forum, I found that The way I had it simply will not work. Instead, this: ;Load States tab of CriticalIllnessRates.xls to Array _ExcelBookOpen($sInFilePath, 0) ;Open CriticalIllnessRates.xls File $oExcel = _ExcelBookAttach($sInFilePath) $oExcel.Sheets("States" ).Select() $aStateLims = _ExcelReadSheetToArray($oExcel, 2) ;Starting on the 2nd Row $oExcel.Sheets("Annual Rates" ).Select() $aAnnualRates = _ExcelReadSheetToArray($oExcel, 2) ;Starting on the 2nd Row _ArrayDisplay($aAnnualRates, "Annual Rates") does the trick Edited July 2, 2010 by everseeker Everseeker Link to comment Share on other sites More sharing options...
Spiff59 Posted July 3, 2010 Share Posted July 3, 2010 (edited) The way you're using _ExcelBookOpen (discarding the returned object), this would accomplish the same thing: $oExcel = ObjCreate("Excel.Application") Edit: Technically, your_ExcelBookOpen() could be replaced by simply: ObjCreate("Excel.Application") It just starts up an instance of Excel. But that's not how _ExcelBookOpen() is intended to be used. You're executing a lot of code that can be replaced by one line. I think this will work in place of your example code? ;Start Excel and open workbook $oExcel = ObjCreate("Excel.Application") $oExcel.Visible = 0 $oExcel.WorkBooks.Open($sInFilePath) $oExcel.Sheets("States" ).Select() $aStateLims = _ExcelReadSheetToArray($oExcel, 2) ;Starting on the 2nd Row _ArrayDisplay($aStateLims, "State Limits") $oExcel.Sheets("Annual Rates" ).Select() $aAnnualRates = _ExcelReadSheetToArray($oExcel, 2) ;Starting on the 2nd Row _ArrayDisplay($aAnnualRates, "Annual Rates") Edited July 3, 2010 by Spiff59 Link to comment Share on other sites More sharing options...
everseeker Posted August 24, 2010 Author Share Posted August 24, 2010 Still having a bit on angst with this... Given the following code bits ;Set up Output File $sOutFilePath = "C:\Program Files\HP\QuickTest Professional\Tests\Critical Illness Rates\Copy Of Default.xls" ;Open Excel and point to Data Entry tab $oExcel = ObjCreate("Excel.Application") $oExcel.Visible = 0 $oExcel.WorkBooks.Open($sOutFilePath) $oExcel.Sheets("DataEntry" ).Select() ;Create Top Row _ExcelWriteCell($oExcel, "State", 1, 1) _ExcelWriteCell($oExcel, "Zip", 1, 2) _ExcelWriteCell($oExcel, "Plan_Type", 1, 3) ;and so on It SEEMS simple... 1. Create an object 2. Open the excel file as that object 3. select the Data Entry sheet 4. Dump data into it So, WHY am I getting C:\Program Files\HP\QuickTest Professional\Tests\Critical Illness Rates\Data\CIRTM.au3 (189) : ==> The requested action with this object has failed.: $oExcel.Sheets("DataEntry" ).Select() $oExcel.Sheets("DataEntry" )^ ERROR Everseeker Link to comment Share on other sites More sharing options...
everseeker Posted August 24, 2010 Author Share Posted August 24, 2010 (edited) Wow.... Me thinks I am a twit OK, Problem solved Note the file name... "Copy Of Default.xls" Since there WAS no "Copy of", it was having issues finding the correct sheet.... I have no idea how the incorrect name crept in there... must have been a cut/paste error Now All I need to do is figure out how to "Empty" a sheet... Edited August 24, 2010 by everseeker Everseeker 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