Yet Another -- ExcelCOM UDF

[sc4ry]

Hey,

i also have a problem by creating a border. replacing the contants did not change anything.

i get following code:

_ExcelCreateBorders($oExcel, $xlThick, 1)
O:\AutoIt\Includes\Excel.au3 (1015) : ==> The requested action with this object has failed.:
.TintAndShade = 0
.TintAndShade = 0^ ERROR

Edited February 17, 2009 by sc4ry

[GreenCan]

_ColorPicker udf

This color pickup tool will allow you to select a color from the standard Excel color palette (same as the Excel color pane but you could extend it to other colors).

I also added an interactive example with Excel.

I think this could be useful if you plan to develop Excel outputs in AutoIt.

GreenCan

Excel color picker tool to be used with ExcelCOM_UDF

or

http://www.autoitscript.com/forum/index.ph...st&p=653170

[gcue]

works great! well done =)

am i mistaken, 1.5 is missing alot of the functions that the previous version "ExcelCOM_UDF.au3 (v1.4)" has...

I've taken on the task of writing a more formal and community-standards based ExcelCOM UDF, with the idea that I'd eventually propose it for inclusion with the extensive UDF set AutoIt installs with now.

This set of routines differs from Randallc's in a few important ways:

1. Instead of opening and closing the document for each call, my routines offer more flexibility. Basically, you call a function to open/create the workbooks, call others to write/read/format the workbook by active sheet, and call yet another to save/close out the workbook and terminate the object.

2. Each function is (or will be) written according to the UDF standards previously established by the community.

3. I will be creating different UDFs for different automation tasks in Excel for brevity's sake - namely, I'll be separating "common" functions from chart-related functions and other more specialized tasks. I'm thinking one UDF for worksheet-related tasks (the "common" set), one for charts, one for perhaps pivot tables, and others for maybe diagrams/drawing, lists, OLE, XML, and other not-as-common tasks. I suppose it depends on the size of finished UDFs, as yet undetermined. Right now I figure I'm about 90% complete on the common UDF, and about 30% complete on the chart UDF.

Suggestions, comments, contributions, and questions welcomed.

CHANGELOG

CODE

---------01/05/08---------- v1.4

--------New functions:

_ExcelAttach() - Attach to an already running instance of Excel (contributed by big_daddy)

_ExcelPrintRange() - Prints a range of cells in the active worksheet

_ExcelPrintSheet() - Prints the specified worksheet

_ExcelBookPropertiesGet() and _ExcelBookPropertiesSet(), get or set some important book properties

_ExcelCreateBorders() is back and better than before

_ExcelReadSheetToArray() - Read the contents of a sheet (in whole or part) into a 2d array (contributed by PsaltyDS)

_ExcelWriteSheetFromArray() - Write the contents of a 2d array into a worksheet (contributed by PsaltyDS)

--------Updated functions:

_ExcelBookOpen() supports read-only, document open/read password, and document write-protect password

_ExcelBookSaveAs() supports document open/read password, document read/write password, document sharing, and sharing conflict resolution

_ExcelFindInRange() fixed and improved RegExp

_ExcelSheetUsedRangeGet() fixed and improved RegExp

--------Bug fixes:

* Typos in _ExcelHyperlinkInsert()

* _ExcelWriteFormula() had some R1C1 reference issues

* When using Opt("MustDeclareVars",1) the _ExcelOpen* commands toss out an undeclared variable error for the $oExcel variable. Fixed (thanks, Blue Drache).

* In the description of _ExcelBookSaveAs, the syntax shows "_ExcelSaveDocAs." Fixed (thanks, ChrisJakarta).

* Some typos corrected and error checking added to _ExcelCellMerge()

---------11/30/06---------- v1.32

Bug fixed for _ExcelSheetUsedRangeGet()

---------11/30/06---------- v1.31

Fixed typos in _ExcelCellFormat()

---------11/24/06---------- v1.3

v1.3

Changed: Added more extensive error handling to _ExcelSheetMove(), and gave it the ability to place a sheet after another, as well as before

Changed: Fixed and cleaned up _ExcelWriteArray()

Changed: Added read-only flag to _ExcelBookOpen()

Fixed: _ExcelHyperlinkInsert(), was very buggy

Added: _ExcelFindInRange(), new function that returns addresses of all instances of a search string in a range as a 2 dimensional array

