Help with editing an Excel file

[911radio]

I have an Excel document that is run for me on a weekly basis and emailed to me. I save the file from the email as "wanted.xls" in the root of C. It has a long file and sheet name and I have to go in and delete several whole columns of information throughout that just don't pertain to what I need. I'm trying to automate deleting the first 7 columns (A-G), and other columns here and there on down to column "CW", then rename the sheet to "wanted" and then save it as a whole new document. I would also like the script to define a name of "range" to all the cells that are the end result of all the editing above, but for now I would like just to be able to nail the basics down, and what I have below really isn't doing it. Any help would be appreciated!

#include <ExcelCOM_UDF.au3>  

$FilePath = "C:\wanted.xls"
Local $oExcel = _ExcelBookOpen($FilePath, 1, 0)

Func _ExcelColumnDelete($oExcel, $iColumn = 3, $iNumCols = 1)
    If NOT IsObj($oExcel) Then Return SetError(1, 0, 0)
    If $iColumn < 1 Then Return SetError(2, 0, 0)
    For $x = 1 to $iNumCols
        $oExcel.ActiveSheet.Columns($iColumn).Delete
    Next
    Return 1
EndFunc;==>_ExcelColumnDelete

$sSheetName = 'wanted'

Func _ExcelSheetNameSet($oExcel, $sSheetName)
    If NOT IsObj($oExcel) Then Return SetError(1, 0, 0)
    $oExcel.ActiveSheet.Name = $sSheetName
    Return 1
EndFunc;==>_ExcelSheetNameSet

_ExcelBookSaveAs($oExcel, "C:\wanted2.xls", "xls", 0, 1)

_ExcelBookClose($oExcel)

[PsaltyDS]

So what exactly goes wrong? Your code is strange because you copied the functions into the script instead of just calling them. What do you get from this?

#include <ExcelCOM_UDF.au3>  

$FilePath = "C:\wanted.xls"
Local $oExcel = _ExcelBookOpen($FilePath, 1, 0)

; 2D array of colums to delete:
;       [n][0] = column to delete; [n][1] = number of columns to delete 
;       Note they are listed right to left, so deletions do not change later column numbers
; Example deletes columns 1-7, 16, 32, 64, and 128
Global $avDel[5][2] = [[128, 1], [64, 1], [32, 1], [16, 1], [1, 7]] 

For $n = 0 To UBound($avDel) - 1
    _ExcelColumnDelete($oExcel, $avDel[$n][0], $avDel[1])
Next

_ExcelBookSaveAs($oExcel, "C:\wanted2.xls", "xls", 0, 1)
_ExcelBookClose($oExcel)

Edited July 2, 2008 by PsaltyDS

[911radio]

Yeah, sorry for being a noob, I really haven't done any AutoIT scripting where I had to call functions. It opened the file and saved it, and the only thing that happened was the sheet name was shortened, but just to "wanted".

The code you provided gives me an error on Line 13; Array variable has incorrect number of subscripts or subscript dimension range exceeded.

So what exactly goes wrong? Your code is strange because you copied the functions into the script instead of just calling them. What do you get from this?

#include <ExcelCOM_UDF.au3>  

$FilePath = "C:\wanted.xls"
Local $oExcel = _ExcelBookOpen($FilePath, 1, 0)

; 2D array of colums to delete:
;       [n][0] = column to delete; [n][1] = number of columns to delete 
;       Note they are listed right to left, so deletions do not change later column numbers
; Example deletes columns 1-7, 16, 32, 64, and 128
Global $avDel[5][2] = [[128, 1], [64, 1], [32, 1], [16, 1], [1, 7]] 

For $n = 0 To UBound($avDel) - 1
    _ExcelColumnDelete($oExcel, $avDel[$n][0], $avDel[1])
Next

_ExcelBookSaveAs($oExcel, "C:\wanted2.xls", "xls", 0, 1)
_ExcelBookClose($oExcel)

[PsaltyDS]

Yeah, sorry for being a noob, I really haven't done any AutoIT scripting where I had to call functions. It opened the file and saved it, and the only thing that happened was the sheet name was shortened, but just to "wanted".

The code you provided gives me an error on Line 13; Array variable has incorrect number of subscripts or subscript dimension range exceeded.

