Update the Excel UDF that comes with AutoIt

[BrewManNH]

The functions will be changed this way: Check if an instance of Excel is already running. If true, don't start a new instance.

$oExcel = ObjGet("", "Excel.Application")
if @error Then $oExcel = ObjCreate("Excel.Application")

This saves ressources (processing time, memory) and allows to access all workbooks at once.

As long as you're checking for any errors after the ObjCreate, that might be a good way of going about it.

@Spiff59

I misunderstood where you were going with that, using ExcelBookOpen to use the object previously opened and not the file previously opened. Mea culpa.

[water]

As long as you're checking for any errors after the ObjCreate, that might be a good way of going about it.

It's just a stripped down example to demonstrate what I have in mind. Error checking has been greatly enhanced. A function of this UDF should crash never again but return a meaningful error code to the calling script in case of an error.

[Spiff59]

I'm using this at work:

Func _ExcelBookOpen($sFilePath, $fVisible = True, $fReadOnly = False, $sPassword = "", $sWritePassword = "")
If Not FileExists($sFilePath) Then Return SetError(2, 0, 0)
$fVisible = ($fVisible > 0)
$fReadOnly = ($fReadOnly > 0)
If Not ($fVisible + $fReadOnly) Then
  $i = FileOpen($sFilePath, 1)
  If $i = -1 Then Return SetError(3, 0, 0)
  FileClose($i)
EndIf
Local $oExcel = ObjCreate("Excel.Application")
If Not IsObj($oExcel) Then Return SetError(1, 0, 0)
With $oExcel
  .Visible = $fVisible
  .WorkBooks.Open($sFilePath, Default, $fReadOnly, Default, $sPassword, $sWritePassword)
  ; Select the first *visible* worksheet.
  For $i = 1 To .ActiveWorkbook.Sheets.Count
   If .ActiveWorkbook.Sheets($i).Visible =  -1 Then ; $xlSheetVisible
    .ActiveWorkbook.Sheets($i).Select()
    ExitLoop
   EndIf
  Next
EndWith
Return $oExcel
EndFunc   ;==>_ExcelBookOpen

Which appears to be the same as the one I linked to a few posts back. I'm not using the UDF in scripts that process large numbers of spreadsheets (because of the constant starting and stopping of the excel process). The example above does have some worthwhile improvements. Why launch Excel and then error out for a "file not found" a couple lines later? The FileExists() test should be the first line. The edits of fVisible and fReadOnly can be done in a single line. The 4 in-line compound If statements that tack 8 compares onto every call are unnecessary as sending "" for either password is the same as sending "Default".

The fileopen/fileclose test (@error = 3) I added, to see if a file is already open when being requested in write-mode, probably should be changed to use the .Readonly method like that other function that had the same issue.

[water]

I hope to soon release another beta including this changes to _ExcelBookOpen().

Depends on the weather - I need a few rainy days

[guinness]

I hope to soon release another beta including this changes to _ExcelBookOpen().

Depends on the weather - I need a few rainy days

Well you are water so I think that shouldn't be a problem. The UDF is coming on nicely, no complaints thus far.

Edit: Well there is one, I fixed an issue in which Au3Check was returning an 'unused variable' but inserting #foreceref fixed this. Any ideas why this could be? As it's clearly being used.

Edited October 8, 2012 by guinness

[water]

Another idea to discuss:

To make the UDF work faster (faster processing of many workbooks etc.) a few design decisions of the current UDF need to be changed. This means "script breaking" changes!

I can imagine a "compatibility flag". If set to True the UDF will behave as it does now, else the new design will be activated.

Example:

If you use _ExcelBookOpen to open a workbook that another user already has write access and flag $fReadOnly = False then the current UDF returns a read-only copy of the workbook.

If the compatibility flag is set to False then an error will be returned.

With such a compatibility mode the users could easily test their scripts and change them to the new design.

One or two AutoIt releases later the compatibility mode could be removed.

WHAT DO YOU THINK?

Edited October 12, 2012 by water

[water]

