Delete range in excel

[Dizzy]

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

[Juvigy]

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

[Dizzy]

$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

[Manjish]

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.

[Juvigy]

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

[Dizzy]

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

Hi 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

[Juvigy]

It is the value of the Excel COM constant - xlDown.Other such constants are:

xlDown -4121: xlToLeft -4159: xlToRight -4161: xlUp -4162

[Dizzy]

It is the value of the Excel COM constant - xlDown.Other such constants are:

xlDown -4121: xlToLeft -4159: xlToRight -4161: xlUp -4162

Hi Juvigy,

thanks for help. I see, i've to dive into COM

Thx

Dizzy

[TheScarab]

$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

[13lack13lade]

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