To quote the sage philosopher, Homer: "DOH!"

Change this line (I left off one of the indexes):

_ExcelColumnDelete($oExcel, $avDel[$n][0], $avDel[$n][1])

I intentionally left the stuff about the sheet name off for now to focus on the harder part.

[911radio]

Ok, I replaced only that line and the error now says unknown function name, Line 13. And that's cool about the sheet name and stuff, I understand and appreciate it.

To quote the sage philosopher, Homer: "DOH!"

Change this line (I left off one of the indexes):

_ExcelColumnDelete($oExcel, $avDel[$n][0], $avDel[$n][1])

I intentionally left the stuff about the sheet name off for now to focus on the harder part.

Edited July 2, 2008 by 911radio

[PsaltyDS]

Ok, I replaced only that line and the error now says unknown function name, Line 13. And that's cool about the sheet name and stuff, I understand and appreciate it.

Make sure you have the latest version of ExecelCOM_UDF.au3 downloaded. Because this works perfectly for me:

#include <ExcelCOM_UDF.au3>  

; Create new workbook
Global $oExcel = _ExcelBookNew()

; Add some data
Global $avData[101]
For $n = 1 to UBound($avData) - 1
    $avData[$n] = $n
Next
For $n = 1 To 10
    _ExcelWriteArray($oExcel, $n, 1, $avData, 0, 1)
Next

; 2D array of colums to delete:
;       [n][0] = column to delete; [n][1] = number of columns to delete 
;       Note they are listed right to left, so deletions do not change later column numbers
; This example deletes columns 1-7, 16, 32, 64, and 96
Global $avDel[5][2] = [[96, 1], [64, 1], [32, 1], [16, 1], [1, 7]] 

For $n = 0 To UBound($avDel) - 1
    _ExcelColumnDelete($oExcel, $avDel[$n][0], $avDel[$n][1])
Next

_ExcelBookSaveAs($oExcel, "C:\wanted2.xls", "xls", 0, 1)
_ExcelBookClose($oExcel)

[911radio]

Make sure you have the latest version of ExecelCOM_UDF.au3 downloaded. Because this works perfectly for me:

EDIT: DOH! Ok, I'm home now and just duplicated the files so I could continue testing here. I apparently had an older version at work because it worked like a charm it seems! I also got the sheet rename to work (code below), so does that mean maybe you could help me with defining a name of "range" to all the columns and rows with data?

#include <ExcelCOM_UDF.au3>  

$FilePath = "C:\wanted.xls"
Local $oExcel = _ExcelBookOpen($FilePath, 1, 0)

$sSheetName = 'wanted'

_ExcelSheetNameSet($oExcel, $sSheetName)

; 2D array of colums to delete:
;        [n][0] = column to delete; [n][1] = number of columns to delete
;        Note they are listed right to left, so deletions do not change later column numbers
; Global $avDel[5][2] = [[128, 1], [64, 1], [32, 1], [16, 1], [1, 7]]  <== Original Example
; Example deletes columns 1-7, 16, 32, 64, and 128
Global $avDel[5][2] = [[33, 69], [18, 8], [12, 4], [1, 7]]

For $n = 0 To UBound($avDel) - 1
    _ExcelColumnDelete($oExcel, $avDel[$n][0], $avDel[$n][1])
Next

_ExcelBookSaveAs($oExcel, "C:\wanted2.xls", "xls", 0, 1)
_ExcelBookClose($oExcel)

Edited July 2, 2008 by 911radio

[PsaltyDS]

EDIT: DOH! Ok, I'm home now and just duplicated the files so I could continue testing here. I apparently had an older version at work because it worked like a charm it seems! I also got the sheet rename to work (code below)

Glad you got it working!

...so does that mean maybe you could help me with defining a name of "range" to all the columns and rows with data?

Actually, I don't even know what that means. I'm not really an Excel geek, and I didn't even know you could name a dis-contiguous list of cells like that. Did you look at _ExcelSheetUsedRangeGet(), maybe?

[911radio]

Glad you got it working!

Actually, I don't even know what that means. I'm not really an Excel geek, and I didn't even know you could name a dis-contiguous list of cells like that. Did you look at _ExcelSheetUsedRangeGet(), maybe?

