subina Posted April 29, 2016 Share Posted April 29, 2016 i thought i submited this before but i guess i am wrong. i am trying to delete a row in an excel spread sheet using excel objects i have this code $oexcel = objcreate("excel.application") $workbook = $oexcel.workbooks.open($exceldoc) $currentworksheet = $oexcel.activateworkbook.worksheets(1) $objreange = $currentworksheet.rows("4:4").enirerow $objrange.delete this does not work please advise Link to comment Share on other sites More sharing options...
Jfish Posted April 29, 2016 Share Posted April 29, 2016 (edited) This is one problem: " enirerow" I see this is a duplicate post. That said, have you looked at the Excel UDF? Take a look at the function called _Excel_RangeDelete. That may be an easy way to do what you are trying to accomplish. Edited April 29, 2016 by Jfish Build your own poker game with AutoIt: pokerlogic.au3 | Learn To Program Using FREE Tools with AutoIt Link to comment Share on other sites More sharing options...
subina Posted April 29, 2016 Author Share Posted April 29, 2016 sorry the enirerow was a miss spell on here, it is really entirerow. i have tried the _excel_rangedelete and had problems kept giving syntex problem Link to comment Share on other sites More sharing options...
subina Posted April 29, 2016 Author Share Posted April 29, 2016 ok i tried this $srange = "4:4" _excel_rangedelete($CURRENTWORKSHEET.ACVIVESHEET, $SRANGE, $X1SHIFTUP) IT DID NOT WORK Link to comment Share on other sites More sharing options...
Jfish Posted April 29, 2016 Share Posted April 29, 2016 (edited) Try this modified example from the help file. #include <Array.au3> #include <Excel.au3> #include <MsgBoxConstants.au3> ; Create application object and open an example workbook Local $oExcel = _Excel_Open() If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeDelete Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended) Local $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\excelrows.xlsx") ; NAME OF YOUR XLS HERE If @error Then MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeDelete Example", "Error opening workbook '" & @ScriptDir & "\Extras\_Excel1.xls'." & @CRLF & "@error = " & @error & ", @extended = " & @extended) _Excel_Close($oExcel) Exit EndIf ; ***************************************************************************** ; Delete a specific row ; ***************************************************************************** Local $sRange = $oWorkbook.ActiveSheet.rows(2); THIS IS THE ROW YOU WOULD LIKE TO DELETE MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeDelete Example 1", "Deleting cells " & $sRange & ".") _Excel_RangeDelete($oWorkbook.ActiveSheet, $sRange, $xlShiftUp) If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeDelete Example 1", "Error deleting cells." & @CRLF & "@error = " & @error & ", @extended = " & @extended) Edited April 29, 2016 by Jfish Build your own poker game with AutoIt: pokerlogic.au3 | Learn To Program Using FREE Tools with AutoIt Link to comment Share on other sites More sharing options...
water Posted April 29, 2016 Share Posted April 29, 2016 (edited) "Does not work" is a bit vague Can you please tell us what you get? Error message, return value, value of @error? What didn't work with the Excel UDF? Which syntax errors? Edited April 29, 2016 by water 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...
subina Posted April 29, 2016 Author Share Posted April 29, 2016 ok, tried $srange = $currentworksheet.activesheet.rows(4) _excel_rangedelete($currentworksheet.activesheet, $srange, $x1shiftup) did not work Link to comment Share on other sites More sharing options...
subina Posted April 29, 2016 Author Share Posted April 29, 2016 by dont work means that it failed to delete the row Link to comment Share on other sites More sharing options...
water Posted April 29, 2016 Share Posted April 29, 2016 Again: What does not work? Do you get any error messages in SciTe? What is the return value of _Excel_RangeDelete and the value of @error after calling the function? Can you please post the whole script you use with the Excel UDF (_Excel_Open etc. are missing right now)? 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...
subina Posted April 29, 2016 Author Share Posted April 29, 2016 strange return on @error is 1 however on my code $currentworksheet = $oexcel.activeworkbook.worksheets(1) if $isobj($currentworksheet) then msgbox(0,"","found") endif i do get found message. Link to comment Share on other sites More sharing options...
subina Posted April 29, 2016 Author Share Posted April 29, 2016 i wish i could post the entire script but cant because of security reasons. teh script works for everything else except this function which i just tried to isolate to test it Link to comment Share on other sites More sharing options...
water Posted April 29, 2016 Share Posted April 29, 2016 Grab Example 2 from the help file for _Excel_RangeDelete and modify it to your needs (filename, row to delete etc.). If you get an error we can work with that. 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...
Jfish Posted April 29, 2016 Share Posted April 29, 2016 For what it is worth the code I posted above is modified from the help file and it works ... you would simply need to replace the file name / path. I would suggest using all that code, not just a piece of it. Then as @water suggests if you still have a problem it would be easier to diagnose. Build your own poker game with AutoIt: pokerlogic.au3 | Learn To Program Using FREE Tools with AutoIt Link to comment Share on other sites More sharing options...
subina Posted April 29, 2016 Author Share Posted April 29, 2016 created script using Jfish example. filled in my path and row to be deleted _excel_open returns @error = 3 and @extended = -2147352567 Link to comment Share on other sites More sharing options...
alien4u Posted April 29, 2016 Share Posted April 29, 2016 Just to be clear: $srange = "4:4" (this is not a Row, not even a range). Be sure you have this two lines like @Jfish told you: Local $sRange = $oWorkbook.ActiveSheet.rows(2); ******THIS IS THE ROW YOU WOULD LIKE TO DELETE***** _Excel_RangeDelete($oWorkbook.ActiveSheet, $sRange, $xlShiftUp) Notice that row to delete is only one number refering to the entire row you want to delete. Regards Alien. Link to comment Share on other sites More sharing options...
water Posted April 29, 2016 Share Posted April 29, 2016 Disagree. "4:4" is a perfectly valid range as described here: https://www.autoitscript.com/wiki/Excel_UDF#Cell_reference 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...
Jfish Posted April 29, 2016 Share Posted April 29, 2016 1 hour ago, subina said: created script using Jfish example. filled in my path and row to be deleted _excel_open returns @error = 3 and @extended = -2147352567 The idea of using that code was that it would not cause you to disclose any confidential info. Can you please post what you tried using the code tags <> ? Build your own poker game with AutoIt: pokerlogic.au3 | Learn To Program Using FREE Tools with AutoIt Link to comment Share on other sites More sharing options...
alien4u Posted April 29, 2016 Share Posted April 29, 2016 1 hour ago, water said: Disagree. "4:4" is a perfectly valid range as described here: https://www.autoitscript.com/wiki/Excel_UDF#Cell_reference You are completly right, sorry my bad. I mean by an excel point of view or standard way, something like A1 or B2 or A1:B2, but like always I learn something new about the Excel_UDF. Thanks you. Link to comment Share on other sites More sharing options...
water Posted April 29, 2016 Share Posted April 29, 2016 That's not added by the Excel UDF, this form of cell reference is offered by Excel. Details can be found here: https://msdn.microsoft.com/en-us/library/ff839036%28v=office.14%29.aspx 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...
subina Posted May 2, 2016 Author Share Posted May 2, 2016 Gentlemen, I got the script from JFish to work. I thank all of your for your assist 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