Here is an example for function _ExcelBookOpen. If compatibility mode is set to False then the function

• tries to connect to an existing Excel instance (in compatibility mode always a new instance is created)
• Returns an error if you asked for write access but the workbook is in use by another user/task (in compatibility mode you get a readonly copy of the workbook)
• The function returns the object of the workbook ((in compatibility mode the object of the Excel application is returned)
• The difference between compatibility mode on/off is documented in the remarks section

expandcollapse popup

; #FUNCTION# ====================================================================================================================
; Name...........: _ExcelBookOpen
; Description ...: Opens an existing workbook and returns its object identifier.
; Syntax.........: _ExcelBookOpen($sFilePath[, $fVisible = 1[, $fReadOnly = False[, $sPassword = ""[, $sWritePassword = ""]]]])
; Parameters ....: $sFilePath     - Path and filename of the file to be opened
;                 $fVisible    - Flag, whether to show or hide the workbook (0=not visible, 1=visible) (default=1)
;                 $fReadOnly      - Flag, whether to open the workbook as read-only (True or False) (default=False)
;                 $sPassword      - The password that was used to read-protect the workbook, if any (default is none)
;                 $sWritePassword - The password that was used to write-protect the workbook, if any (default is none)
; Return values .: Success - Returns new object identifier
;                 Failure - Returns 0 and sets @error:
;                 |1 - Unable to create the Excel COM object. @extended is set to the error code returned by the ObjCreate method
;                 |2 - File does not exist
;                 |3 - Unable to open $sFilePath. @extended is set to the error code returned by the Open method
;                 |4 - Readwrite access could not be granted. Workbook might be open by another users/task.
; Author ........: SEO <locodarwin at yahoo dot com>
; Modified.......: litlmike, water
; Remarks .......: If $__bExcelCompatibility = True:
;                   Tries to connect to an existing Excel instance. If none exists a new one is created
;                   Returns the object of the Excel workbook
;                   If a workbook was opened readonly but you asked for readwrite access then an error is returned
; Related .......: _ExcelBookAttach
; Link ..........:
; Example .......: Yes
; ===============================================================================================================================
Func _ExcelBookOpen($sFilePath, $fVisible = 1, $fReadOnly = False, $sPassword = "", $sWritePassword = "")
    Local $oAppl, $oWorkBook
    If Not FileExists($sFilePath) Then Return SetError(2, 0, 0)
    If $__bExcelCompatibility Then
        $oAppl = ObjCreate("Excel.Application")
        If Not IsObj($oAppl) Then Return SetError(1, 0, 0)
    Else
        $oAppl = ObjGet("", "Excel.Application")
        If @error Then
            $oAppl = ObjCreate("Excel.Application")
            If @error Then Return SetError(1, @error, 0)
        EndIf
    EndIf
    If $fVisible > 1 Then $fVisible = 1
    If $fVisible < 0 Then $fVisible = 0
    If $fReadOnly > 1 Then $fReadOnly = 1
    If $fReadOnly < 0 Then $fReadOnly = 0
    With $oAppl
        .Visible = $fVisible
        If $sPassword <> "" And $sWritePassword <> "" Then $oWorkBook = .WorkBooks.Open($sFilePath, Default, $fReadOnly, Default, $sPassword, $sWritePassword)
        If $sPassword = "" And $sWritePassword <> "" Then $oWorkBook = .WorkBooks.Open($sFilePath, Default, $fReadOnly, Default, Default, $sWritePassword)
        If $sPassword <> "" And $sWritePassword = "" Then $oWorkBook = .WorkBooks.Open($sFilePath, Default, $fReadOnly, Default, $sPassword, Default)
        If $sPassword = "" And $sWritePassword = "" Then $oWorkBook = .WorkBooks.Open($sFilePath, Default, $fReadOnly)
        If @error Then Return SetError(3, @error, 0)
        ; If a readwrite workbook was opened readonly then return an error
        If $__bExcelCompatibility And $fReadOnly = False And .ActiveWorkBook.Readonly Then
            $oWorkBook.Close(False)
            $oWorkBook = 0
            Return SetError(4, 0, 0)
        EndIf
        ; Select the first *visible* worksheet.
        For $i = 1 To .ActiveWorkbook.Sheets.Count
            If .ActiveWorkbook.Sheets($i).Visible = $xlSheetVisible Then
                .ActiveWorkbook.Sheets($i).Select()
                ExitLoop
            EndIf
        Next
    EndWith
    If $__bExcelCompatibility Then
        Return $oAppl
    Else
        Return $oWorkBook
    EndIf
EndFunc   ;==>_ExcelBookOpen

[Spiff59]

This

$oWorkBook = .WorkBooks.Open($sFilePath, Default, $fReadOnly, Default, $sPassword, $sWritePassword)

really would replace all this

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

without causing any issues.

Edit:

And these are functionally equivalent:

$FVisible = ($fVisible > 0)

If $fVisible > 1 Then $fVisible = 1
If $fVisible < 0 Then $fVisible = 0

Edited October 12, 2012 by Spiff59

[water]

What do you think of the compatibility flag?

Many desing changes are needed to remove the limitations of the current UDF. Either a complete rewrite with a lot of script breaking changes, a complete new UDF (ExcelEX) that co-exists with the current Excel UDF or a combined UDF with the compatibility flag to select the behavior of the UDF.

What woud you prefer?

[THECARDNEXUS]

So, was trying to use this UDF, because I use ready to array, and readsheettoarray a LOT. As well as write to array. Was hoping for dramatic speed improvements(which I thought were listed). I copied the excel.au3 from the 1st post over the one in my include folders... yet I am experiencing no change in speeds? I assume I am doing something wrong, thoughts on what it is?

[water]

The Alpha 1 version you can download from page 1 only contains an enhanced _ExcelReadSheetToArray version. Only this function shows the speed improvement.

[THECARDNEXUS]

The Alpha 1 version you can download from page 1 only contains an enhanced _ExcelReadSheetToArray version. Only this function shows the speed improvement.

Does the speed improvement only show itself on Excel 2010+. I have a VM runnign excel 2003, a gig of ram, XP and it showed no speed increase(my laptop on the other hand was blazingly fast).

Also is there a way to get the alpha 2 already?

Either way, thanks for your work, its definitely a improvement so far.

[water]

The speed enhancement can be seen on all Excel versions. The "old" UDF has written the array cell by cell whereas the "new" uses a range.

At the moment I'm running a to decide how important compatibility is for the users of the UDF. Did you vote already?

At the moment it looks like I'm going to move to a complete rewrite of the UDF. This means better design but script breaking changes for the users.

[THECARDNEXUS]

Fair enough. If you are willing to, I am very interested in the Alpha 2 for my current scripting purposes, can give you feedback on it etc.

The only other obvious question I have is, why sheet to array is so fast, but readtoarray is not. I assume its a similar improvement/problem. I use readtoarray more than sheetreadtoarray.

[water]

As soon as I have something to show I will post it for you to play with. Remember that the Alpha 1 I posted was an enhancement of the current UDF, the next Alpha will be a rewrite of the UDF causing some script breaking changes.

Stay tuned ...

[Spiff59]

Just curious, are you considering defaulting to $fReadOnly=True for the pertinent functions?

[water]

Where it's sensible, yes. Which functions doy ou have in mind?

[water]

I have added the Alpha 2 to the first post for download. It's a complete rewrite of the UDF so do not expect it to work with your existing scripts.

You need to run the example scripts with Beta 3.3.9.2 or later.

Please tell me what you think!

[water]

Alpha 2 of the new Excel UDF released on the first post. Be aware that Alpha 2 is a complete rewrite of the UDF. That means the UDF is not compatible with the UDF that is distributed with AutoIt now nor with the Alpha 1.

Just check the example script to see how it works.

Any feedback is welcome!

[water]

I am about to add functions to import text files into Excel.

Importing CSV (comma separated values) files is easy.

Importing fixed width files is more complex.

Does anyone need a function to import fixed width files?