Saving/Closing Excel obj created with GetObj

[afried101]

I'm trying to keep the same excel object open for the duration of my script.  I'm using rows in an excel doc to grab info.  I then do something with that info and write back different info to the open worksheet.  I've looked around for quite a while and I'm unable to find a solution to this - in the help file or forums.  I'm not exactly sure what I'm doing wrong.  My apologies if this has already been covered, I just can't find it if it has.

Basic coding (this part is all working great.  No issues whatsoever.  pulling data, working with it, etc)

$oExcelDoc = ObjGet($tempIn)

If (not @error) and IsObj($oExcelDoc) Then
    $oDocument = $oExcelDoc.Worksheets(1)
    $Array = $oDocument.range($CellRange).value
    $oRows = $oExcelDoc.ActiveSheet.UsedRange.Rows.Count
    For $j = 1 To $oRows-1
        
        $firstName = $Array[1][$j]
        $lastName = $Array[2][$j]
        $birthDate = $Array[3][$j]
        $admissionDate = $Array[4][$j]
        $gender = $Array[5][$j]
        $patientID = $Array[6][$j]
        $zipCode = $Array[7][$j]
EndIf

Again, the above works just fine.  All parameters are set with the correct data.

I now go out and do something with that info, and assign other variables to the resulting set. (that endif is added by me so it doesn't look like incomplete code.  There's a bunch of other stuff in there).

$oDocument.Cells($j+1,10).Value = $memberID
$oDocument.Cells($j+1,11).Value = $plan
$oDocument.Cells($j+1,12).Value = $planStatus
$oDocument.Cells($j+1,13).Value = $message

I'm fairly certain this is also working.  I have seen it write this info back to the excel doc, although I think that was a fluke, since it's no longer showing.

Now, I have no idea how to save and close my excel document/object.  I've tried

_ExcelBookSave, and _ExcelBookClose [with the save flag set], but those seem to only work with _ExcelBookOpen/Attach, which I am trying to avoid.  I don't want excel to actually open.  I just want to write back to it the same way I'm reading from it at the very beginning.

I have tried:

$oDocument.Close
$oDocument = 0

$OExcelDoc.Close
$oExcelDoc = 0

But those all return errors : ==> The requested action with this object has failed.:

One thing I have noticed when viewing other forums/script examples is that people usually add a workbook when they open their object.  Does the fact that I'm not adding a workbook have anything to do with it?  Can someone help me out with what I'm missing here?  I had it working with _ExcelBookOpen, _ExcelWriteCell, and _ExcelBookClose(with save flag), but my boss doesn't want it to flash up.  I guess I could set Visible = 0, but I don't see a reason to open the file with _ExcelBookopen if it's already open through GetObj.

Thanks!

[afried101]

Figured it out.  Not using GetObj.  Opening with:

$oExcelDoc = _ExcelBookOpen($tempIn,0)

Then leaving it open until the end.  Closing with:

_ExcelBookClose($oExcelDoc,1,0)

and it's fine.

[water]

Just a hint. Try my rewrite of the Excel UDF (needs the latest AutoIt Beta version). Should do all you need and is much faster