Added: _ExcelReplaceInRange(), new function that replaces target strings in a range with another string

Added: _ExcelSheetActivate(), to replace _ExcelSheetNumActivate() and _ExcelSheetNameActivate(); added extensive error handling

Added: _ExcelSheetDelete(), to replace _ExcelSheetNumDelete() and _ExcelSheetNameDelete(); added extensive error handling

Added: _ExcelSheetUsedRangeGet(), for finding the used range of a sheet, contributed by DaLiMan, revised by SEO

Added: _ExcelCellFormat(), for formatting a range of cells with parameters like wrapped text, text orientation, text indentation, etc.

Added: _ExcelCellMerge(), for merging (or unmerging) together all cells of a given range

Added: _ExcelReadArray(), for reading a number of cells straight into an array

Added: _ExcelInsert(), insert the clipboard data at a specified cell, shifting the existing contents left or down

Added: _ExcelBookOpenTxt(), for opening and parsing a text file as a worksheet

Removed: The functions that were depricated as of last release - _ExcelWriteCellA1(), _ExcelWriteCellR1C1(), _ExcelWriteFormulaA1(), _ExcelWriteFormulaR1C1(), _ExcelReadCellA1(), _ExcelReadCellR1C1(), _ExcelCopyA1(), _ExcelCopyR1C1(), _ExcelPasteA1(), _ExcelPasteR1C1(), _ExcelNumberFormatA1(), _ExcelNumberFormatR1C1(), _ExcelFontBold(), _ExcelFontItalic(), and _ExcelFontUnderline()

Removed: Headers for _ExcelSheetNumActivate(), _ExcelSheetNameActivate(), _ExcelSheetNumDelete() and _ExcelSheetNameDelete(); these functions will be removed entirely from the next release

Removed: _ExcelSheetUsedRowGet() and _ExcelSheetUsedColGet(), replaced by _ExcelSheetUsedRangeGet()

Things to be incorporated into next release:

Printing & document setup

Workbook & worksheet password/protection/encryption

Bordering will return, with better options

---------10/24/06----------

Update, v1.2

MAJOR UPDATE! Will most likely break existing scripts. Please update scripts accordingly.

Changed: All functions that accepted $sRange will now accept either A1 or R1C1 format. This means that all of these functions will have different parameters, and in most cases the order of the parameters will be different as well. Here's a list of changed functions, with new syntax:

_ExcelWriteCell($oExcel, $sValue, $sRangeOrRow, $iColumn = 1)

_ExcelWriteFormula($oExcel, $sFormula, $sRangeOrRow, $iColumn = 1)

_ExcelReadCell($oExcel, $sRangeOrRow, $iColumn = 1)

_ExcelCopy($oExcel, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1)

_ExcelPaste($oExcel, $sRangeOrRow, $iColumn = 1)

_ExcelNumberFormat($oExcel, $sFormat, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1)

_ExcelHyperlinkInsert($oExcel, $sLinkText, $sAddress, $sRangeOrRow, $iColumn = 1)

_ExcelSort($oExcel, $sKey, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $iDirection = 2)

_ExcelSortExtended($oExcel, $sKey, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $iDirection = 2, $iHeader = 0, $fMatchCase = False, $iOrientation = 2, $iDataOption = 0)

_ExcelCommentAdd($oExcel, $sComment, $sRangeOrRow, $iColumn = 1, $fVisible = 0)

_ExcelCommentDelete($oExcel, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1)

_ExcelCommentShow($oExcel, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $fVisible = False)

_ExcelCommentRead($oExcel, $sRangeOrRow, $iColumn = 1)

_ExcelFontSetProperties($oExcel, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $fBold = False, $fItalic = False, $fUnderline = False)

_ExcelFontSet($oExcel, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $sFontName = "Arial")

_ExcelFontGet($oExcel, $sRangeOrRow, $iColumn = 1)

_ExcelFontSetSize($oExcel, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $iFontSize = 10)

_ExcelFontGetSize($oExcel, $sRangeOrRow, $iColumn = 1)

_ExcelFontSetColor($oExcel, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $iColorIndex = 1, $hColor = 0x000000)

_ExcelFontGetColor($oExcel, $sRangeOrRow, $iColumn = 1)

_ExcelCellColorSet($oExcel, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $iColorIndex = 1, $hColor = 0x000000)

_ExcelCellColorGet($oExcel, $sRangeOrRow, $iColumn = 1)