No, haven't looked at that yet. Maybe my describing what I do manually will help you (or someone) help me along if I don't figure it out. I'm not an Excel geek either...

Once I have deleted all the columns, I wind up with Cols A-M (13) but the number of rows varies in the 450 - 550 range. I highlight all the rows and columns, click Insert, then Name, then Define and just type in "range" and then click ok. This makes it so that =wanted!$A$1:$M$466 is defined as "range". That way, I can run SQL queries on that name etc.

Since the number of rows varies from week to week, I would assume that whatever code I used would have to parse the rows and find the last row that has data in it, then use that as the last row number in the range.

EDIT: I located some code that figures out the number of the last row that has data in a certain column (in my case, column E, or the 5th column is always guaranteed to have data in the cell) and it works great, now if I can only put it to use in defining the name.

Edited July 3, 2008 by 911radio

[soulhealer]

where do u get ExcelCOM_UDF.au3 include file, i'm trying to find it on my autoit(beta and non beta) include dirs, i can't find it...

[911radio]

where do u get ExcelCOM_UDF.au3 include file, i'm trying to find it on my autoit(beta and non beta) include dirs, i can't find it...

http://www.autoitscript.com/forum/index.ph...xcelCOM_UDF.au3

It isn't part of the regular install. Download it from the bottom of the first topic in the thread.

Edited July 6, 2008 by 911radio

[Jeemo]

Once I have deleted all the columns, I wind up with Cols A-M (13) but the number of rows varies in the 450 - 550 range. I highlight all the rows and columns, click Insert, then Name, then Define and just type in "range" and then click ok. This makes it so that =wanted!$A$1:$M$466 is defined as "range". That way, I can run SQL queries on that name etc.

Hey man - Excel geek here, at your service.

Check out the following code. I took the last snippet of yours and added a couple lines (see the comments on lines 21, 22 and 25 once you paste this back to SciTE) to enable you to define a named range.

Good luck dude,

Jeemo

#include <ExcelCOM_UDF.au3>  

$FilePath = "C:\wanted.xls"
Local $oExcel = _ExcelBookOpen($FilePath, 1, 0)

$sSheetName = 'wanted'

_ExcelSheetNameSet($oExcel, $sSheetName)

; 2D array of colums to delete:
;         [n][0] = column to delete; [n][1] = number of columns to delete
;         Note they are listed right to left, so deletions do not change later column numbers
; Global $avDel[5][2] = [[128, 1], [64, 1], [32, 1], [16, 1], [1, 7]]  <== Original Example
; Example deletes columns 1-7, 16, 32, 64, and 128
Global $avDel[5][2] = [[33, 69], [18, 8], [12, 4], [1, 7]]

For $n = 0 To UBound($avDel) - 1
    _ExcelColumnDelete($oExcel, $avDel[$n][0], $avDel[$n][1])
Next

Global $xlRange = "=" & $sSheetName & "!$A$1:$M$466"        ; $xlRange simply concatenates to form "=wanted!$A$1:$M$466" (the range from your last post)
$oExcel.ActiveWorkbook.Names.Add("WantedRange",$xlRange)    ; Invoke the 'Add' method of the 'Names' object collection (in the ActiveWorkbook context) with the two parameters you need. The first parameter will be the name of the range.

_ExcelBookSaveAs($oExcel, "C:\Wanted2.xls", "xls", 0, 1)
;_ExcelBookClose($oExcel)                                   ; I just commented this out so that it would leave the file open so you can check it out.

Edited July 7, 2008 by Jeemo

[911radio]

Hey man - Excel geek here, at your service.

Check out the following code. I took the last snippet of yours and added a couple lines (see the comments on lines 21, 22 and 25 once you paste this back to SciTE) to enable you to define a named range.

Good luck dude,

Jeemo

CODE

#include <ExcelCOM_UDF.au3>

$FilePath = "C:\wanted.xls"

Local $oExcel = _ExcelBookOpen($FilePath, 1, 0)

$sSheetName = 'wanted'

_ExcelSheetNameSet($oExcel, $sSheetName)

; 2D array of colums to delete:

; [n][0] = column to delete; [n][1] = number of columns to delete

