Sign in to follow this  
Followers 0
Dizzy

Delete range in excel

10 posts in this topic

#1 ·  Posted (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 by Dizzy

Share this post


Link to post
Share on other sites



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

Share this post


Link to post
Share on other sites

$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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

#9 ·  Posted (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 by TheScarab

Share this post


Link to post
Share on other sites

#10 ·  Posted (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 by 13lack13lade

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  
Followers 0