AnuReddy Posted March 31, 2010 Share Posted March 31, 2010 hi, I want to find the used range of a particular sheet in an excel file and delete the contents.. how to do this?? Link to comment Share on other sites More sharing options...
Tvern Posted March 31, 2010 Share Posted March 31, 2010 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 Link to comment Share on other sites More sharing options...
AnuReddy Posted March 31, 2010 Author Share Posted March 31, 2010 Its throwing an error: "Requested action on this object failed" in second line Link to comment Share on other sites More sharing options...
Tvern Posted March 31, 2010 Share Posted March 31, 2010 I can't reproduce that. Try to open your Excelbook in visible mode to see if it loads properly. Link to comment Share on other sites More sharing options...
99ojo Posted March 31, 2010 Share Posted March 31, 2010 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 Link to comment Share on other sites More sharing options...
Tvern Posted March 31, 2010 Share Posted March 31, 2010 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. Link to comment Share on other sites More sharing options...
Juvigy Posted March 31, 2010 Share Posted March 31, 2010 $oExcel = ObjCreate("Excel.Application") $oExcel.Visible = 1 $oExcel.WorkBooks.Open ("C:\file.xls") $oExcel.ActiveSheet.UsedRange.ClearContents Link to comment Share on other sites More sharing options...
AnuReddy Posted March 31, 2010 Author Share Posted March 31, 2010 (edited) $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 Link to comment Share on other sites More sharing options...
Tvern Posted March 31, 2010 Share Posted March 31, 2010 This works for me: $oExcel = ExcelAttach($path) $oExcel.Application.Sheets($SheetNumberOrName).clearcontents() Link to comment Share on other sites More sharing options...
AnuReddy Posted March 31, 2010 Author Share Posted March 31, 2010 $oExcel = ExcelAttach($path) $oExcel.Application.Sheets($SheetNumberOrName).usedrange.clearcontents() 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