Jump to content
Sign in to follow this  

Saving/Closing Excel obj created with GetObj

Recommended Posts


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]

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 = 0

$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.


Share this post

Link to post
Share on other sites

Figured it out.  Not using GetObj.  Opening with:

$oExcelDoc = _ExcelBookOpen($tempIn,0)

Then leaving it open until the end.  Closing with:


and it's fine.

Share this post

Link to post
Share on other sites

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 :)

My UDFs and Tutorials:


Active Directory (NEW 2018-12-03 - Version - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki

ADO - Wiki


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