Help with editing an Excel file

[911radio]

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

No, this is an Excel file, that was hot off the software that exported that report and emailed to me. I get it and (would) run the AutoIT script and then upload the excel file to the web server. But what you said at the end may be what we should try.

[Jeemo]

Okay, so you haven't messed with it at all at the point when you're getting that Jet engine / named 'range' error, right? If so, try this - open the Excel file as-is, then click the little dropdown arrow on the Name Box (if you don't know what that is, the Name Box is on the left just above the column headers, and displays the cell address that's currently active). When you click the dropdown, does it display a named range that's already there?

[911radio]

Ok, when I get that excel file emailed to me, I have to mess with it, either manually as I have been doing or via the AutoIT script. If I do all the things that script does by hand and upload it to the web server, it works fine. If I let the scritp do what I intend for it to do and upload the file I get that Jet engine / named 'range' error. Either way I have to do something with the file before I upload it, it just wont work "out of the box".

When I click the name box, all it says and continues to say is A1. (I did this on the completely unedited file as it came to me through email, and it never gets touched by the person sending it)

Edited July 7, 2008 by 911radio

[Jeemo]

Alright, I think I understand the flow of things now but I want to make sure we're on the same page.

So when you manually name the range, everything works fine. But naming it via the script, it breaks.

Have you tried this? Perform the manual steps on the file in it's out-of-box state and save it as TestManual.xls. Then run the script against another clean, out-of-box copy of the Excel file and save it as TestAuto.xls. Compare the two files and examine the following very closely:

1) Do they have the same contents?

2) Is the column count the same?

3) Is the row count the same?

4) When you click the drop-down arrow next to the Name Box, do the same named ranges appear, identically spelled?

4a) If the same named ranges do appear, click on it to highlight said range in the worksheet. Does it highlight the exact same range in both TestManual and TestAuto?

If you've done all of the above and the answer to everything is 'yes', let me know and we'll take it from there. I want to make sure that the script is actually doing the exact same things as when you do it manually.

[911radio]

Yes to all of the above, with the exception of #4 & #4a (sort of).

In the manually edited file, when I open it and click the name box and select "range", range starts off fully to the left in that box and after chosen it stays in the box but centered

In the AutoIT edited file, when I open it and click the name box and select "range", range starts off fully to the left in that box and after chosen A1 comes up and stays centered in the box.

I know that sounds crazy but there is a slight difference in the way the 2 files act. Other than that, no difference in 1-3. Even the file sizes are the same.

[Jeemo]

In the manually edited file, when I open it and click the name box and select "range", range starts off fully to the left in that box and after chosen it stays in the box but centered

In the AutoIT edited file, when I open it and click the name box and select "range", range starts off fully to the left in that box and after chosen A1 comes up and stays centered in the box.

It sounds like there's something not quite right with the way the script is creating the range. In TestAuto.xls, does selecting the named range actually highlight your target range? Also, what version of Excel are you using?

[911radio]

It sounds like there's something not quite right with the way the script is creating the range. In TestAuto.xls, does selecting the named range actually highlight your target range? Also, what version of Excel are you using?

Yes, selecting the named range highlights my target range and I'm using Excel 2003 both at home and at work.

[911radio]

Jeemo!

Its all my fault (I know you knew that already!) muttley . I just went back and looked at the name in the file and then looked at my code one last time...

Global $xlRange = "=" & $sSheetName & "!$A$1:$M" & $iLastUsed;  <- I screwed up this code and left out the "$" after the $M

It should be:

Global $xlRange = "=" & $sSheetName & "!$A$1:$M$" & $iLastUsed;

Just uploaded the file to the webserver. Now it works 100%. Jeemo's code was great as was PsaltyDS's. Thanks guys!

Now, one last little question The original file I am working with actually comes to me named something like wanted_list_by_disposition_status07022008_07_07_53.xls The numbers at the end are the date and the time the file was created. All I know is each time it gets emailed to me its difference because that's the way the program exports the reports. Instead of me having to rename it to wanted_list_by_disposition_status.xls each time, can the script open up the file with a wildcard, something like wanted_list_by_disposition_status*.xls (I know thats not what it is but you get the idea)

There will only ever be one file like that in the directory because I am going to have the script delete the file it originally opened after it is done saving the edited file that it renames to wanted.xls

Thanks!

Edited July 8, 2008 by 911radio

[Jeemo]

Global $strDir = "C:\Excel\"                                        ; Your source directory - notice the trailing \ (backslash)
Global $strFile = "wanted_list_by_disposition_status"               ; File name you listed in last post
FileMove($strDir & $strFile & "*.xls", $strDir & $strFile & ".xls") ; FileMove simply renames the file - FileMove(oldName, newName).

FileMove() can move and/or rename files, so we're using it just to rename. In the last line above, the string concatenation in the first FileMove() parameter translates to "C:\Excel\wanted_list_by_disposition_status*.xls" - exactly like the wildcard suggestion you made in your last post. It then renames it by parsing the second FileMove() parameter.

Edited July 8, 2008 by Jeemo

[911radio]