_ExcelHorizontalAlignSet($oExcel, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $sHorizAlign = "left")

_ExcelVerticalAlignSet($oExcel, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $sVertAlign = "bottom")

Changed: Several headers that had typos or were otherwise incorrect.

Changed: _ExcelColWidthSet($oExcel, $vColumn, $vWidth), _ExcelColWidthGet($oExcel, $vColumn), _ExcelRowHeightSet($oExcel, $vRow, $vHeight), _ExcelRowHeightGet($oExcel, $vRow) all now accept numbers as well as letters for column or row (A1 and R1C1 format)

Removed: All headers from functions that specify A1 or R1C1 addressing in their function names. The associated functions remain, but will be removed at next release. Here's the list of functions to be removed:

_ExcelWriteCellA1()

_ExcelWriteCellR1C1()

_ExcelWriteFormulaA1()

_ExcelWriteFormulaR1C1()

_ExcelReadCellA1()

_ExcelReadCellR1C1()

_ExcelCopyA1()

_ExcelCopyR1C1()

_ExcelPasteA1()

_ExcelPasteR1C1()

_ExcelNumberFormatA1()

_ExcelNumberFormatR1C1()

Removed: Headers for _ExcelFontBold(), _ExcelFontItalic(), and _ExcelFontUnderline(). These functions will be removed entirely from the next release.

Moved: _ExcelSortExtended() to working functions list, with thanks to DaLiMan

_ExcelSortExtended($oExcel, $sKey, $sRangeOrRowStart, $iColStart = 1, $iRowEnd = 1, $iColEnd = 1, $iDirection = 2, $iHeader = 0, $fMatchCase = False, $iOrientation = 2, $iDataOption = 0)

Added: _ExcelSheetUsedRowGet(), contributed by Stanley Lim, minor changes by SEO

Added: _ExcelSheetUsedColGet(), contributed by DaLiMan, based on _ExcelSheetUsedRowGet() by Stanely Lim, minor changes by SEO

Added: _ExcelFontSetProperties(), which replaces _ExcelFontBold(), _ExcelFontItalic(), and _ExcelFontUnderline()

---------10/13/06----------

Update, v1.11

Changed: _ExcelBookNew() has been drastically changed - please read its header!

Changed: _ExcelBookSaveAs() has had some important notes added to its header

Changed: Updated many comment headers

Fixed: Typos and bugs in _ExcelReadCellR1C1()

Added: _ExcelHyperlinkInsert() to working functions list

Added: Some functions to be written, _ExcelBookOpenTxt(), _ExcelCellFormat(), _ExcelCellMerge(), _ExcelPrintSheet(), _ExcelPrintRange(), _ExcelPrintSetup()

Added: Simple example file, ExcelCOM_UDF_Example01.au3

---------10/11/06----------

Update, v1.1

Updates: _ExcelPictureAdd(), _ExcelPictureAdjust(), _ExcelPictureScale(), _ExcelSheetNumActivate(), _ExcelSheetNameActivate(), _ExcelSheetNumDelete(), _ExcelSheetNameDelete(),

_ExcelCellColorSet(), _ExcelFontColorGet() all updated, tested, and added to working functions list.

Comments (help) updated for most functions.

Changed names of the following functions to match big_daddy's suggestion: _ExcelColumnInsert(), _ExcelColumnDelete(), _ExcelRowInsert(), _ExcelRowDelete(), ExcelHyperlinkInsert(), _ExcelFontSet(), _ExcelFontGet(), _ExcelFontSetSize(), _ExcelFontGetSize(), _ExcelFontSetColor(), ExcelFontGetColor(), _ExcelCellColorSet(), _ExcelCellColorGet(), ExcelHorizontalAlignSet(), _ExcelVerticalAlignSet(), _ExcelColWidthSet(), _ExcelColWidthGet(), _ExcelRowHeightSet(), _ExcelRowHeightGet(), _ExcelSheetMove(), _ExcelSheetAddNew(), _ExcelSheetNameGet(), _ExcelSheetNameSet(), _ExcelSheetNumActivate(), _ExcelSheetNameActivate(), _ExcelSheetNumDelete(), _ExcelSheetNameDelete(), _ExcelSheetList().

---------10/11/06----------

Update, v1.01