; Note they are listed right to left, so deletions do not change later column numbers

; Global $avDel[5][2] = [[128, 1], [64, 1], [32, 1], [16, 1], [1, 7]] <== Original Example

; Example deletes columns 1-7, 16, 32, 64, and 128

Global $avDel[5][2] = [[33, 69], [18, 8], [12, 4], [1, 7]]

For $n = 0 To UBound($avDel) - 1

_ExcelColumnDelete($oExcel, $avDel[$n][0], $avDel[$n][1])

Next

Global $xlRange = "=" & $sSheetName & "!$A$1:$M$466" ; $xlRange simply concatenates to form "=wanted!$A$1:$M$466" (the range from your last post)

$oExcel.ActiveWorkbook.Names.Add("WantedRange",$xlRange) ; Invoke the 'Add' method of the 'Names' object collection (in the ActiveWorkbook context) with the two parameters you need. The first parameter will be the name of the range.

_ExcelBookSaveAs($oExcel, "C:\Wanted2.xls", "xls", 0, 1)

;_ExcelBookClose($oExcel) ; I just commented this out so that it would leave the file open so you can check it out.

Hey Jeemo,

Very awesome, thanks! I incorporated your code with the last bit of code I came across that wasn't in the snippet above. Its what figures out the last row of data for me, because the number of rows varies (it wont always be 466). E is the column I know will always have data in every cell, and this will give me the last cell at the bottom and puts it into "$iLastUsed".

Here is the finished script and it works flawlessly! Thanks Jeemo and PsaltyDS!!

expandcollapse popup

#include <ExcelCOM_UDF.au3>  

$FilePath = "C:\wanted_list_by_disposition_status.xls"
Local $oExcel = _ExcelBookOpen($FilePath, 1, 0)

$sSheetName = 'wanted'
_ExcelSheetNameSet($oExcel, $sSheetName)

; 2D array of colums to delete:
;        [n][0] = column to delete; [n][1] = number of columns to delete
;        Note they are listed right to left, so deletions do not change later column numbers
; Global $avDel[5][2] = [[128, 1], [64, 1], [32, 1], [16, 1], [1, 7]]  <== Original Example
; Example deletes columns 1-7, 16, 32, 64, and 128
Global $avDel[5][2] = [[33, 69], [18, 8], [12, 4], [1, 7]]

For $n = 0 To UBound($avDel) - 1
    _ExcelColumnDelete($oExcel, $avDel[$n][0], $avDel[$n][1])
Next

; Read column E to an array
Global $avData = _ExcelReadArray($oExcel, 1, 5, 1000, 1, 1)

; find the last used cell in this column
Global $iLastUsed = 0, $iNextRow = 0
For $n = UBound($avData) - 1 To 1 Step -1
    If StringStripWS($avData[$n], 8) <> "" Then
        $iLastUsed = $n
        ExitLoop
    EndIf
Next
If $iLastUsed Then
    $iNextRow = $iLastUsed + 1
EndIf

Global $xlRange = "=" & $sSheetName & "!$A$1:$M" & $iLastUsed; $xlRange simply concatenates to form "=wanted!$A$1:$M$466" (the range from your last post)
$oExcel.ActiveWorkbook.Names.Add("range",$xlRange); Invoke the 'Add' method of the 'Names' object collection (in the ActiveWorkbook context) with the two parameters you need. The first parameter will be the name of the range.

_ExcelBookSaveAs($oExcel, "C:\wanted.xls", "xls", 0, 1)
;_ExcelBookClose($oExcel)

Edited July 7, 2008 by 911radio

[Jeemo]

You got it.

Also (and this goes for anyone), if your AutoIT script is going to be doing lotsssss of shifting things around, it might speed things up a bit to drop the following into your code right after invoking _ExcelBookNew() or _ExcelBookOpen():

$oExcel.Application.ScreenUpdating = False

This will keep Excel from showing evvvvverything that's going on. Then at the end of the script (or wherever you want to start seeing what your script is doing), just drop the same line from above but change it back to 'True'. I tried it with the code from earlier in the thread (the one that writes arbitrary data before it deletes the "unwanted" columns), and it cut the execution time from around 19 seconds down to around 15 (and what the code was doing was not a major operation).

