_ExcelSheetActivate not working for me

[everseeker]

;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]

By the way, that

_ExcelBookClose($oExcel, 1, 0)

simply locks up Excel...

[everseeker]

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

[Spiff59]

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

[everseeker]

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]

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