Updates: Changed function names for _Excel document add/save/new functions, removed _ExcelInit(), gave _ExcelBookOpen() and _ExcelBookNew() the ability to return object handle.

Additions: None

---------10/10/06----------

Initial release, v1.0

Updates: None

Additions: None

Latest Version - v1.5 (07/18/2008)

Excel.zip

Note: This thread is being maintained by litlmike while LocoDarwin is away

---

[blitzkrg]

What happend to all of the other functions????

Edited April 17, 2009 by blitzkrg

[GioVit]

'm starting to use some function of the excel user defined functions, created by Locodarwin, and I notice that in the code there are many string using the format "R1C1" and also the code assume that the addres of a cell are returned with with the format "R" for "Row" and "C" for Column, and this is not true for non english excel.

Specifically I'm using excel 2007 in spanish and the address is returned as "F1C1" where "F" and "C" represent Row and Column respectively.

So I sugest the following modifications:

1.- Test the letter used to reference an address by regional or local excel every time an excel file is opened or created, as show below

$HomeCell = $oExcel.WorkSheets(1).Cells(1,1)
$HomeCellRef = $HomeCell.AddressLocal(True, True, $xlR1C1)
$LocalRowRef = StringLeft($HomeCellRef,1)
$LocalColRef = StringMid($HomeCellRef,3,1)

2.- Change all the reference of type "R1C1" to LocalRowRef & "1" & LocalColRef & "1"

3.- Change all the "R" and "C" to LocalRowRef and LocalColRef respectively

Sorry for my English

[JamesPatageule]

Hi all,

There is a very useful library here, congratulations !

But there is a thing i haven't see ... Is it possible to modify the "request" in a pivotable file ?

i don't know how i can do that ... Otherwise is it possible to integrate this possibility in the library ?

Thanks by advance guys !

[willichan]

Can someone address the inability of saving as DBF4 with Office 2007? Thanks.

A year late getting this for you, but ...

Several formats are blocked by default in Office 2003 and 2007. You can unlock these file formats in the registry.

Run the following code:

RegWrite("HKEY_LOCAL_MACHINE\Software\Microsoft\Shared Tools\Graphics Filters\Import\CDR","Enabled","REG_DWORD",00000001)
RegWrite("HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\Word\Security\FileOpenBlock","FilesBeforeVersion","REG_DWORD",00000000)
RegWrite("HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\Excel\Security\FileOpenBlock","LotusandQuattroFiles","REG_DWORD",00000000)
RegWrite("HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\Excel\Security\FileOpenBlock","LegacyBinaryFiles","REG_DWORD",00000000)
RegWrite("HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\Excel\Security\FileOpenBlock","LegacyDatabaseAndDatasourceFiles","REG_DWORD",00000000)
RegWrite("HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\Excel\Security\FileSaveBlock","LotusandQuattroFiles","REG_DWORD",00000000)
RegWrite("HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\Excel\Security\FileSaveBlock","LegacyBinaryFiles","REG_DWORD",00000000)
RegWrite("HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\Excel\Security\FileSaveBlock","LegacyDatabaseAndDatasourceFiles","REG_DWORD",00000000)
RegWrite("HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\PowerPoint\Security\FileOpenBlock","FilesBeforePowerPoint97","REG_DWORD",00000000)
RegWrite("HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\PowerPoint\Security\FileSaveBlock","FilesBeforePowerPoint97","REG_DWORD",00000000)

[reb]

Hi,