In truth, nothing will be faster than using VBA in an Excel macro (especially one that utilizes the ScreenUpdating property). With AutoIt, every last action is being performed as if being entered as fast as physically possible with a keyboard and mouse. Even then, the code example from before takes about 15 seconds, but with VBA would be done in an instant. If you go to Excel, hit Alt+F11 to bring up the VBA editor, and then hit F1 (especially useful if you record a macro) - that's how I learned everything I know about VBA.

By default, a macro is stored in the particular workbook to which you want to apply it, and can only be called when that workbook is open. But if you have macros that you would need to operate upon any workbooks in excel, you can save it as a module in the Personal.xls file (Google it), which basically acts as in #include that you never have to reference. If you get comfortable with all this - which I highly recommend you to do if you ever need to automate Excel tasks beyond a casual level - then there's a post here on how to invoke Excel macros from AutoIT.

Good luck,

Jeemo

Edited July 7, 2008 by Jeemo

[911radio]

Ok, I might have spoke a little too soon. It looked like it was going to work ok, but when I go to put it to use I get "The Microsoft Jet database engine could not find the object 'range'.", so something just isn't quite taking right. BTW, I added your $oExcel.Application.ScreenUpdating = False and that worked good.

Thanks,

David

[Jeemo]

Ok, I might have spoke a little too soon. It looked like it was going to work ok, but when I go to put it to use I get "The Microsoft Jet database engine could not find the object 'range'.", so something just isn't quite taking right.

That's strange, I don't think I've ever seen a Jet error in Excel. The Jet engine is what drives MS Access - I had no idea that it pertained to Excel at all. Can you post the entire contents of your script that's giving you that error?

[911radio]

That's strange, I don't think I've ever seen a Jet error in Excel. The Jet engine is what drives MS Access - I had no idea that it pertained to Excel at all. Can you post the entire contents of your script that's giving you that error?

Sorry, what it is, after I finish with excel I load it onto a web server and an ASP script parses it and extracts and formats the data as a web page. This way i avoid taking the excel data and putting it into an access DB etc. It works like a charm when I manually edit the excel file, but if all the data in the cells isnt properly named the ASP script cant do anything with it.

[Jeemo]

So is it still not working? It seems like, based on the error message saying "cannot find the object 'range'", the problem might be in whatever is trying to reference your source Excel file. Can you post some specific examples?

[911radio]

<%
' Set Connection Params
dim mySQL
Set oConn = Server.CreateObject("ADODB.connection")
oConn.Open "Driver={Microsoft Excel Driver (*.xls)};" & _
           "DriverID=790;" & _
           "DBQ=E:\Web\County\sheriff\wanted.xls;" & _
           "DefaultDir=E:\Web\County\sheriff\" 

Set RS=Server.CreateObject("ADODB.recordset")
Set RS2=Server.CreateObject("ADODB.recordset")

' Write the SQL Query
RS.open "SELECT * FROM range", oConn
RS2.open "SELECT Count(*) AS intTotal FROM range", oConn

'RS2.open "SELECT count(DISTINCT namedesc) AS intTotal FROM range", oConn

'RS2.open "Select Count(*) from (SELECT DISTINCT namedesc From range) As q", oConn

' Display result
'Response.Write (RS2("q")) 
Response.Write (RS2("intTotal")) 
Response.Write (" total records displayed below.")

That code is part of my ASP script .

Like I said, when I edit the Excel file manually, and upload it to the web server its fine, but when the file is edited by the script, my ASP script cant figure out which cells are named 'range' and therefore cant execute the SQL queries. Now when I go back and look, it has given the name 'range' to $A$1:$M$901 instead of where the data ends at M455 in this particular file. And I double checked and the script does pass the proper count to $iLastUsed. Then I will look at the defined name 'range' and it shows $A$1:$M$455 so something is just not happening right somewhere.

[Jeemo]

Hmmmmmm.... Let me ask you this: Your AutoIT script that is defining the named range - is it running on an Excel file that it has already ran against (or that you have already manually created a range named 'range')? If so, then the problem might be with re-assigning different range values to an existing range name. If that's the case, let me know and I'll see if I can throw together something that will either perform a "rename" operation or delete the original range and re-create it with the new values.

Jeemo