subina

deleting excel row

20 posts in this topic

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

 

Share this post


Link to post
Share on other sites



#2 ·  Posted (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 by Jfish

Build your own poker game with AutoIt: pokerlogic.au3 | Learn To Program Using FREE Tools with AutoIt

Share this post


Link to post
Share on other sites

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

 

Share this post


Link to post
Share on other sites

ok i tried this

 

$srange = "4:4"

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

IT DID NOT WORK

 

Share this post


Link to post
Share on other sites

#5 ·  Posted (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 by Jfish

Build your own poker game with AutoIt: pokerlogic.au3 | Learn To Program Using FREE Tools with AutoIt

Share this post


Link to post
Share on other sites

#6 ·  Posted (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 by water

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
Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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

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

did not work

Share this post


Link to post
Share on other sites

by dont work means that it failed to delete the row

Share this post


Link to post
Share on other sites

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 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
Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

 

Share this post


Link to post
Share on other sites

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 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
Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

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

 

 

 

Share this post


Link to post
Share on other sites

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.
 

Share this post


Link to post
Share on other sites

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 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
Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

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 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
Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#20 ·  Posted

 

 

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

 

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