I downloaded the latest ExcelCOM UDF and it does not have _ExcelCellMerge(

am I missing something?

REB

[Leagnus]

If i'm looking something with Ctrl+F dialog after _ExcelFindInRange have worked –

dropdown list "find: " is "in sheet" position.

How to switch it to "in workbook" position?

[Alodar]

In using ExcelBookAttach...

I've found that often, I don't care what's open, I'd just rather attach to the current instance of excel rather than creating a new one, and trying to make sure things open to that instance (especially in opening things from the web). Would it be much to ask to add the fact that a null string (i.e. "") attaches to the first instance of excel it finds?

simple few lines at the top of the _ExcelBookAttach() function would add this functionality.

If $s_string = "" Then
        $o_Result = ObjGet("","Excel.Application")
        If Not @error And IsObj($o_Result) Then
            Return $o_Result
        EndIf
    EndIf

[PsaltyDS]

In using ExcelBookAttach...

I've found that often, I don't care what's open, I'd just rather attach to the current instance of excel rather than creating a new one, and trying to make sure things open to that instance (especially in opening things from the web). Would it be much to ask to add the fact that a null string (i.e. "") attaches to the first instance of excel it finds?

simple few lines at the top of the _ExcelBookAttach() function would add this functionality.

If $s_string = "" Then
        $o_Result = ObjGet("","Excel.Application")
        If Not @error And IsObj($o_Result) Then
            Return $o_Result
        EndIf
    EndIf

It's a good idea, but won't work that way. _ExcelBookAttach() doesn't return the Excel.Application object, it returns a found .Workbook object matched from the .Workbooks collection. So the implementation in Excel.au3 (as included with AutoIt's distribution) would be like this change (can't test right now):

For $o_workbook In $o_workbooks
        Switch $s_mode
            Case "filename"
                If $o_workbook.Name = $s_string Then
                    Return $o_workbook
                EndIf
            Case "filepath" ; The default mode
                If ($s_string = "") Or ($o_workbook.FullName = $s_string) Then ; Return first found if $s_string = ""
                    Return $o_workbook
                EndIf
            Case "title"
                If ($o_workbook.Application.Caption) = $s_string Then
                    Return $o_workbook
                EndIf
            Case Else
                ConsoleWrite("--> Error from function _ExcelAttach, Invalid Mode Specified" & @CR)
                Return SetError(1, 0, 0)
        EndSwitch
    Next

If something like that tests correctly for you, post it to Trac as a new feature request.

[Alodar]

It's a good idea, but won't work that way. _ExcelBookAttach() doesn't return the Excel.Application object, it returns a found .Workbook object matched from the .Workbooks collection. ...

I see your point. However, this would throw an error assuming no workbooks were open I believe. Haven't tested it yet, as I'm busy with other projects, but if you simply had the application open, but all 'workbooks' closed, there would be no object to return.

I guess I should be looking for a new function such as _ExcelAttach()to attach to the application level, rather than the .Workbook object? Is that something that would go into Trac?

[PsaltyDS]

I see your point. However, this would throw an error assuming no workbooks were open I believe. Haven't tested it yet, as I'm busy with other projects, but if you simply had the application open, but all 'workbooks' closed, there would be no object to return.

I guess I should be looking for a new function such as _ExcelAttach()to attach to the application level, rather than the .Workbook object? Is that something that would go into Trac?

Empty collections don't throw errors (usually, again not tested in this case), the For/Next loop just finishes without running any iterations. Handling that just requires a return case for AFTER the loop, which is already there in the function:

For $o_workbook In $o_workbooks
        ; ...should Return from inside loop on matched workbook
    Next
    ConsoleWrite("--> Warning from function _ExcelAttach, No Match" & @CR)
    Return SetError(1, 0, 0)

[Syl21]

Hello,

You have a bug in function _ExcelCommentRead

If Cell have no comments that Crash...

Can you help me to correct-it???

[PsaltyDS]

Hello,

You have a bug in function _ExcelCommentRead

If Cell have no comments that Crash...

Can you help me to correct-it???

What, exactly, do you mean by "Crash"? What is the exact error?

Check that your range is valid. No comment text should not throw an error, but it might if you try to get .Comment off an invalid range.

[StevenOfPomroyIL]

This is probably not the place (or time) to ask this. Please don't hate the noob. I've been looking high and low for an example AutoIt script that calls an Excel Macro Function from within the AutoIt script. For the sake of concreteness, the function I will need to call each morning is called "SaveWorld(Date)." SaveWorld is a function that I have saved in an Excel workbook (called GodlyTasks.xls) that sits on my desktop. SaveWorld takes a special date from within the AutoIt script, but otherwise does not pass values back to AutoIt. It simply sets off the SaveWorld function to run and exits. Thank you, in advance, for your help.

-Stephen "the Noob"

Edited April 12, 2010 by StevenOfPomroyIL

[PsaltyDS]

Some forum searching should have turned it up. Posted many times in various topics: $oExcel.Run("MacroName")

[StevenOfPomroyIL]

Some forum searching should have turned it up. Posted many times in various topics: $oExcel.Run("MacroName")

Awesome. That simple.

[Spiff59]

_ExcelBookOpen() needs some work.

If another user already has a spreadsheet open, _ExcelBookOpen() has no mechanism of notifying the script.

If you've set $fVisible to True, one can assume you're expecting user-intervention, and they can respond to the "read-only" notification from Excel as they see fit. If you're processing multiple spreadsheets in a batch environment ($fVisible = False) and intending to modify the spreadsheets ($fReadonly = False), then the first indication you get of a problem is when your script blows up.

I think an additional @error code of 3 would be useful, indicating "File in Use", a change like the following would work:

Func _ExcelBookOpen($sFilePath, $fVisible = 1, $fReadOnly = False, $sPassword = "", $sWritePassword = "")
    If Not FileExists($sFilePath) Then Return SetError(2, 0, 0)
    Local $oExcel = ObjCreate("Excel.Application")
    If Not IsObj($oExcel) Then Return SetError(1, 0, 0)
    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 $oExcel
        .Visible = $fVisible
        If $sPassword <> "" And $sWritePassword <> ""  Then .WorkBooks.Open($sFilePath, Default, $fReadOnly, Default, $sPassword, $sWritePassword)
        If $sPassword = ""  And $sWritePassword <> ""  Then .WorkBooks.Open($sFilePath, Default, $fReadOnly, Default, Default, $sWritePassword)
        If $sPassword <> "" And $sWritePassword = ""   Then .WorkBooks.Open($sFilePath, Default, $fReadOnly, Default, $sPassword, Default)
        If $sPassword = ""  And $sWritePassword = ""   Then .WorkBooks.Open($sFilePath, Default, $fReadOnly)


        ; 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
        If $fVisible = 0 And $fReadOnly = 0 And .ActiveWorkbook.ReadOnly Then
            $oExcel.Close
            Return SetError(3, 0, 0)
        EndIf
    EndWith
    Return $oExcel
EndFunc   ;==>_ExcelBookOpen

It tests for the read-only state only when you're attempting to open the book in write mode, and only when you've set the call to invisible operation. I did also move the test for '@error = 2' up a couple lines, as I don't see the point in starting the Excel application, and then aborting the function with a file-not-found condition, leaving excel.exe running.

Edit: The above solution needs work too. If you attempt to open an already-open file, it returns @error=3 just fine, but the next time you open an excel file you'lll get a notification that the previous file is now available for editing. That needs to be suppressed...

Edit2: Since DisplayAlerts and the "notify" parameter of .Open don't stop the popups, this is the best I can come up with:

If you try to open a workbook in unattended-write-mode ($fVisible and $fReadonly both False), and the file is already in use, then it returns an @error = 3 condition, and does not turn on the Excel "file now available" mechanism. I put the FileExists() test where it ought to be, cleaned up the parameter edits, and eliminated what appeared to be a ton of unnecessary tests and superfluous .Workbooks.Open statements. I also changed the docs to reflect the .visible property as a boolean, as that is what MSDN seems to say. It's working well for me, so far.

expandcollapse popup

; #FUNCTION# ====================================================================================================================
; Name...........: _ExcelBookOpen
; Description ...: Opens an existing workbook and returns its object identifier.
; Syntax.........: _ExcelBookOpen($sFilePath[, $fVisible = True[, $fReadOnly = False[, $sPassword = ""[, $sWritePassword = ""]]]])
; Parameters ....: $sFilePath - Path and filename of the file to be opened
;                  $fVisible - Flag, whether to show or hide the workbook (default = True)
;                  $fReadOnly - Flag, whether to open the workbook as read-only (default = False)
;                  $sPassword - The password that was used to read-protect the workbook, if any
;                  $sWritePassword - The password that was used to write-protect the workbook, if any
; Return values .: Success      - Returns new object identifier
;                  Failure      - Returns 0 and sets @error on errors:
;                  |@error=1     - Unable to create the object
;                  |@error=2     - File does not exist
;                  |@error=3     - File already in use (returned only when $fVisible and $fReadOnly are False)
; Author ........: SEO <locodarwin at yahoo dot com>
; Modified.......: litlmike, spiff59
; Remarks .......: None
; Related .......: _ExcelBookAttach
; Link ..........:
; Example .......: Yes
; ===============================================================================================================================
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 = $xlSheetVisible Then
                .ActiveWorkbook.Sheets($i).Select()
                ExitLoop
            EndIf
        Next
    EndWith
    Return $oExcel
EndFunc   ;==>_ExcelBookOpen

Edited June 9, 2010 by Spiff59

[Spiff59]

I threw the following function together in response to a thread in the General forum:

expandcollapse popup

#include <Array.au3>
$Workbook = @ScriptDir & "\test.xls"

;===============================================================================
; example calling function when workbook is not open, passing workbook as a pathname/filename
$aResult = _ExcelFindCells($Workbook, "12345")
_ArrayDisplay($aResult, "pathname/filename")

;===============================================================================
; example calling function (repeatedly) when workbook is already open, passing workbook as an object
$oExcel = ObjCreate("Excel.Application")
$oExcel.Visible = 0
$oExcel.WorkBooks.Open($Workbook)

$aResult = _ExcelFindCells($oExcel, "ab") ; partial match
_ArrayDisplay($aResult, "object")
$aResult = _ExcelFindCells($oExcel, "ABC", "*", True, True) ; full match, case sensitive
_ArrayDisplay($aResult, "object")

$oExcel.Quit
$oExcel = ""
Exit


; #FUNCTION# ====================================================================================================================
; Name...........: _ExcelFindCells
; Description ...: Returns an array of workbook cell addresses whose content matches the search string.
; Syntax.........: _ExcelFindCells($sFilePath, $sSearch [, $sRange] [, $sFull] [, $sCase])
; Parameters ....: $sFilePath - Object to an open workbook, or, full path/filename of an unopened workbook
;                  $sSearch   - Search string
;                  $sRange    - Range of cells to search (Default = "*". Format = "A1:D99")
;                  $sFull     - False = allow partial match (default), True = entire cell contents must match exactly
;                  $sCase     - False = case insensitive (default), True = case sensitive
; Return values .: Success    - Returns a 1-based array containing cell addresses that match the search string
;                  @error=1   - Unable to create the Excel object
;                  @error=2   - File does not exist
; Author ........: Spiff59
; ===============================================================================================================================
Func _ExcelFindCells($sFilePath, $sSearch, $sRange = "*", $sFull = False, $sCase = False)
    If IsObj($sFilePath) Then
        Local $oExcel = $sFilePath
    Else
        If Not FileExists($sFilePath) Then Return SetError(2, 0, 0)
        Local $oExcel = ObjCreate("Excel.Application")
        If Not IsObj($oExcel) Then Return SetError(1, 0, 0)
        $oExcel.Visible = 0
        $oExcel.WorkBooks.Open($sFilePath)
    EndIf
    Local $sReturn
    If $sFull <> True Then  $sFull = False
    If $sCase <> True Then  $sCase = False
    If $sRange = "*" Then
;       $oRange = $oExcel.ActiveSheet.UsedRange
        $oRange = $oExcel.Cells
    Else
        $oRange = $oExcel.Range($sRange)
    EndIf
    $oMatch = $oRange.Find($sSearch, Default, Default, $sFull, Default, Default, $sCase)
    If Isobj($oMatch) Then
        $oFirst = $oMatch.Address
        While IsObj($oMatch)
            $sReturn &= StringReplace($oMatch.Address, "$", "") & "|"
            $oMatch = $oRange.Findnext($oMatch)
            If $oMatch.Address = $oFirst then ExitLoop
        WEnd
        $oFirst = ""
    EndIf
    $oMatch = ""
    $oRange = ""
    If Not IsObj($sFilePath) Then $oExcel.Quit
    $oExcel = ""
    If $sReturn Then Return StringSplit(StringTrimRight($sReturn, 1), "|")
    Local $sReturn[1] = [0]
    Return $sReturn
    EndFunc

_ExcelFindCells() contains functionality that I think would benefit a couple of the existing functions within the Excel UDF.

That being the ability to be passed either:

1. A full path/filename of a 'not-open' workbook, in which case it will start excel, open the workbook, perfom the function, and then cleanup after it self (close excel).

2. An object to an already open workbook (or an existing excel application object). In this case it simply performs it's function and returns the result. No creating a new instance of excel.exe.

Many of the existing UDF functions are unusable in a batch environment. If you're performing some operation on say 10,000 speadsheets it becomes a mess. 99% of the execution time is spent starting and stopping excel processes. I should probably review the functions in the UDF, but if I recall from a year ago I was unable to use them to process over 80,000 .xls files unless I wanted to let the script run for 2 days. It seems like neither BookOpen nor BookAttach were well-tailored to be run against thousands of workbooks.

Edited July 1, 2010 by Spiff59