Update the Excel UDF that comes with AutoIt

[nitekram]

Is there a zip version of the BETA - I have tried the portal wrapper in the past and it just sits there for hours - not sure if it ever does anything.

If no zip version - can someone PM me the function _Excel_RangeRead and anything else needed to run it?

Thanks

- sorry for hijacking thread

Edited December 20, 2012 by nitekram

[water]

Unfortunately the function itself doesn't help. You need the complete UDF plus the beta of AutoIt.

[water]

Nitekram,

the function can be extracted from the Alpha version of the new Copy the new functions to your script and pass the necessaray parameters.

The Range functions should work with all AutoIt versions (no beta function used).

[nitekram]

Water,

Not at work today, but thank you for your help...looking forward to testing!

[nitekram]

Anyway to tell _Excel_RangeRead() to read the whole sheet without know the size of it before execution? Maybe I am missing how to do it?

I see this

; $vRangeOrRow - Either an A1 range or an integer row number to read from if using R1C1

; $iColumn - Optional: The column to read from if using R1C1 (default = 1)

; $iReturn - Optional: What to return of the specified cell:

Still in the testing phase, but it appears to be "A HELL OF A LOT FASTER", how is it possible that it was taking over 3 mins on 2007 and then over 15 mins for 2010 - and now, it is taking about 2 seconds...was there something wrong with the other excel UDF or was it something I was doing - EITHER way, what a huge change and I thank you from the bottom of my heart

HAPPY HOLIDAYS

[water]

The UDF is still in an early alpha state but the function will accept parameters to read the whole sheet.

The new UDF is much faster because it reads and writes the array in a single operation now whereas the "old" UDF processed cell by cell.

[GMK]

Perhaps some default parameters that use the UsedRange methods are in order?

[water]

That is exactly what I plan to do. I hope to release a new Alpha till end of January.

[nitekram]

@water - I may have found a bug.

When you use _Excel_Open(False),(application should not be visible) and open another excel file, the one that is supposed to be hidden appears. I am using Office 2010.

To fix this I used the _Excel_BookClose($oOExcel, $oWorkbook) - may need to add that to your example???

$ExcelFile = FileOpenDialog('Look GCAR report spreadsheet', 'c:\', "Excel (*.xls;*.xlsx)", 1 + 2)

Global $oOExcel = _Excel_Open(False)
If @error <> 0 Then
;Return
MsgBox(16, "Excel UDF: _Excel_Open " & $ExcelFile, "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
EndIf

Global $oWorkbook = _Excel_BookOpen($oOExcel, $ExcelFile, True)
If @error <> 0 Then
    If @error <> 0 Then
    ;Return
    MsgBox(16, "Excel UDF: _Excel_BookOpen " & $ExcelFile, "Error opening '" & $ExcelFile & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    EndIf
    _Excel_Close($oOExcel)
    Exit
EndIf

$aArrayExcel = _Excel_RangeRead($oOExcel, $oWorkbook, Default, "A7:BC750")
_Excel_BookClose($oOExcel, $oWorkbook)
_Excel_Close($oOExcel)

[water]

Thanks for this report.

I will check as soon as I return from vacation (mid January).

[nitekram]

While using the example I provide, it appears that once you try to open another excel file - the new file appears to open minimized. It does not appear to happen the next time you open said file. But I am able to replicate it each time I close out of my script, start my script and upload my array from my excel file, then once I open a new different excel file - it opens each time minimized.

[water]

I tested the latest Alpha with this reproducer script. It starts Excel and opens two workbooks, then sets visibility of the Excel application to true.

Both workbooks don't show up until visibility is set to true.

I use Excel 2010 as well.

#include <Excel Rewrite.au3>

Global $oExcel = _Excel_Open(False)
If @error <> 0 Then
    MsgBox(16, "Excel UDF: _Excel_Open", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
EndIf

Global $ExcelFile1 = FileOpenDialog('Select Worksheet', 'H:\tools\AutoIt3\ExcelEX', "Excel (*.xls;*.xlsx)", 1 + 2)
Global $oWorkbook1 = _Excel_BookOpen($oExcel, $ExcelFile1, True)
If @error <> 0 Then
    MsgBox(16, "Excel UDF: _Excel_BookOpen", "Error opening '" & $ExcelFile1 & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    _Excel_Close($oExcel)
    Exit
EndIf
MsgBox(0, "Test", "Book 1 opened!")

Global $ExcelFile2 = FileOpenDialog('Select Worksheet', 'H:\tools\AutoIt3\ExcelEX', "Excel (*.xls;*.xlsx)", 1 + 2)
Global $oWorkbook2 = _Excel_BookOpen($oExcel, $ExcelFile2, True)
If @error <> 0 Then
    MsgBox(16, "Excel UDF: _Excel_BookOpen", "Error opening '" & $ExcelFile2 & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    _Excel_Close($oExcel)
    Exit
EndIf
MsgBox(0, "Test", "Book 2 opened!")

$oExcel.Visible = True
MsgBox(0, "Test", "Excel set to Visible = True!")

_Excel_Close($oExcel)

[nitekram]

Sorry, just seen this. I will try and get more troubleshooting.

[nitekram]

@water

OK, from what we discussed, I am going to have to try this with the BETA version of AutoIt. But I do want to make sure of something that I just found out today. I am using your code as I showed above and when I open it hiden the excel file it actually makes a copy and puts in my My Documents folder (root) - have not tested it without hiding it. Does this also happen to you with and without using the BETA? Also, the file seems to have been created using less space then the original - which leads me to believe it is not an exact copy. Just more for you to think about. - Thanks again for making it faster.

EDIT - just some further info, the file that I am using is on a server and there fore on a mapped drive - not sure it matters, but wanted you to have more info

Edited January 27, 2013 by nitekram

[water]

As my UDF doesn't create a file copy it has to be Excel default behaviour.

[BrewManNH]

Are you talking about a file with a name that starts with "~" and is hidden? Because if that's the case that happens any time you open an Excel file as long as the file existed previously.

[nitekram]

No this is a file not a temp file. Original file size is 1,196KB the file that is located in my 'My Documents' folder is 815KB. Just something else I noticed, the original file's date modified also changes, each time I gather the information into my array. And the file that is copied to my 'My Documents' folder has a different time stamp - 2 minutes later

When I just open the file from the mapped drive, I do not get anything in the 'My Documents' folder, not even a temp file, so it does not look like a default behavior for excel (2010)

I put some msgbox() stop points in to determine where it happens: _Excel_BookClose($oOExcel, $oWorkbook) -->> after this point is when the file shows up in my docs folder.

I put that in, as I was having issues listed above. I took it out and no longer get a copy of the file.

Edited January 27, 2013 by nitekram

[water]

Please have a look at the docu for function _Excel_BookClose. Default for parameter 3 is True which means that the modified workbook is saved before being closed.

[nitekram]

My fault then. Glad it was something I was doing. That will never happen again - me being wrong...lol

Edit - I do have a question then, should not the original and the copy be the same size, as all I did was open it to create the array, then close it. Would think there would be no changes, therefore the file size would be the same???

Edited January 27, 2013 by nitekram

[water]

Don't know what Excel stores in the copy it creates at open time.

Is the copy deleted after _Excel_BookClose or after _Excel_Close?