Jump to content
Sign in to follow this  
AnuReddy

Excel Related

Recommended Posts

AnuReddy

hi,

I want to find the used range of a particular sheet in an excel file and delete the contents.. how to do this??

Share this post


Link to post
Share on other sites
Tvern

Effectively you want to clear the entire sheet right? Try this:

$sheet = 1 ;can be sheet number or name
$oExcel.ActiveWorkbook.Sheets($sheet).Select ()
$oExcel.Cells().ClearContents

Share this post


Link to post
Share on other sites
AnuReddy

Its throwing an error: "Requested action on this object failed" in second line

Share this post


Link to post
Share on other sites
Tvern

I can't reproduce that. Try to open your Excelbook in visible mode to see if it loads properly.

Share this post


Link to post
Share on other sites
99ojo

Hi,

i suppose you haven't have an excel object. See helpfile _ExcelBookOpen() or _ExcelBookAttach().

This must be before $oExcel.ActiveWorkBook....

$oExcel = _ExcelBookOpen ("<fullpath to excelworkbook>") ; or your sheet is in ScriptDir
;$oExcel = _ExcelBookOpen ("workbookname.xls")
;cleaning contents of 1.st sheet in workbook
$sheet = 1 ;can be sheet number or name
$oExcel.ActiveWorkbook.Sheets($sheet).Select ()
$oExcel.Cells().ClearContents

;-))

Stefan

Share this post


Link to post
Share on other sites
Tvern

I assume too much :( thanks 99ojo.

@AnuReddy: Don't forget to finish with:

_ExcelBookSave($oExcel)
_ExcelBookClose($oExcel)

Otherwise your changes won't be saved, or excel will keep running in the background.

Share this post


Link to post
Share on other sites
Juvigy

$oExcel = ObjCreate("Excel.Application")
$oExcel.Visible = 1
$oExcel.WorkBooks.Open ("C:\file.xls")
$oExcel.ActiveSheet.UsedRange.ClearContents

Share this post


Link to post
Share on other sites
AnuReddy

$oExcel = ExcelAttach($path)

$oExcel.Application.ActiveSheet.UsedRange.clearcontents()

This is working fine for a particular active sheet but if i a particular sheet to be identified by name and then clear the contents its not working fine

Edited by AnuReddy

Share this post


Link to post
Share on other sites
Tvern

This works for me:

$oExcel = ExcelAttach($path)
$oExcel.Application.Sheets($SheetNumberOrName).clearcontents()

Share this post


Link to post
Share on other sites
AnuReddy

$oExcel = ExcelAttach($path)
$oExcel.Application.Sheets($SheetNumberOrName).usedrange.clearcontents()

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  

×