afried101 Posted December 19, 2013 Share Posted December 19, 2013 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! Link to comment Share on other sites More sharing options...
afried101 Posted December 19, 2013 Author Share Posted December 19, 2013 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. Link to comment Share on other sites More sharing options...
water Posted December 19, 2013 Share Posted December 19, 2013 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 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now