Thanks but I think you missed my point a little. I rename the file manually because the name of the file changes and if I just left it alone (with dates and random numbers in the file names) the script would never know where to find it and open it. I dont want to rename the file that gets opened and edited at all, whether it be manually or by the script. I just want the script to look for a file named "wanted_list_by_disposition_status_something*.*something.xls" and open it so it can be edited.

Edited July 8, 2008 by 911radio

[Jeemo]

Ahhhhh - in that case you want to find out what the file name is, assign that file name to your $filePath variable for use in _ExcelBookOpen($FilePath, 1, 0).

I can't make the code for you because I'm at work, but you can definitely put it together if you go to AutoIT help, click the Index tab and type in FileFindFirstFile. If you still can't get it after hacking around with it then let me know.

Jeemo

[911radio]

Ok, I checked it out and the example they use works fine to find my file, but when I integrate it into my code I get

$oExcel.Application.ScreenUpdating = False

$oExcel.^ERROR

Error: Variable must be of the type "Object".

And yes, I am using FileClose($search) at the end.

Snippet:

#include <ExcelCOM_UDF.au3>  

$search = FileFindFirstFile("wanted_list_by_disposition_status*.xls")  

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

$oExcel.Application.ScreenUpdating = False

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

Edited July 8, 2008 by 911radio

[Jeemo]

The problem is the FileFindFirstFile needs to be used in tandem with FileFindNextFile in order to actually return a file name. Also, FileFindFirstFile searches the "current" directory, which is probably the directory in which the script is running. My bad, I've never used either of these before.

I added the lines below which should get you what you want.

#include <ExcelCOM_UDF.au3>  

#Region ======== Jeemo additions ==========

Global $strDir = "C:\Excel"
FileChangeDir ( $strDir ) ; Change the "current" directory from that of the script to the one where the Excel file is located

Global $search = FileFindFirstFile("wanted_list_by_disposition_status*.xls")  ; This will return "1", not the filename
Global $file = FileFindNextFile($search)  ; THIS will return the file name, based on the results of FileFindFirstFile

#EndRegion ===== Jeemo additions ==========

; Local $oExcel = _ExcelBookOpen($search, 1, 0)
Local $oExcel = _ExcelBookOpen($file, 1, 0)   ; we're using $file here instead of $search

$oExcel.Application.ScreenUpdating = False

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

Edited July 8, 2008 by Jeemo

[911radio]

Thanks. I'm getting a Line 227 (ExcelCOM_UDF.au3) Error

If $sPassword = "" And $sWritePassword = "" Then .WorkBooks.Open($sFilePath, Default, $fReadOnly)

If $sPassword = "" And $sWritePassword = "" Then .WorkBooks.Open($sFilePath, Default, $fReadOnly)^ERROR

Error: The requested action with this object has failed.

The file I'm trying to open is not already open, just in case you ask muttley

[Jeemo]

Can you include the lines of code that effect that error? I.e., any line that modifies the value of the variables $sFilePath or $fReadOnly, whether the If statement evaluates to true or false, and the full context of the With... Wend block.

[911radio]

Here is all of what I have if it helps...

expandcollapse popup

#include <ExcelCOM_UDF.au3> 

#Region ======== Jeemo additions ==========

Global $strDir = "C:\Excel"
FileChangeDir ( $strDir ); Change the "current" directory from that of the script to the one where the Excel file is located

Global $search = FileFindFirstFile("wanted_list_by_disposition_status*.xls") ; This will return "1", not the filename
Global $file = FileFindNextFile($search) ; THIS will return the file name, based on the results of FileFindFirstFile

#EndRegion ===== Jeemo additions ==========

; Local $oExcel = _ExcelBookOpen($search, 1, 0)
Local $oExcel = _ExcelBookOpen($file, 1, 0)  ; we're using $file here instead of $search

$oExcel.Application.ScreenUpdating = False

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

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"  & $iLastUsed tacks on last row of data in lieu of $466
$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.

$oExcel.Application.ScreenUpdating = True

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

;FileDelete($file)

[Jeemo]

Found it - you should make the following changes:

Look at lines 5...

Global $strDir = "C:\Excel\"oÝ÷ Ù©Ý×®¶­sdvÆö&Âb33c¶ôW6VÂÒôW6VÄ&öö´÷Vâb33c·7G$F"fײb33c¶fÆR²vRb33·&RW6ærb33c¶fÆRW&Rç7FVBöbb33c·6V&6

Line 14 used to read "_ExcelBookOpen($file, 1, 0)". I got rid of the 1 and 0 because they are optional and unnecessary in your case. The problem, however, was with just using $file. The function _ExcelBookOpen needs the value of the first parameter to contain the entire path of the Excel book, not just the file name. You get that by concatenating the value of $strDir & $strFile. (I also changed 'Local' to 'Global' - not necessary, but to follow best practice)

In line 5, all I did was add a trailing backslash so that you don't have to write _ExcelBookOpen($strPath & '\' & $file). Make sure you change the value of $strDir from "C:\Excel\" to whatever directory you actually use for your file.

The bottom line is that if your file is called MyExcel.xls and it's located in C:\MyDirectory, then the value that's used in _ExcelBookOpenFile() needs to translate to C:\MyDirectory\MyExcel.xls.

[911radio]

Sweet! That did it! Script finished! Thanks a ton!