Sign in to follow this  
Followers 0
afried101

Saving/Closing Excel obj created with GetObj

3 posts in this topic

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!

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:

_ExcelBookClose($oExcelDoc,1,0)

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:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
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  
Followers 0