Excel Related

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

[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

[AnuReddy]

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

[Tvern]

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

[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

[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.

[Juvigy]

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

[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 March 31, 2010 by AnuReddy

[Tvern]

This works for me:

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

[AnuReddy]

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