Jump to content
Sign in to follow this  
everseeker

_ExcelSheetActivate not working for me

Recommended Posts

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

Everseeker

Share this post


Link to post
Share on other sites
everseeker

By the way, that

_ExcelBookClose($oExcel, 1, 0)

simply locks up Excel...


Everseeker

Share this post


Link to post
Share on other sites
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 by everseeker

Everseeker

Share this post


Link to post
Share on other sites
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 by Spiff59

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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 by everseeker

Everseeker

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  

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.