deleting excel row

[subina]

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

 

[Jfish]

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

[subina]

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

 

[subina]

ok i tried this

 

$srange = "4:4"

_excel_rangedelete($CURRENTWORKSHEET.ACVIVESHEET, $SRANGE, $X1SHIFTUP)

IT DID NOT WORK

 

[Jfish]

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

[water]

"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

[subina]

ok, tried $srange = $currentworksheet.activesheet.rows(4)

_excel_rangedelete($currentworksheet.activesheet, $srange, $x1shiftup)

did not work

[subina]

by dont work means that it failed to delete the row

[water]

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

[subina]

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.

[subina]

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

 

[water]

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.

[Jfish]

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.

[subina]

created script using Jfish example. filled in my path and row to be deleted

_excel_open returns @error = 3 and @extended = -2147352567

 

 

 

[alien4u]

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.
 

[water]

Disagree. "4:4" is a perfectly valid range as described here: https://www.autoitscript.com/wiki/Excel_UDF#Cell_reference

[Jfish]

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 <> ?

[alien4u]

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.

[water]

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

[subina]

 

 

Gentlemen, I got the script from JFish to work. I thank all of your for your assist