Update the Excel UDF that comes with AutoIt

[water]

This UDF is now part of AutoIt since 3.3.12.0.

New versions of Microsoft Office have been released since the last changes were made to the Excel UDF. The new extensions (e.g. xlsx) are not (fully) supported, new functions are missing etc.
 
The rewrite of the Excel UDF delivers changes in the following areas:

• Works with as many instances of Excel as you like - not just one
• Works with any Workbook - not just the active one
• Works with any Worksheet - not just the active one
• Only does what you tell it to do - no implicit "actions"
• Only one function to read from a cell or a range
• Only one function to write a string, an 1D or 2D array to a cell or a range
• Support for every file format Excel supports
• Speed enhancements when transferring data from/to an Excel sheet (20 - 100 times faster)

2014-03-22 - Beta 5
 
Known bugs

• None

SCRIPT BREAKING

• The UDF has been renamed from "Excel Rewrite" to "Excel" - change your #include accordingly

FIXED BUGS

• _Excel_BookOpen ignored $bVisible = True so a hidden workbook could never be shown with this function
• _Excel_BookSaveAs: Default changed from $xlWorkbookNormal to $xlWorkbookDefault to better handle all versions of Excel

GENERAL

• Enhanced documentation, removed documentation bugs
• Preparation to add the UDF to the AutoIt source
• The example files (xls, jpg) where moved to subdirectory "Extras"

The example scripts have been tested with Excel 2010 and AutoIt 3.3.10.2 on Windows 7.
You need to run the scripts with the latest AutoIt production version (3.3.10.x)!
 
Please test with Excel 2003 and Excel 2007 and post changes you need/want to see in the next beta version!
 
Excel Rewrite Beta 5.zip has been removed as it is now part of AutoIt since 3.3.12.0. (627 downloads)

History.txt

Edited June 15, 2014 by water

[BrewManNH]

I posted some code that gets the formula in a cell, rather than what is displayed. Example the cell formula is "=sum(A1:a5)", displayed 10, it will get the "=sum(A1:a5)" instead of the 10.

[Myicq]

I have change a bit here and there in the Excel UDF myself. Have a few requests..

• Additional possibilities in the font parameters. Right now can only change bold, italic, underline. Perhaps something else than a (long) parameter list is better, different functions ? Not sure.. Font .name and .size at least
• Set shading and coloring of cells
• Control of borders on range / cell, both linestyle, width and color
• Control of print area (mark area for printout)
• Run macro on excel side (execute VBA).

Some examples:

With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThin
    End With

With Selection.Font
        .Name = "Calibri"
        .Size = 14
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontMinor
    End With

With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorLight2
        .TintAndShade = 0.399975585192419
        .PatternTintAndShade = 0
    End With

With Selection
   
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With

With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThin
    End With

[Myicq]

I found a resource that may come in handy for this: Excel Enumerations in a single txt format

There is a blog on the same site that's worth a read for ExcelFreaks.

[water]

Thanks a lot for your post!

The Excel UDF will always just contain basic functions. A function in the UDF has to do some processing because otherwise the function could be replaced by simply calling a method or accessing a property.

Therefore there will be no function to run a macro (we removed the macro function from the Word UDF).

You use

$oExcel.Run(macroname, parameter1, ....)

Maybe there wil be a function to work with ranges. The result can then be passed to other functions. Will have to think about.

For the Enumeration part there might be an even simpler solution. Don't have more info at the moment.

I will certainly read the blog, looks quite interesting.

[Spiff59]

I'd posted #2199 in BugTracker a while back, but in last-minute clean-up I'd truncated the SetError() statements down to a single parameter, i.e: SetError(1) instead of SetError(1,0,0). That earned the ticket a thumbs-down. Even though it was suggested I correct and reactivate the ticket, I never did.

Anyway, _ExcelSheetAddNew() causes AutoIt to crash if you specify a workbook name that already exists. When that happens, it also causes Excel to create a new and unwanted generically-named worksheet such as "Sheet1", "Sheet2", etc.

