Dizzy Posted November 4, 2009 Share Posted November 4, 2009 (edited) Hi everybody, i'm searching a function to delete the content of a range in excel. My file has about 10000 columns and 20 rows. I want to clear the content of cells from column 20 to the end of table in each rows from 5 to 15. To do this with for/next takes tooooo long. So is there a short way to mark the range and clear the content? Thanks for every idea! Dizzy Edited November 4, 2009 by Dizzy Link to comment Share on other sites More sharing options...
Juvigy Posted November 4, 2009 Share Posted November 4, 2009 $oExcel = ObjCreate("Excel.Application") $oExcel.Visible = 1 $oExcel.WorkBooks.Open ($filename1) $oExcel.Range ("D5","F15").Select See the above example - it selects the cells from D5 to F15. Then you can just delete the contents. Link to comment Share on other sites More sharing options...
Dizzy Posted November 4, 2009 Author Share Posted November 4, 2009 $oExcel = ObjCreate("Excel.Application") $oExcel.Visible = 1 $oExcel.WorkBooks.Open ($filename1) $oExcel.Range ("D5","F15").Select See the above example - it selects the cells from D5 to F15. Then you can just delete the contents. Works perfect! 2 little questions more? How can i get the last column of the worksheet with ExcelCOM? Did i have to read the file into an array to get the last line with: $array = _ExcelSheetUsedRangeGet($file, 1) $lastline = $array[3] I think, there is a faster way in COM ..., right? And where can i get (see) all posibilities offered with COM? The commands you take aren't displayed by scite "intellisence" ... Thanks! Dizzy Link to comment Share on other sites More sharing options...
Manjish Posted November 4, 2009 Share Posted November 4, 2009 to answer your second question: Go to the "Include" folder and checkout the Excel.au3 UDF. Or alternatively go to contents of helpfile and checkout the Excel Management. Also, if you need to make your own function in Excel, the best way is to record a macro to do whatever you want and the export that code into autoit. [font="Garamond"]Manjish Naik[/font]Engineer, Global Services - QPSHoneywell Automation India LimitedE-mail - Manjish.Naik@honeywell.com Link to comment Share on other sites More sharing options...
Juvigy Posted November 4, 2009 Share Posted November 4, 2009 For question 2: For the Excel COM you will have to visit the MSDN. For example: http://msdn.microsoft.com/de-de/library/microsoft.office.interop.excel.range.select%28office.11%29.aspx For question 1: If there is no gaps (empty cells) an easy ways is to do: $oExcel.Application.ActiveSheet.Range("A1").End(-4121).Select Link to comment Share on other sites More sharing options...
Dizzy Posted November 5, 2009 Author Share Posted November 5, 2009 For question 2:For the Excel COM you will have to visit the MSDN.For example:http://msdn.microsoft.com/de-de/library/microsoft.office.interop.excel.range.select%28office.11%29.aspxFor question 1:If there is no gaps (empty cells) an easy ways is to do:$oExcel.Application.ActiveSheet.Range("A1").End(-4121).SelectHi Juvigy,thanks for reply and thanks for the link!There are no gaps in my list, so i can try your code.Can you explain the -4121? Dizzy Link to comment Share on other sites More sharing options...
Juvigy Posted November 5, 2009 Share Posted November 5, 2009 It is the value of the Excel COM constant - xlDown.Other such constants are: xlDown -4121: xlToLeft -4159: xlToRight -4161: xlUp -4162 Link to comment Share on other sites More sharing options...
Dizzy Posted November 10, 2009 Author Share Posted November 10, 2009 It is the value of the Excel COM constant - xlDown.Other such constants are:xlDown -4121: xlToLeft -4159: xlToRight -4161: xlUp -4162Hi Juvigy,thanks for help. I see, i've to dive into COM ThxDizzy Link to comment Share on other sites More sharing options...
TheScarab Posted October 26, 2014 Share Posted October 26, 2014 (edited) $oExcel = ObjCreate("Excel.Application") $oExcel.Visible = 1 $oExcel.WorkBooks.Open ($filename1) $oExcel.Range ("D5","F15").Select See the above example - it selects the cells from D5 to F15. Then you can just delete the contents. This code is an example of what I always find. It (per my understanding and experimenting) opens a blank Excel spreadsheet and works from there. My problem is that I have an existing spreadsheet (that may or may not be opened yet) and I need to clear a range before I paste new data into it. I cannot seem to get a way to pass MY spreadsheet to autoit. I have included my current code below (with multiple trys commented out). Can someone help me to see what I am doing wrong? ;need to clear the cells a2:w10001 ;$MyExcel = Objget("Excel.Application") ;$MyExcel = ("Microsoft Excel - Particle.xlsm") $MyExcel = ("Particle.xlsm") MsgBox(262144, "Testing", "Cell clear algorithm starting...",0) ;WITH $MyExcel.activesheet ;MsgBox(262144, "Testing", "inside the 'with'...",0) ;.range("A2:W10001").select ;.range("A2:W10001").clear ;MsgBox(262144, "Testing", "cells should be clear...",0) ;ENDWITH ;$MyExcel.activesheet.range("A2:W10001").select ;$MyExcel.activesheet.range("A2:W10001").clear ;$oExcel = ObjCreate("Excel.Application") ;$oExcel.Visible = 1 ;$oExcel.WorkBooks.Open ($filename1) $MyExcel.Range ("A2","W10001").clear MsgBox(262144, "Testing", "finished clearing...",0) Sleep(1000) I get this error currently and do not know what to do about it: "C:UserskscarabiDesktopAspex Project0000Testingtemp.au3" (32) : ==> Variable must be of type "Object".: $MyExcel.Range ("A2","W10001").clear $MyExcel^ ERROR Edited October 26, 2014 by TheScarab Link to comment Share on other sites More sharing options...
13lack13lade Posted October 26, 2014 Share Posted October 26, 2014 (edited) i could be wrong, but i am quite sure you will have to create the object regardless.. - This *should* delete the range.. however with something like this, especially since you are saying the workbook may be open... if that is the case it would be simpler to create a VBA macro like this: sub clearrange() sheets("Sheetname").activate range("A2:W10001").select selection.clearcontents end sub Otherwise this will open the book and delete the range: #include <Excel.au3> $oExcel = _Excel_Open() $Workbook = _Excel_BookOpen($oExcel,'C:\filepath\to\your\worksheet.xlsm') $range = "A2:W100001" _Excel_RangeDelete($Workbook.ActiveSheet, $Range, $xlShiftUp) _Excel_Close($oExcel) Alternatively if you are going to use the VBA macro in your workbook(since i see it is .xlsm anyway and probably a better way of doing it) you could alternatively just open the workbook and call the macro when it is not open using this: #include <Excel.au3> $oExcel = _Excel_Open() $Workbook = _Excel_BookOpen($oExcel,'C:\filepath\to\your\worksheet.xlsm') $oExcel.Run("clearrange") _Excel_Close($oExcel) The helpfile will correctly show you how to structure autoit code for _Excel , its all there.... alternatively you can just use VBA depending on what you require.. for a simple clear contents on 1 workbook im not sure what the benefit of autoit would be except for being able to run the built in macros on demand without actually having the book open or visible .. But! each to their own! Edited October 26, 2014 by 13lack13lade 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