I've been using the following version for a long time successfully. It adds an "@error = 2 " return, and avoids both the crash and the creation of a misnamed worksheet.

;                |@error=2   - Specified sheet already exists

Func _ExcelSheetAddNew($oExcel, $sName = "")
    If Not IsObj($oExcel) Then Return SetError(1, 0, 0)
    If $sName Then
        For $_ws In $oExcel.ActiveWorkbook.Sheets
            If $_ws.Name = $sName Then Return SetError(2, 0, 0)
        Next
    EndIf
    $oExcel.ActiveWorkBook.WorkSheets.Add().Activate()
    If $sName Then $oExcel.ActiveSheet.Name = $sName
    Return 1
EndFunc ;==>_ExcelSheetAddNew

Edited September 11, 2012 by Spiff59

[Spiff59]

I've also had an issue with _ExcelBookOpen() in that if you call it to open a workbook in write-mode ($fReadOnly = False) and the file is already open by another user, _ExcelBookOpen() still returns no error, and returns an object to a read-only copy of the spreadsheet. You have no way of knowing if you can proceed with updates. I've been using the following for a couple years, that inserts a FileOpen/FileClose test, and returns a new @error=3 condition if the file is in use. The test is only performed when $fReadOnly = False. Maybe there is a better way to go about it? Regardless, when you request write access to a spreadsheet, _ExcelBookOpen() should tell you whether or not it has been granted.

Func _ExcelBookOpen2($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 $fReadOnly Then ; first check for open file - prior to launching Excel
        If FileOpen($sFilePath, 1) = -1 Then Return SetError(3, 0, 0)
        FileClose($sFilePath)
    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)
        If (Not $fReadOnly) And .ActiveWorkBook.Readonly Then ; second check for open file - after launching Excel
            $oExcel.Quit
            $oExcel = ""
            Return SetError(3, 0, 0)
        EndIf
;      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

Edit: regarding "Maybe there is a better way to go about it?". The FileOpen()/FileClose() test is much faster than actually launching an instance of Excel, but has the miniscule potential of finding a file closed, and then by the time Excel is launched, someone had opened it and you'll again be working with a read-only object and not know it. Maybe a second test should be added, testing the $fReadOnly flag, that would return the same @error=3 condition (as well as closing the instance of Excel) if the spreadsheet is not open in write-mode? I've yet to encounter that situation, where someone opened a file in the instance between the FileOpen() test and launching Excel, but it is remotely possible.

Edit2: I updated the code with 2 tests to check if the file was returned in a read-only state. The second test would be the more reliable. The only reason to keep the first test would be that the FileOpen()/FileClose() method is much faster than launching Excel and opening the workbook. But, the frequency that one will request a file in write-mode and find it already open is probably low enough that we could just delete the initial FileOpen()/FileClose() test?

Edited September 11, 2012 by Spiff59

[water]

Thank you very much for your suggestions.

I will test them as soon as I'm back from vacation (end of september).

In the the cold and dark season I will have plenty of time to play with the UDF.

[Andy007]

Just wanted to say "Thank you Water and the other dudes" - I am very happy to see this course of action and I greatly appreciate your efforts.

<loud applause>

[water]

Anything special you want to see in the Excel UDF?

[RamadSula]

Water

I just started using Autoit recently and wished I found your UDF before creating much of my own interface to EXCEL.

Project:

I am creating GUI that runs a Document Marquee that roates various Documents , i.e Word, PDF, Excel on a large display.

There is one Excel document that is a maintenance log that is READ Only

Request:

I have an issue in which I can give focus to focus to the READ ONLY Excel Workbook.

Is there a plan in your next UDF ( or does anyone know how ) to be able to Activate ( give focus to) a particular Read Only workbook without getting a "Invalid Index"

I apologize for the indirect addressing , I created an editable array for management to change documents in the Marquee.

Code:

$logtxt = $Title[$Row][$Col] & " Attempt Active"

Call("WritetoLog", $logtxt)

$oExcel = ObjCreate("Excel.Application") ; Create initial Excel Object

$oExcel.WorkBooks($Title[$Row][$Col]).Activate() ; Activate open workbook

I use a Log system to a txt file to trap my errors instead of Popups

09/13/2012 08:52:37 maintenance crossover.xlsx [Read-Only] Attempt Active

09/13/2012 08:52:37 COM Error Hex Number is: 80020009

09/13/2012 08:52:37 COM Error Description is: Invalid index.

Note: The READ-ONLY file will open but I can not switch to it via Activate()

If I don't make maintenance crossover a Read-Only Activate() works just fine.

[water]

Use the Excel UDF that comes with AutoIt. Function _ExcelSheetActivate activates the desired sheet and returns an error if the sheet number is invalid (doesn't crash).

[RamadSula]

Water

Thank you for the response.

I didn't want to take the time to replace all my code so I examined the Excel UDF , extracted what I needed and came up with.

$oExcel.WorkBooks($Title[$Row][$Col]).Sheets($Tab[$Row][$Col]).Select()

I still get Invalid Index Errors on Read Only when attempting to Select a read only worksheet.

09/27/2012 13:57:49 WorkBook maintenance crossover.xlsx [Read-Only] Tab maint log Attempt

09/27/2012 13:57:49 COM Error Hex Number is: 80020009

09/27/2012 13:57:49 COM Error Description is: Invalid index.

Any thoughts on what may be causing it?

A thought has occurred to me while writing this plea.

I made the Document Marquee yo be a scripted based GUI that can an launch a particular document, loop through all documents or configure a GUI button to document.. The launch of document was coded to act as a single loop, i.e. the document opened and was then activated. The act of activating the last open document is redundant but should not cause any errors

The document opens with no error but if I activate a read only EXCEL document I get an error.

[water]

RamadSula,

could you please do me a favour and open a new thread on the subject?

This thread is to discuss functionality of the existing Excel UDF and how to modify it.

Thanks!

[GMK]

I did a UDF for OOo/LibO Calc that includes some error checking on sheets, cells, and converting cell references, which could probably be applied here, if that would be helpful.

[GMK]

Also, if there is a way to make this backwards compatible with as many versions of Excel as possible, that would be helpful. I know it may seem improbable/impossible, but I still run Excel 2000 at home, while running Excel 2010 at work. I try to write AutoIT scripts that will work in both places.

[water]

If your scripts work with Excel 2000 now they should work with the brushed up version of the UDF as well.

Backward compatibility is one of the main goals.

[RamadSula]

Water

I will do that right now .However I think the basic Excel UDF is gonna have the same problem with trying to Activate a read only excel document.

[water]

Hi Spiff59,

I've reopened ticket #2199 and implemented the solution you provided.

[water]

I've also had an issue with _ExcelBookOpen() in that if you call it to open a workbook in write-mode ($fReadOnly = False) and the file is already open by another user, _ExcelBookOpen() still returns no error, and returns an object to a read-only copy of the spreadsheet. You have no way of knowing if you can proceed with updates. I've been using the following for a couple years, that inserts a FileOpen/FileClose test, and returns a new @error=3 condition if the file is in use. The test is only performed when $fReadOnly = False. Maybe there is a better way to go about it? Regardless, when you request write access to a spreadsheet, _ExcelBookOpen() should tell you whether or not it has been granted.

I have been thinking about this problem and I think we should stick with what Excel provides. So no FileOpen/FileClose, just let Excel do the checking.

If the workbook can't be opened read/write should we

• Return "success" and set @extended to 1 to indicate that the workbook was opened read/only. The user then has to decide if he wants to work with the read/only version
• Return "error" and close the read/only version of the workbook (as you suggested in your example code)

I would stick with the first solution because this behaviour is as it was before (backward compatibility). But the user gets additional information what happened.

What do you think?