Update the Excel UDF that comes with AutoIt

[nitekram]

The copy is never deleted. I had to delete the files my self.

I had used the _Excel_BookClose to rid my self of getting a minimized excel file that I opened while script was running (after it created the array - I used _Excel_Close) - the problem was that the newest excel file was opening, but not opening in full screen, just minimized - not a big deal, but not what you would expect.

Edit my fault again, and I thought I would never be wrong again - this is what I posted, as my issue that I was having, not it being minimized

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.

Edited January 27, 2013 by nitekram

[water]

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.

In post #92 I posted a reproducer script which works as expected - no worksheet shows up until visibility for Excel is set to True.

Can you test this please and post the result?

[nitekram]

P:AutoitfilesExcel Rewrite.au3(289,56) : ERROR: ObjGet() [built-in] called with wrong number of args.

$oWorkbook = ObjGet("", $sCLSID_Workbook, $iCount + 1)

Your excel rewrite.au3 that I am currently using, have not uploaded your newer one

expandcollapse popup

#Tidy_Parameters= /gd 1 /gds 1 /nsdp
#AutoIt3Wrapper_AU3Check_Parameters= -d -w 1 -w 2 -w 3 -w 4 -w 5 -w 6
#AutoIt3Wrapper_AU3Check_Stop_OnWarning=Y
#include-once

; #INDEX# =======================================================================================================================
; Title .........: Microsoft Excel COM UDF library for AutoIt v3
; Language ......: English
; Description ...: A collection of functions for accessing and manipulating Microsoft Excel files
; Author(s) .....: SEO (Locodarwin), DaLiMan, Stanley Lim, MikeOsdx, MRDev, big_daddy, PsaltyDS, litlmike, water, spiff59, golfinhu, bowmore
; Remarks .......:
; ===============================================================================================================================

; #VARIABLES# ===================================================================================================================
Global $__iExcel_Debug = 0 ; Debug level. 0 = no debug information, 1 = Debug info to console, 2 = Debug info to MsgBox, 3 = Debug Info to File
Global $__sExcel_DebugFile = @ScriptDir & "\Excel_Debug.txt" ; Debug file if $__iExcel_Debug is set to 3
Global $__oExcel_Error ; COM Error handler
; ===============================================================================================================================

; #CONSTANTS# ===================================================================================================================
; Constants Enumeration. This enumeration groups together constants used with various Excel methods.
; See: http://msdn.microsoft.com/en-us/library/ff197824.aspx (Excel 2010)
Global Const $xlCenter = -4108 ; Center
Global Const $xlLeft = -4131 ; Left
Global Const $xlRight = -4152 ; Right

; XlColumnDataType Enumeration. Specifies how a column is to be parsed.
; See: http://msdn.microsoft.com/en-us/library/ff193030.aspx (Excel 2010)
Global Const $xlDMYFormat = 4 ; DMY date format
Global Const $xlDYMFormat = 7 ; DYM date format
Global Const $xlEMDFormat = 10 ; EMD date format
Global Const $xlGeneralFormat = 1 ; General
Global Const $xlMDYFormat = 3 ; MDY date format
Global Const $xlMYDFormat = 6 ; MYD date format
Global Const $xlSkipColumn = 9 ; Column is not parsed
Global Const $xlTextFormat = 2 ; Text
Global Const $xlYDMFormat = 8 ; YDM date format
Global Const $xlYMDFormat = 5 ; YMD date format

; XlFileFormat Enumeration. Specifies the file format when saving the worksheet.
; See: http://msdn.microsoft.com/en-us/library/ff198017.aspx (Excel 2010)
Global Const $xlAddIn = 18 ; Microsoft Excel 97-2003 Add-In
Global Const $xlAddIn8 = 18 ; Microsoft Excel 97-2003 Add-In
Global Const $xlCSV = 6 ; CSV
Global Const $xlCSVMac = 22 ; Macintosh CSV
Global Const $xlCSVMSDOS = 24 ; MSDOS CSV
Global Const $xlCSVWindows = 23 ; Windows CSV
Global Const $xlCurrentPlatformText = -4158 ; Current Platform Text
Global Const $xlDBF2 = 7 ; DBF2
Global Const $xlDBF3 = 8 ; DBF3
Global Const $xlDBF4 = 11 ; DBF4
Global Const $xlDIF = 9 ; DIF
Global Const $xlExcel12 = 50 ; Excel12
Global Const $xlExcel2 = 16 ; Excel2
Global Const $xlExcel2FarEast = 27 ; Excel2 FarEast
Global Const $xlExcel3 = 29 ; Excel3
Global Const $xlExcel4 = 33 ; Excel4
Global Const $xlExcel4Workbook = 35 ; Excel4 Workbook
Global Const $xlExcel5 = 39 ; Excel5
Global Const $xlExcel7 = 39 ; Excel7
Global Const $xlExcel8 = 56 ; Excel8
Global Const $xlExcel9795 = 43 ; Excel9795
Global Const $xlHtml = 44 ; HTML format
Global Const $xlIntlAddIn = 26 ; International Add-In
Global Const $xlIntlMacro = 25 ; International Macro
Global Const $xlOpenDocumentSpreadsheet = 60 ; OpenDocument Spreadsheet
Global Const $xlOpenXMLAddIn = 55 ; Open XML Add-In
Global Const $xlOpenXMLTemplate = 54 ; Open XML Template
Global Const $xlOpenXMLTemplateMacroEnabled = 53 ; Open XML Template Macro Enabled
Global Const $xlOpenXMLWorkbook = 51 ; Open XML Workbook
Global Const $xlOpenXMLWorkbookMacroEnabled = 52 ; Open XML Workbook Macro Enabled
Global Const $xlSYLK = 2 ; SYLK
Global Const $xlTemplate = 17 ; Template
Global Const $xlTemplate8 = 17 ; Template 8
Global Const $xlTextMac = 19;  Macintosh Text
Global Const $xlTextMSDOS = 21 ; MSDOS Text
Global Const $xlTextPrinter = 36 ; Printer Text
Global Const $xlTextWindows = 20 ; Windows Text
Global Const $xlUnicodeText = 42 ; Unicode Text
Global Const $xlWebArchive = 45 ; Web Archive
Global Const $xlWJ2WD1 = 14 ; WJ2WD1
Global Const $xlWJ3 = 40 ; WJ3
Global Const $xlWJ3FJ3 = 41 ; WJ3FJ3
Global Const $xlWK1 = 5 ; WK1
Global Const $xlWK1ALL = 31 ; WK1ALL
Global Const $xlWK1FMT = 30 ; WK1FMT
Global Const $xlWK3 = 15 ; WK3
Global Const $xlWK3FM3 = 32 ; WK3FM3
Global Const $xlWK4 = 38; WK4
Global Const $xlWKS = 4 ; Worksheet
Global Const $xlWorkbookDefault = 51 ; Workbook default
Global Const $xlWorkbookNormal = -4143 ; Workbook normal
Global Const $xlWorks2FarEast = 28 ; Works2 FarEast
Global Const $xlWQ1 = 34 ; WQ1
Global Const $xlXMLSpreadsheet = 46 ; XML Spreadsheet

; XlFindLookIn Enumeration. Specifies the type of data to search.
; See: http://msdn.microsoft.com/en-us/library/ff822180.aspx (Excel 2010)
Global Const $xlComments = -4144 ; Comments
Global Const $xlFormulas = -4123 ; Formulas
Global Const $xlValues = -4163 ; Values

; XlFixedFormatQuality Enumeration. Specifies the quality of speadsheets saved in different fixed formats.
; See: http://msdn.microsoft.com/en-us/library/ff838396.aspx (Excel 2010)580

Global Const $xlQualityMinimum = 1 ; Minimum quality
Global Const $xlQualityStandard = 0 ; Standard quality

; XlFixedFormatType Enumeration. Specifies the type of file format.
; See: http://msdn.microsoft.com/en-us/library/ff195006.aspx (Excel 2010)
Global Const $xlTypePDF = 0 ; "PDF" — Portable Document Format file (.pdf)
Global Const $xlTypeXPS = 1 ; "XPS" — XPS Document (.xps)

; XlLookAt Enumeration. Specifies whether a match is made against the whole of the search text or any part of the search text.
; See: http://msdn.microsoft.com/en-us/library/ff823160.aspx (Excel 2010)
Global Const $xlPart = 2 ; Match against any part of the search text
Global Const $xlWhole = 1 ; Match against the whole of the search text

; xlSheetVisibility Enumeration. Specifies whether the object is visible.
; See: http://msdn.microsoft.com/en-us/library/ff821673.aspx (Excel 2010)
Global Const $xlSheetHidden = 0 ; Hides the worksheet which the user can unhide via menu
Global Const $xlSheetVeryHidden = 2 ; Hides the object so that the only way for you to make it visible again is by setting this property to True (the user cannot make the object visible)
Global Const $xlSheetVisible = -1 ; Displays the sheet

; XlSortDataOption Enumeration. Specifies how to sort text.
; See: http://msdn.microsoft.com/en-us/library/ff821069.aspx (Excel 2010)
Global Const $xlSortNormal = 0 ; Sorts numeric and text data separately
Global Const $xlSortTextAsNumbers = 1 ; Treat text as numeric data for the sort

; XlSortOrder Enumeration. Specifies the sort order for the specified field or range.
; See: http://msdn.microsoft.com/en-us/library/ff834316.aspx (Excel 2010)
Global Const $xlAscending = 1 ; Sorts the specified field in ascending order
Global Const $xlDescending = 2 ; Sorts the specified field in descending order

; XlSortOrientation Enumeration. Specifies the sort orientation.
; See: http://msdn.microsoft.com/en-us/library/ff839607.aspx (Excel 2010)
Global Const $xlSortColumns = 1 ; Sorts by column
Global Const $xlSortRows = 2 ; Sorts by row. This is the default value

; XlTextParsingType Enumeration. Specifies the column format for the data in the text file that you are importing into a query table.
; See: http://msdn.microsoft.com/en-us/library/ff822876.aspx (Excel 2010)
Global Const $xlDelimited = 1 ; Default. Indicates that the file is delimited by delimiter characters
Global Const $xlFixedWidth = 2 ; Indicates that the data in the file is arranged in columns of fixed widths

; XlTextQualifier Enumeration. Specifies the delimiter to use to specify text.
; See: http://msdn.microsoft.com/en-us/library/ff838376.aspx (Excel 2010)
Global Const $xlTextQualifierDoubleQuote = 1 ; Double quotation mark (")
Global Const $xlTextQualifierNone = -4142 ; No delimiter
Global Const $xlTextQualifierSingleQuote = 2 ; Single quotation mark (')

; XlYesNoGuess Enumeration. Specifies whether or not the first row contains headers.
; See: http://msdn.microsoft.com/en-us/library/ff838812.aspx (Excel 2010)
Global Const $xlGuess = 0 ; Excel determines whether there is a header, and where it is, if there is one
Global Const $xlNo = 2 ; Default. The entire range should be sorted
Global Const $xlYes = 1 ; The entire range should not be sorted
; ===============================================================================================================================

; #CURRENT# =====================================================================================================================
;_Excel_Open
;_Excel_Close
;_Excel_BookAttach
;_Excel_BookClose
;_Excel_BookNew
;_Excel_BookOpen
;_Excel_BookOpenText
;_Excel_BookSave
;_Excel_BookSaveAs
;_Excel_Export
;_Excel_RangeFind
;_Excel_RangeRead
;_Excel_RangeReplace
;_Excel_RangeSort
;_Excel_RangeWrite
;_Excel_ErrorNotify
; ===============================================================================================================================

; #INTERNAL_USE_ONLY#============================================================================================================
;__Excel_CloseOnQuit
; ===============================================================================================================================

;_Excel_ErrorNotify(2)

; #FUNCTION# ====================================================================================================================
; Name...........: _Excel_Open
; Description ...: Connects to an existing Excel instance or creates a new Excel Application Object.
; Syntax.........: _Excel_Open([$bVisible = True[, $bDisplayAlerts = False[, $bScreenUpdating = True[, $bForceNew = False]]]])
; Parameters ....: $bVisible        - Optional: True specifies that the application will be visible (default = True)
;                 $bDisplayAlerts  - Optional: False suppresses all prompts and alert messages while opening the workbook (default = False)
;                 $bScreenUpdating - Optional: False suppresses screen updating to speed up your script (default = True)
;                 $bForceNew       - Optional: True forces to create a new Excel instance even if there is already a running instance (default = False)
; Return values .: Success - Returns the Excel application object. Sets @extended to:
;                 |0 - Excel was already running
;                 |1 - Excel was not running or $bForceNew was set to True. Excel has been started by this function
;                 Failure - Returns 0 and sets @error
;                 |1 - Error returned by ObjCreate. @extended is set to the error code returned by ObjCreate
; Author ........: water
; Modified ......:
; Remarks .......: If $bDisplayAlerts is set to False and a message requires a response, Excel chooses the default response.
; Related .......:
; Link ..........:
; Example .......: Yes
; ===============================================================================================================================
Func _Excel_Open($bVisible = Default, $bDisplayAlerts = Default, $bScreenUpdating = Default, $bForceNew = Default)
    Local $oExcel, $bApplCloseOnQuit = False
    If $bVisible = Default Then $bVisible = True
    If $bDisplayAlerts = Default Then $bDisplayAlerts = False
    If $bScreenUpdating = Default Then $bScreenUpdating = True
    If $bForceNew = Default Then $bForceNew = False
    If Not $bForceNew Then $oExcel = ObjGet("", "Excel.Application")
    If $bForceNew Or @error Then
        $oExcel = ObjCreate("Excel.Application")
        If @error Or Not IsObj($oExcel) Then Return SetError(1, @error, 0)
        $bApplCloseOnQuit = True
    EndIf
    __Excel_CloseOnQuit($bApplCloseOnQuit)
    $oExcel.Visible = $bVisible
    $oExcel.DisplayAlerts = $bDisplayAlerts
    $oExcel.ScreenUpdating = $bScreenUpdating
    Return SetError(0, $bApplCloseOnQuit, $oExcel)
EndFunc   ;==>_Excel_Open

; #FUNCTION# ====================================================================================================================
; Name...........: _Excel_Close
; Description ...: Closes all worksheets and the instance of the Excel application.
; Syntax.........: _Excel_Close($oExcel[, $iSaveChanges = True[, $bForceClose = False]])
; Parameters ....: $oExcel     - Excel application object returned by a preceding call to _Excel_Open
;                 $bSaveChanges - Optional: Specifies whether changed worksheets should be saved before closing (default = True)
;                 $bForceClose  - Optional: If True the Excel application is closed even when it was not started by _Excel_Open (default = False)
; Return values .: Success - Returns 1
;                 Failure - Returns 0 and sets @error
;                 |1 - $oExcel is not an object
;                 |2 - Error returned by method Application.Quit. @extended is set to the COM error code
;                 |3 - Error returned by method Application.Save. @extended is set to the COM error code
; Author ........: water
; Modified ......:
; Remarks .......: If Excel was started by _Excel_Open then _Excel_Close closes all workbooks
;                 (even those opened manually by the user after _Excel_Open for this instance) and closes the specified Excel instance.
;                 If _Excel_Open connected to an already running instance of Excel then you have to set $bForceClose to True to do the same.
; Related .......:
; Link ..........:
; Example .......: Yes
; ===============================================================================================================================
Func _Excel_Close(ByRef $oExcel, $bSaveChanges = Default, $bForceClose = Default)
    If $bSaveChanges = Default Then $bSaveChanges = True
    If $bForceClose = Default Then $bForceClose = False
    If Not IsObj($oExcel) Then Return SetError(1, 0, 0)
    If $bSaveChanges Then
        For $oWorkbook In $oExcel.Workbooks
            If Not $oWorkbook.Saved Then
                $oWorkbook.Save()
                If @error Then Return SetError(3, @error, 0)
            EndIf
        Next
    EndIf
    If __Excel_CloseOnQuit() Or $bForceClose Then
        $oExcel.Quit()
        $oExcel = 0
        If @error Then Return SetError(2, @error, 0)
    EndIf
    __Excel_CloseOnQuit(False)
    Return 1
EndFunc   ;==>_Excel_Close

; #FUNCTION# ====================================================================================================================
; Name...........: _Excel_BookAttach
; Description ...: Attach to the first instance of a workbook where the search string matches based on the selected mode.
; Syntax.........: _Excel_BookAttach($sString[, $sMode = "FilePath"])
; Parameters ....: $sString  - String to search for
;                 $sMode    - Optional: specifies search mode:
;                 |FileName - Name of the open workbook
;                 |FilePath - Full path to the open workbook (default)
;                 |Title    - Title of the Excel window
; Return values .: Success - Returns the Excel workbook object
;                 Failure - Returns 0 and sets @error:
;                 |1 - An error occurred or $sString can't be found in any of the open workbooks. @extended is set to the COM error code
;                 |2 - $sMode is invalid
; Author ........: Bob Anthony (big_daddy)
; Modified.......: water
; Remarks .......: All instances of Excel are searched
; Related .......: _Excel_BookNew, _Excel_BookOpen
; Link ..........:
; Example .......: Yes
; ===============================================================================================================================
Func _Excel_BookAttach($sString, $sMode = Default)
    Local $oWorkbook, $iCount = 0, $sCLSID_Workbook = "{00020819-0000-0000-C000-000000000046}"
    If $sMode = Default Then $sMode = "FilePath"
    ; Loop through all workbooks
    While True
        $oWorkbook = ObjGet("", $sCLSID_Workbook, $iCount + 1)
        If @error Then Return SetError(1, @error, 0)
        Switch $sMode
            Case "filename"
                If $oWorkbook.Name = $sString Then Return $oWorkbook
            Case "filepath"
                If $oWorkbook.FullName = $sString Then Return $oWorkbook
            Case "title"
                If ($oWorkbook.Application.Caption) = $sString Then Return $oWorkbook
            Case Else
                Return SetError(2, 0, 0)
        EndSwitch
        $iCount += 1
    WEnd
EndFunc   ;==>_Excel_BookAttach

; #FUNCTION# ====================================================================================================================
; Name...........: _Excel_BookClose
; Description ...: Closes the specified workbook.
; Syntax.........: _Excel_BookClose($oExcel, $oWorkbook[, $bSave = True])
; Parameters ....: $oExcel    - Excel application object
;                 $oWorkbook - Excel workbook object
;                 $bSave     - If True the workbook will be saved before closing (default = True)
; Return values .: Success - Returns 1
;                 Failure - Returns 0 and sets @error:
;                 |1 - $oExcel is not an object
;                 |2 - $oWorkbook is not an object
;                 |3 - Error occurred when saving the workbook. @extended is set to the error code returned by the Save method
;                 |4 - Error occurred when closing the workbook. @extended is set to the error code returned by the Close method
; Author ........: SEO <locodarwin at yahoo dot com>
; Modified.......: 07/17/2008 by bid_daddy; litlmike, water
; Remarks .......: None
; Related .......:
; Link ..........:
; Example .......: Yes
; ===============================================================================================================================
Func _Excel_BookClose($oExcel, ByRef $oWorkbook, $bSave = Default)
    If Not IsObj($oExcel) Then Return SetError(1, 0, 0)
    If Not IsObj($oWorkbook) Then Return SetError(2, 0, 0)
    If $bSave = Default Then $bSave = True
    If $bSave Then
        $oWorkbook.Save()
        If @error Then Return SetError(3, @error, 0)
    EndIf
    $oWorkbook.Close()
    If @error Then Return SetError(4, @error, 0)
    $oWorkbook = 0
    Return 1
EndFunc   ;==>_Excel_BookClose

; #FUNCTION# ====================================================================================================================
; Name...........: _Excel_BookNew
; Description ...: Creates new workbook and returns its object identifier.
; Syntax.........: _Excel_BookNew($oExcel[, $iSheets = Default])
; Parameters ....: $oExcel   - Excel application object where you want to create the new workbook.
;                 $iSheets  - Optional: Number of sheets to create in the new workbook (default = Excel default value)
; Return values .: Success - Returns new Excel application object identifier
;                 Failure - Returns 0 and sets @error:
;                 |1 - $oExcel is not an object
;                 |2 - Error setting SheetsInNewWorkbook to $iSheets. @extended is set to the COM error code
;                 |3 - Error returned by method Workbooks.Add. @extended is set to the COM error code
; Author ........: SEO <locodarwin at yahoo dot com>
; Modified.......: litlmike, water
; Remarks .......:
; Related .......: _Excel_BookAttach
; Link ..........:
; Example .......: Yes
; ===============================================================================================================================
Func _Excel_BookNew($oExcel, $iSheets = Default)
    If Not IsObj($oExcel) Then Return SetError(1, 0, 0)
    With $oExcel
        If $iSheets <> Default Then
            Local $iSheetsBackup = .SheetsInNewWorkbook = $iSheets
            .SheetsInNewWorkbook = $iSheets
            If @error Then Return SetError(2, @error, 0)
        EndIf
        Local $oWorkbook = .Workbooks.Add()
        If @error Then Return SetError(3, @error, 0)
        .ActiveWorkbook.Sheets(1).Select()
        If $iSheets <> Default Then .SheetsInNewWorkbook = $iSheetsBackup
    EndWith
    Return $oWorkbook
EndFunc   ;==>_Excel_BookNew

; #FUNCTION# ====================================================================================================================
; Name...........: _Excel_BookOpen
; Description ...: Opens an existing workbook and returns its object identifier.
; Syntax.........: _Excel_BookOpen($oExcel, $sFilePath[, $bReadOnly = False[, $sPassword = Default[, $sWritePassword = Default]]])
; Parameters ....: $oExcel       - Excel application object where you want to open the new workbook
;                 $sFilePath      - Path and filename of the file to be opened
;                 $bReadOnly      - Optional: Flag, whether to open the workbook as read-only (True or False) (default = False)
;                 $sPassword      - Optional: The password that was used to read-protect the workbook, if any (default is none)
;                 $sWritePassword - Optional: 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 - $oExcel is not an object
;                 |2 - Specified $sFilePatch 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 .......:
; Related .......: _Excel_BookAttach, _Excel_BookOpenText
; Link ..........:
; Example .......: Yes
; ===============================================================================================================================
Func _Excel_BookOpen($oExcel, $sFilePath, $bReadOnly = Default, $sPassword = Default, $sWritePassword = Default)
    Local $oWorkbook
    If Not FileExists($sFilePath) Then Return SetError(2, 0, 0)
    If Not IsObj($oExcel) Then Return SetError(1, @error, 0)
    If $bReadOnly = Default Then $bReadOnly = False
    $oWorkbook = $oExcel.Workbooks.Open($sFilePath, Default, $bReadOnly, Default, $sPassword, $sWritePassword)
    If @error Then Return SetError(3, @error, 0)
    ; If a readwrite workbook was opened readonly then return an error
    If $bReadOnly = False And $oWorkbook.Readonly Then
        $oWorkbook.Close(False)
        $oWorkbook = 0
        Return SetError(4, 0, 0)
    EndIf
    ; Select the first visible worksheet
    For $i = 1 To $oWorkbook.Sheets.Count
        If $oWorkbook.Sheets($i).Visible = $xlSheetVisible Then
            $oWorkbook.Sheets($i).Select()
            ExitLoop
        EndIf
    Next
    Return $oWorkbook
EndFunc   ;==>_Excel_BookOpen

; #FUNCTION# ====================================================================================================================
; Name...........: _Excel_BookOpenText
; Description ...: Opens a text file and parses the content as a new workbook with a single sheet.
; Syntax.........: _Excel_BookOpenText($oExcel, $sFilePath[, $iStartRow = 1[, $iDataType = Default[, $sTextQualifier = $xlTextQualifierDoubleQuote[, $bConsecutiveDelimiter = False[, $sDelimiter = ";"[, $aFieldInfo = ""[, $sDecimalSeparator = "."[, $sThousandsSeparator = ","[, $bTrailingMinusNumbers = True]]]]]]]]])
; Parameters ....: $oExcel              - Excel application object where you want to open the new workbook
;                 $sFilePath             - Path and filename of the file to be opened
;                 $iStartRow             - Optional: The row at which to start parsing the file (default = 1)
;                 $iDataType             - Optional: Specifies the column format of the data in the file. Can be any of the XlTextParsingType enumeration. If set to Default Excel attempts to determine the column format (default = Default)
;                 $sTextQualifier       - Optional: Specifies the text qualifier (default = $xlTextQualifierDoubleQuote)
;                 $bConsecutiveDelimiter - Optional: True will consider consecutive delimiters as one delimiter (default = False)
;                 $sDelimiter           - Optional: Single character to be used as delimiter (default = ";")
;                 $aFieldInfo           - Optional: An array containing parse information for individual columns of data. The interpretation depends on the value of DataType.
;                                          When the data is delimited, this argument is an array of two-element arrays, with each two-element array specifying the conversion options for a particular column.
;                                          The first element is the column number (1-based), and the second element is one of the XlColumnDataType constants specifying how the column is parsed (default = "")
;                 $sDecimalSeparator     - Optional: Decimal separator that Excel uses when recognizing numbers. Default setting is the system setting (default = Default)
;                 $sThousandsSeparator   - Optional: Thousands separator that Excel uses when recognizing numbers. The default setting is the system setting (default = Default)
;                 $bTrailingMinusNumbers - Optional: True treats numbers with a minus character at the end as negative numbers. False treats such numbers as text (default = True)
; Return values .: Success - Returns new object identifier
;                 Failure - Returns 0 and sets @error:
;                 |1 - $oExcel is not an object
;                 |2 - Specified $sFilePatch does not exist
;                 |3 - Unable to open or parse $sFilePath. @extended is set to the error code returned by the OpenText method
; Author ........: water
; Modified.......:
; Remarks .......: Parameter $aFieldInfo has to be an array containing arrays, not a 2D array. Please see example 2
; Related .......: _Excel_BookAttach, _Excel_BookOpen
; Link ..........:
; Example .......: Yes
; ===============================================================================================================================
Func _Excel_BookOpenText($oExcel, $sFilePath, $iStartRow = Default, $iDataType = Default, $sTextQualifier = Default, $bConsecutiveDelimiter = Default, $sDelimiter = Default, $aFieldInfo = Default, $sDecimalSeparator = Default, $sThousandsSeparator = Default, $bTrailingMinusNumbers = Default)
    Local $oWorkbook, $bTab = False, $bSemicolon = False, $bComma = False, $bSpace = False, $aDelimiter[1], $bOther = False, $sOtherChar
    If Not IsObj($oExcel) Then Return SetError(1, @error, 0)
    If Not FileExists($sFilePath) Then Return SetError(2, 0, 0)
    If $iStartRow = Default Then $iStartRow = 1
    If $sTextQualifier = Default Then $sTextQualifier = $xlTextQualifierDoubleQuote
    If $bConsecutiveDelimiter = Default Then $bConsecutiveDelimiter = False
    If $sDelimiter = Default Then $sDelimiter = ";"
    If $bTrailingMinusNumbers = Default Then $bTrailingMinusNumbers = True
    If StringInStr($sDelimiter, @TAB) > 0 Then $bTab = True
    If StringInStr($sDelimiter, ";") > 0 Then $bSemicolon = True
    If StringInStr($sDelimiter, ",") > 0 Then $bComma = True
    If StringInStr($sDelimiter, " ") > 0 Then $bSpace = True
    $aDelimiter = StringRegExp($sDelimiter,"[^;, " & @TAB & "]", 1)
    If @error = 0 Then
        $sOtherChar = $aDelimiter[0]
        $bOther = True
    EndIf
    $oWorkbook = $oExcel.Workbooks.OpenText($sFilePath, Default, $iStartRow, $iDataType, $sTextQualifier, $bConsecutiveDelimiter, _
        $bTab, $bSemicolon, $bComma, $bSpace, $bOther, $sOtherChar, $aFieldInfo, Default, $sDecimalSeparator, $sThousandsSeparator, _
        $bTrailingMinusNumbers)
    If @error Then Return SetError(3, @error, 0)
    Return $oWorkbook
EndFunc   ;==>_Excel_BookOpenText

; #FUNCTION# ====================================================================================================================
; Name...........: _Excel_BookSave
; Description ...: Saves the specified workbook.
; Syntax.........: _Excel_BookSave($oExcel[, $oWorkbook = Default])
; Parameters ....: $oExcel    - Excel application object
;                 $oWorkbook - Optional: Excel workbook object. If set to Default the active workbook will be saved (default)
; Return values .: Success - Returns 1
;                 Failure - Returns 0 and sets @error:
;                 |1 - $oExcel is not an object
;                 |2 - $oWorkbook is not an object or Default
;                 |3 - Error occurred when saving the workbook. @extended is set to the error code returned by the Save method
; Author ........: SEO <locodarwin at yahoo dot com>
; Modified.......: litlmike, water
; Remarks .......: A newly created workbook has to be saved using _Excel_BookSaveAs
; Related .......: _Excel_BookSaveAs
; Link ..........:
; Example .......: Yes
; ===============================================================================================================================
Func _Excel_BookSave($oExcel, $oWorkbook = Default)
    If Not IsObj($oExcel) Then Return SetError(1, 0, 0)
    If $oWorkbook = Default Then $oWorkbook = $oExcel.ActiveWorkbook
    If Not IsObj($oWorkbook) Then Return SetError(2, 0, 0)
    $oWorkbook.Save()
    If @error Then Return SetError(3, @error, 0)
    Return 1
EndFunc   ;==>_Excel_BookSave

; #FUNCTION# ====================================================================================================================
; Name...........: _Excel_BookSaveAs
; Description ...: Saves the specified workbook with a new filename and/or type.
; Syntax.........: _Excel_BookSaveAs($oExcel, $oWorkbook, $sFilePath[, $iType = $xlWorkbookNormal[, $bOverWrite = False[, $sPassword = Default[, $sWritePassword = Default[, $bReadOnlyRecommended = False]]]]])
; Parameters ....: $oExcel             - Excel application object
;                 $oWorkbook            - Excel workbook object. If set to Default the active workbook will be saved
;                 $sFilePath            - Path and filename of the file to be read
;                 $iType                - Optional: Excel writable filetype. Can be any value of the XlFileFormat enumeration (default = $xlWorkbookNormal)
;                 $bOverWrite          - Optional: True overwrites an already existing file (default = False)
;                 $sPassword            - Optional: The string password to protect the sheet with; if set to Default no password will be used (default = Default)
;                 $sWritePassword      - Optional: The string write-access password to protect the sheet with; if set to Default no password will be used (default = Default)
;                 $bReadOnlyRecommended - Optional: True displays a message when the file is opened, recommending that the file be opened as read-only (default = False)
; Return values .: Success - Returns 1
;                 Failure - Returns 0 and sets @error:
;                 |1 - $oExcel is not an object
;                 |2 - $oWorkbook is not an object
;                 |3 - Invalid filetype string
;                 |4 - File exists, overwrite flag not set
;                 |5 - Error occurred when saving the workbook. @extended is set to the error code returned by the SaveAs method
; Author ........: SEO <locodarwin at yahoo dot com>
; Modified.......: litlmike, water
; Remarks .......:
; Related .......: _Excel_BookSave
; Link ..........:
; Example .......: Yes
; ===============================================================================================================================
Func _Excel_BookSaveAs($oExcel, $oWorkbook, $sFilePath, $iType = Default, $bOverWrite = Default, $sPassword = Default, $sWritePassword = Default, $bReadOnlyRecommended = Default)
    If Not IsObj($oExcel) Then Return SetError(1, 0, 0)
    If $oWorkbook = Default Then $oWorkbook = $oExcel.ActiveWorkbook
    If Not IsObj($oWorkbook) Then Return SetError(2, 0, 0)
    If $iType = Default Then $iType = $xlWorkbookNormal
    If Not IsNumber($iType) Then Return SetError(3, 0, 0)
    If $bOverWrite = Default Then $bOverWrite = False
    If $bReadOnlyRecommended = Default Then $bReadOnlyRecommended = False
    If FileExists($sFilePath) Then
        If Not $bOverWrite Then Return SetError(4, 0, 0)
        FileDelete($sFilePath)
    EndIf
    $oWorkbook.SaveAs($sFilePath, $iType, $sPassword, $sWritePassword, $bReadOnlyRecommended)
    If @error Then Return SetError(5, @error, 0)
    Return 1
EndFunc   ;==>_Excel_BookSaveAs

; #FUNCTION# ====================================================================================================
; Name...........: _Excel_Export
; Description....: Exports a workbook, worksheet, chart or range object as PDF or XPS.
; Syntax.........: _Excel_Export($oExcel, $vObject, $sFilename[, $iType = $xlTypePDF[, $iQuality = $xlQualityStandard[, $iIncludeProperties = True[, $iFrom = Default[, $iTo = Default[, $bOpenAfterPublish = Default]]]]]])
; Parameters ....: $oExcel           - Excel application object
;                 $vObject          - Workbook, worksheet, chart or range object to export as PDF or XPS. Range can be a range object or an A1 range
;                 $sFilename          - Path/name of the exported file
;                 $iType              - Optional: Can be either $xlTypePDF or $xlTypeXPS of the XlFixedFormatType enumeration (default = $xlTypePDF)
;                 $iQuality        - Optional: Can be either $xlQualityStandard or $xlQualityMinimum of the xx enumeration (default = $xlQualityStandard)
;                 $iIncludeProperties - Optional: True indicates that document properties should be included (default = True)
;                 $iFrom              - Optional: The page number at which to start publishing (default = Default = start at the beginning)
;                 $iTo              - Optional: The page number at which to end publishing (default = Default = end at the last page)
;                 $bOpenAfterPublish  - Optional: True displays the file in viewer after it is published (default = False)
; Return values .: Success - Returns 1
;                 Failure - Returns 0 and sets @error:
;                 |1 - $vObject is not an object
;                 |2 - $sFilename is empty
;                 |3 - Error exporting the object. @extended is set to the error code returned by the ExportAsFixedFormat method
; Author ........: water
; Modified ......:
; Remarks .......:
; Related .......:
; Link ..........:
; Example .......: Yes
; ===============================================================================================================
Func _Excel_Export($oExcel, $vObject, $sFilename, $iType = Default, $iQuality = Default, $bIncludeProperties = Default, $iFrom = Default, $iTo = Default, $bOpenAfterPublish = Default)
    If IsString($vObject) Then $vObject = $oExcel.Range($vObject)
    If Not IsObj($vObject) Then Return SetError(1, 0, 0)
    If $sFilename = "" Then Return SetError(2, 0, 0)
    If $iType = Default Then $iType = $xlTypePDF
    If $iQuality = Default Then $iQuality = $xlQualityStandard
    If $bIncludeProperties = Default Then $bIncludeProperties = True
    If $bOpenAfterPublish = Default Then $bOpenAfterPublish = False
    $vObject.ExportAsFixedFormat($iType, $sFilename, $iQuality, $bIncludeProperties, Default, $iFrom, $iTo, $bOpenAfterPublish)
    If @error <> 0 Then Return SetError(3, @error, 0)
    Return 1
EndFunc   ;==>_Excel_Export

; #FUNCTION# ====================================================================================================================
; Name...........: _Excel_RangeFind
; Description ...: Finds matching cells in a range or workbook and returns the address and the content.
; Syntax.........: _Excel_RangeFind($oExcel, $oWorkbook, $sSearch[, $sRange = Default[, $iLookIn = $xlValues[, $iLookAt = $xlPart[, $bMatchcase = False]]]])
; Parameters ....: $oExcel   - Excel application object
;                 $oWorkbook  - Excel workbook object. If set to Default the active workbook will be used
;                 $sSearch    - Search string. Can be a string (wildcards - *?~ - can be used) or any Excel data type. See Remarks
;                 $sRange    - Optional: An A1 range or keyword Default to search all cells of the workbook (default = Default)
;                 $iLookIn    - Optional: Specifies where to search. Can be any of the XLFindLookIn enumeration (default = $xlValues)
;                 $iLookAt    - Optional: Specifies whether the search text must match as a whole or any part. Can be any of the XLLookAt enumeration (default = $xlPart)
;                 $bMatchcase - Optional: True = case sensitive, False = case insensitive (default = False)
; Return values .: Success - two-dimensional one based array with the following information:
;                 |0 - Name of the worksheet
;                 |1 - Name of the cell
;                 |2 - Address of the cell
;                 |3 - Value of the cell
;                 |4 - Formula of the cell
;                 |5 - Comment of the cell
;                 Failure - Returns 0 and sets @error:
;                 |1 - $oExcel is not an object
;                 |2 - $oWorkbook is not an object
;                 |3 - $sSearch is empty
;                 |4 - $sRange is invalid. @extended is set to the COM error code
;                 |5 - Error returned by the Find method. @extended is set to the COM error code
; Author ........: water
; Modified.......:
; Remarks .......: Excel recognizes the following wildcards:
;                 ? (question mark)             - Any single character
;                 * (asterisk)                   - Any number of characters
;                 ~ (tilde) followed by ?, *, or ~ - A question mark, asterisk, or tilde
; Related .......: This function mimics the Ctrl+F functionality of Excel, except that it adds the cells comment to the result.
; Link ..........: http://office.microsoft.com/en-us/excel-help/wildcard-characters-HP005203612.aspx
; Example .......: Yes
; ===============================================================================================================================
Func _Excel_RangeFind($oExcel, $oWorkbook, $sSearch, $sRange = Default, $iLookIn = Default, $iLookAt = Default, $bMatchcase = Default)
    If Not IsObj($oExcel) Then Return SetError(1, 0, 0)
    If Not IsObj($oWorkbook) Then Return SetError(2, 0, 0)
    If $oWorkbook = Default Then $oWorkbook = $oExcel.ActiveWorkbook
    If StringStripWS($sSearch, 3) = "" Then Return SetError(3, 0, 0)
    If $iLookIn = Default Then $iLookIn = $xlValues
    If $iLookAt = Default Then $iLookAt = $xlPart
    If $bMatchcase = Default Then $bMatchcase = False
    Local $oRange, $oMatch, $sFirst
    If $sRange = Default Then
        $oRange = $oExcel.Cells
    Else
        $oRange = $oExcel.Range($sRange)
        If @error Then Return SetError(4, @error, 0)
    EndIf
    $oMatch = $oRange.Find($sSearch, Default, $iLookIn, $iLookAt, Default, Default, $bMatchcase)
    If @error Or Not IsObj($oMatch) Then Return SetError(5, @error, 0)
    $sFirst = $oMatch.Address
    Local $aResult[100][6], $iIndex = 1
    While 1
        $aResult[$iIndex][0] = $oMatch.Worksheet.Name
        $aResult[$iIndex][1] = $oMatch.Name.Name
        $aResult[$iIndex][2] = $oMatch.Address
        $aResult[$iIndex][3] = $oMatch.Value
        $aResult[$iIndex][4] = $oMatch.Formula
        $aResult[$iIndex][5] = $oMatch.Comment.Text
        $oMatch = $oRange.Findnext($oMatch)
        If $sFirst = $oMatch.Address Then ExitLoop
        $iIndex = $iIndex + 1
        If Mod($iIndex, 100) = 0 Then ReDim $aResult[UBound($aResult, 1) + 100][6]
    WEnd
    ReDim $aResult[$iIndex + 1][6]
    $aResult[0][0] = $iIndex
    $aResult[0][1] = 6
    Return $aResult
EndFunc   ;==>_Excel_RangeFind

; #FUNCTION# ====================================================================================================================
; Name...........: _Excel_RangeRead
; Description ...: Reads the value, formula or displayed text from a cell or range of cells of the specified workbook and worksheet.
; Syntax.........: _Excel_RangeRead($oExcel, $oWorkbook, $oWorksheet, $vRangeOrRow[, $iColumn = 1[,  $iReturn = 1]])
; Parameters ....: $oExcel    - Excel application object
;                 $oWorkbook   - Excel workbook object. If set to Default the active workbook will be used
;                 $oWorksheet  - Excel worksheet object. If set to Default the active sheet will be used
;                 $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:
;                 |1 - Value (default)
;                 |2 - Formula
;                 |3 - The displayed text
; Return values .: Success - Returns the data from the specified cell(s). A string for a cell, an zero-based array for a range of cells.
;                 Failure - Returns 0 and sets @error:
;                 |1 - $oExcel is not an object
;                 |2 - $oWorkbook is not an object
;                 |3 - $oWorksheet is not an object
;                 |4 - Specified parameter is incorrect. Sets @extended:
;                 |    0 - Row out of valid range
;                 |    1 - Column out of valid range
;                 |5 - Error occurred when reading data. @extended is set to the error code returned when accessing the Value property
;                 |6 - Parameter $iReturn is invalid. Has to be > 1 and < 3
; Author ........: SEO <locodarwin at yahoo dot com>
; Modified.......: litlmike, water
; Remarks .......:
; Related .......:
; Link ..........:
; Example .......: Yes
; ===============================================================================================================================
Func _Excel_RangeRead($oExcel, $oWorkbook, $oWorksheet, $vRangeOrRow, $iColumn = Default, $iReturn = Default)
    If Not IsObj($oExcel) Then Return SetError(1, 0, 0)
    If $oWorkbook = Default Then $oWorkbook = $oExcel.ActiveWorkbook
    If Not IsObj($oWorkbook) Then Return SetError(2, 0, 0)
    If $oWorksheet = Default Then $oWorksheet = $oWorkbook.Activesheet
    If Not IsObj($oWorksheet) Then Return SetError(3, 0, 0)
    If $iColumn = Default Then $iColumn = 1
    If $iReturn = Default Then $iReturn = 1
    If $iReturn < 1 Or $iReturn > 3 Then Return SetError(6, 0, 0)
    Local $vResult
    If Not StringRegExp($vRangeOrRow, "[A-Z,a-z]", 0) Then
        If $vRangeOrRow < 1 Then Return SetError(4, 0, 0)
        If $iColumn < 1 Then Return SetError(4, 1, 0)
        If $iReturn = 1 Then
            $vResult = $oWorksheet.Cells($vRangeOrRow, $iColumn).Value
        ElseIf $iReturn = 2 Then
            $vResult = $oWorksheet.Cells($vRangeOrRow, $iColumn).Formula
        Else
            $vResult = $oWorksheet.Cells($vRangeOrRow, $iColumn).Text
        EndIf
        If @error Then Return SetError(5, @error, 0)
    Else
        If $iReturn = 1 Then
            $vResult = $oExcel.Transpose($oWorksheet.Range($vRangeOrRow).Value)
        ElseIf $iReturn = 2 Then
            $vResult = $oExcel.Transpose($oWorksheet.Range($vRangeOrRow).Formula)
        Else
            $vResult = $oExcel.Transpose($oWorksheet.Range($vRangeOrRow).Text)
        EndIf
        If @error Then Return SetError(5, @error, 0)
    EndIf
    Return $vResult
EndFunc   ;==>_Excel_RangeRead

; #FUNCTION# ====================================================================================================================
; Name...........: _Excel_RangeReplace
; Description ...: Finds and replaces matching strings in a range or workbook.
; Syntax.........: _Excel_RangeReplace($oExcel, $oWorkbook, $sSearch, $sReplace[, $sRange = Default[, $iLookAt = $xlPart[, $bMatchcase = False]]])
; Parameters ....: $oExcel   - Excel application object
;                 $oWorkbook  - Excel workbook object. If set to Default the active workbook will be used
;                 $sSearch    - Search string
;                 $sReplace   - Replace string
;                 $sRange    - Optional: An A1 range or keyword Default to search all cells of the workbook (default = Default)
;                 $iLookAt    - Optional: Specifies whether the search text must match as a whole or any part. Can be any of the XLLookAt enumeration (default = $xlPart)
;                 $bMatchcase - Optional: True = case sensitive, False = case insensitive (default = False)
; Return values .: Success - 1
;                 Failure - Returns 0 and sets @error:
;                 |1 - $oExcel is not an object
;                 |2 - $oWorkbook is not an object
;                 |3 - $sSearch is empty
;                 |4 - $sRange is invalid. @extended is set to the COM error code
;                 |5 - Error returned by the Replace method. @extended is set to the COM error code
; Author ........: water
; Modified.......:
; Remarks .......:
; Related .......:
; Link ..........:
; Example .......: Yes
; ===============================================================================================================================
Func _Excel_RangeReplace($oExcel, $oWorkbook, $sSearch, $sReplace, $sRange = Default, $iLookAt = Default, $bMatchcase = Default)
    If Not IsObj($oExcel) Then Return SetError(1, 0, 0)
    If Not IsObj($oWorkbook) Then Return SetError(2, 0, 0)
    If $oWorkbook = Default Then $oWorkbook = $oExcel.ActiveWorkbook
    If StringStripWS($sSearch, 3) = "" Then Return SetError(3, 0, 0)
    If $iLookAt = Default Then $iLookAt = $xlPart
    If $bMatchcase = Default Then $bMatchcase = False
    Local $oRange, $bReplace
    If $sRange = Default Then
        $oRange = $oExcel.Cells
    Else
        $oRange = $oExcel.Range($sRange)
        If @error Then Return SetError(4, @error, 0)
    EndIf
    $bReplace = $oRange.Replace($sSearch, $sReplace, $iLookAt, Default, $bMatchcase)
    If @error Then Return SetError(5, @error, 0)
    Return $bReplace
EndFunc   ;==>_Excel_RangeReplace

; #FUNCTION# ====================================================================================================================
; Name...........: _Excel_RangeSort
; Description ...: Sorts a cell range.
; Syntax.........: _Excel_RangeSort($oExcel, $oWorkbook, ...)
; Parameters ....: $oExcel     - Excel application object
;                 $oWorkbook    - Excel workbook object. If set to Default the active workbook will be used
;                 $sRange      - An A1 range or keyword Default to sort the whole worksheet (default = Default)
;                 $iHeader    - Specifies whether the first row contains header information. Can be any of the XlYesNoGuess enumeration (default = xlNo)
;                 $bMatchCase   - True to perform a case-sensitive sort, False to perform non-case sensitive sort (Default = False)
;                 $iOrientation - Specifies the sort orientation. Can be any of the XlSortOrientation enumeration (default = $xlSortRows)
;                 $sKey1        - Specifies the first sort field, either as a range name or range object
;                 $iOrder1    - Determines the sort order. Can be any of the XlSortOrder enumeration (default = $xlAscending)
;                 $iSortText1   - Specifies how to sort text in $sKey1. Can be any of the XlSortDataOption enumeration (default = $xlSortNormal)
;                 $sKey2        - See $sKey1
;                 $iOrder2    - See $iOrder1
;                 $iSortText2   - See $iSortText1
;                 $sKey3        - See $sKey1
;                 $iOrder3    - See $iOrder1
;                 $iSortText3   - See $iSortText1
; Return values .: Success - 1
;                 Failure - Returns 0 and sets @error:
;                 |1 - $oExcel is not an object
;                 |2 - $oWorkbook is not an object
; Author ........: water
; Modified.......:
; Remarks .......:
; Related .......:
; Link ..........:
; Example .......: Yes
; ===============================================================================================================================
Func _Excel_RangeSort($oExcel, $oWorkbook, $sRange, $iHeader, $bMatchCase, $iOrientation, $sKey1, $iOrder1 = Default, $iSortText1 = Default, _
    $sKey2 = Default, $iOrder2 = Default, $iSortText2 = Default, $sKey3 = Default, $iOrder3 = Default, $iSortText3 = Default)
    If Not IsObj($oExcel) Then Return SetError(1, 0, 0)
    If Not IsObj($oWorkbook) Then Return SetError(2, 0, 0)
    If $oWorkbook = Default Then $oWorkbook = $oExcel.ActiveWorkbook
    If $iHeader = Default Then $iHeader = $xlNo
    If $bMatchCase= Default Then $bMatchCase = False
    If $iOrientation = Default Then $iOrientation = $xlSortRows
    If $iOrder1 = Default Then $iOrder1 = $xlAscending
    If $iSortText1 = Default Then $iSortText1 = $xlSortNormal
    If $iOrder2 = Default Then $iOrder2 = $xlAscending
    If $iSortText2 = Default Then $iSortText2 = $xlSortNormal
    If $iOrder3 = Default Then $iOrder3 = $xlAscending
    If $iSortText3 = Default Then $iSortText3 = $xlSortNormal
    Local $oRange
    If $sRange = Default Then
        $oRange = $oExcel.Cells  ; <== Maxusedrow/maxusedcolumn
    Else
        $oRange = $oExcel.Range($sRange)
        If @error Then Return SetError(4, @error, 0)
    EndIf
    $oRange.Sort($sKey1, $iOrder1, $sKey2, $iOrder2, $sKey3, $iOrder3, $iHeader, Default, $bMatchCase, $iOrientation, Default, $iSortText1, $iSortText2, $iSortText3)
    If @error Then Return SetError(5, @error, 0)
    Return 1
EndFunc   ;==>_Excel_RangeSort

; #FUNCTION# ====================================================================================================================
; Name...........: _Excel_RangeWrite
; Description ...: Write value(s) or formula(s) to a cell or a cell range on the specified workbook and worksheet.
; Syntax.........: _Excel_RangeWrite($oExcel, $oWorkbook, $oWorksheet, $vValue, $vRangeOrRow[, $iColumn = 1[, $iArrayRowStart = 0[, $iArrayColStart = 0[, $bValue = True]]]])
; Parameters ....: $oExcel       - Excel application object
;                 $oWorkbook      - Excel workbook object. If set to Default the active workbook will be used
;                 $oWorksheet    - Excel worksheet object. If set to Default the active sheet will be used
;                 $vValue        - Can be a string, a 1D or 2D array containing the data to be written to the worksheet
;                 $vRangeOrRow    - Either an A1 range (only valid when $vValue is a string) or an integer row number to write to if using R1C1
;                 $iColumn      - Optional: The column to write to if using R1C1 (default = 1)
;                 $iArrayRowStart - Optional: Array index base for rows (default is 0)
;                 $iArrayColStart - Optional: Array index base for columns (default is 0)
;                 $bValue        - Optional: If True the $vValue will be written to the value property. If False $vValue will be written to the formula property (default = True)
; Return values .: Success - Returns 1
;                 Failure - Returns 0 and sets @error:
;                 |1 - $oExcel is not an object
;                 |2 - $oWorkbook is not an object
;                 |3 - $oWorksheet is not an object
;                 |4 - Parameter out of range. Sets @extended:
;                 |    0 - $vRangeOrRow out of range
;                 |    1 - $iColumn out of range
;                 |5 - Base index out of range. Sets @extended:
;                 |    0 - $iArrayRowStart out of range
;                 |    1 - $iArrayColStart out of range
;                 |6 - Error occurred when writing data. @extended is set to the COM error code
; Author ........: SEO <locodarwin at yahoo dot com>
; Modified.......: litlmike and PsaltyDS 01/04/08 - 2D version _Excel_RangeWrite(), Golfinhu (improved speed), water
; Remarks .......:
; Related .......:
; Link ..........:
; Example .......: Yes
; ===============================================================================================================================
Func _Excel_RangeWrite($oExcel, $oWorkbook, $oWorksheet, $vValue, $vRangeOrRow, $iColumn = Default, $iArrayRowStart = Default, $iArrayColStart = Default, $bValue = Default)
    If Not IsObj($oExcel) Then Return SetError(1, 0, 0)
    If $oWorkbook = Default Then $oWorkbook = $oExcel.ActiveWorkbook
    If Not IsObj($oWorkbook) Then Return SetError(2, 0, 0)
    If $oWorksheet = Default Then $oWorksheet = $oWorkbook.Activesheet
    If Not IsObj($oWorksheet) Then Return SetError(3, 0, 0)
    If $iColumn = Default Then $iColumn = 1
    If $iArrayRowStart = Default Then $iArrayRowStart = 0
    If $iArrayColStart = Default Then $iArrayColStart = 0
    If $bValue = Default Then $bValue = True
    If $iColumn < 1 Then Return SetError(4, 1, 0)
    Local $iDims = UBound($vValue, 0), $iArrayRowSize = UBound($vValue, 1), $iArrayColSize = UBound($vValue, 2)
    If $iArrayColSize = 0 Then $iArrayColSize = 1
    If $iArrayRowStart > $iArrayRowSize Then Return SetError(5, 0, 0)
    If $iArrayColStart > $iArrayColSize Then Return SetError(5, 1, 0)
    Local $iLastRow, $iLastCol
    If Not IsArray($vValue) Then
        If Not StringRegExp($vRangeOrRow, "[A-Z,a-z]", 0) Then
            If $bValue Then
                $oWorksheet.Cells($vRangeOrRow, $iColumn).Value = $vValue
            Else
                $oWorksheet.Cells($vRangeOrRow, $iColumn).Formula = $vValue
            EndIf
        Else
            If $bValue Then
                $oWorksheet.Range($vRangeOrRow).Value = $vValue
            Else
                $oWorksheet.Range($vRangeOrRow).Formula = $vValue
            EndIf
        EndIf
    Else
        If $vRangeOrRow < 1 Then Return SetError(4, 0, 0)
        Local $iNewRowArraySize = $iArrayRowSize - $iArrayRowStart
        Local $iNewColArraySize = $iArrayColSize - $iArrayColStart
        $iLastRow = $vRangeOrRow + $iNewRowArraySize - 1
        $iLastCol = $iColumn + $iNewColArraySize - 1
        ; Create a transposed new array and add new values
        Local $aTransposed[$iNewColArraySize][$iNewRowArraySize]
        For $i = $iArrayRowStart To $iArrayRowSize - 1
            For $j = $iArrayColStart To $iArrayColSize - 1
                If $iDims = 2 Then
                    $aTransposed[$j - $iArrayColStart][$i - $iArrayRowStart] = $vValue[$i][$j]
                Else
                    $aTransposed[$j - $iArrayColStart][$i - $iArrayRowStart] = $vValue[$i]
                EndIf
            Next
        Next
        If $bValue = 1 Then
            $oWorksheet.Range($oWorksheet.Cells($vRangeOrRow, $iColumn), $oWorksheet.Cells($iLastRow, $iLastCol)).Value = $aTransposed
        Else
            $oWorksheet.Range($oWorksheet.Cells($vRangeOrRow, $iColumn), $oWorksheet.Cells($iLastRow, $iLastCol)).Formula = $aTransposed
        EndIf
    EndIf
    If @error Then Return SetError(6, @error, 0)
    Return 1
EndFunc   ;==>_Excel_RangeWrite


; #FUNCTION# ====================================================================================================================
; Name...........: _Excel_ErrorNotify
; Description ...: Sets or queries the debug level.
; Syntax.........: _Excel_ErrorNotify($iDebug[, $sDebugFile = @ScriptDir & "\Excel_Debug.txt"])
; Parameters ....: $iDebug   - Debug level. Allowed values are:
;                 |-1 - Return the current settings
;                 |0  - Disable debugging
;                 |1  - Enable debugging. Output the debug info to the console
;                 |2  - Enable Debugging. Output the debug info to a MsgBox
;                 |3  - Enable Debugging. Output the debug info to a file defined by $sDebugFile
;                 $sDebugFile - Optional: File to write the debugging info to if $iDebug = 3 (Default = @ScriptDir & "\Excel_Debug.txt")
; Return values .: Success (for $iDebug => 0) - 1, sets @extended to:
;                 |0 - The COM error handler for this UDF was already active
;                 |1 - A COM error handler has been initialized for this UDF
;                 Success (for $iDebug = -1) - one based one-dimensional array with the following elements:
;                 |1 - Debug level. Value from 0 to 3. Check parameter $iDebug for details
;                 |2 - Debug file. File to write the debugging info to as defined by parameter $sDebugFile
;                 |3 - True if the COM error handler has been defined for this UDF. False if debugging is set off or a COM error handler was already defined
;                 Failure - 0, sets @error to:
;                 |1 - $iDebug is not an integer or < -1 or > 3
;                 |2 - Installation of the custom error handler failed. @extended is set to the error code returned by ObjEvent
;                 |3 - COM error handler already set to another function
; Author ........: water
; Modified.......:
; Remarks .......:
; Related .......:
; Link ..........:
; Example .......: Yes
; ===============================================================================================================================
Func _Excel_ErrorNotify($iDebug, $sDebugFile = "")

    If Not IsInt($iDebug) Or $iDebug < -1 Or $iDebug > 3 Then Return SetError(1, 0, 0)
    If $sDebugFile = "" Then $sDebugFile = @ScriptDir & "\Excel_Debug.txt"
    Switch $iDebug
        Case -1
            Local $avDebug[4] = [3]
            $avDebug[1] = $__iExcel_Debug
            $avDebug[2] = $__sExcel_DebugFile
            $avDebug[3] = IsObj($__oExcel_Error)
            Return $avDebug
        Case 0
            $__iExcel_Debug = 0
            $__sExcel_DebugFile = ""
            $__oExcel_Error = 0
        Case Else
            $__iExcel_Debug = $iDebug
            $__sExcel_DebugFile = $sDebugFile
            ; A COM error handler will be initialized only if one does not exist
            If ObjEvent("AutoIt.Error") = "" Then
                $__oExcel_Error = ObjEvent("AutoIt.Error", "__Excel_ErrorHandler") ; Creates a custom error handler
                If @error <> 0 Then Return SetError(2, @error, 0)
                Return SetError(0, 1, 1)
            ElseIf ObjEvent("AutoIt.Error") = "__Excel_ErrorHandler" Then
                Return SetError(0, 0, 1) ; COM error handler already set by a call to this function
            Else
                Return SetError(3, 0, 0) ; COM error handler already set to another function
            EndIf
    EndSwitch
    Return 1

EndFunc   ;==>_Excel_ErrorNotify

; #INTERNAL_USE_ONLY#============================================================================================================
; Name...........: __Excel_CloseOnQuit
; Description ...: Sets or returns the state used to determine if the Excel application can be closed by _Excel_Close.
; Syntax.........: __Excel_CloseOnQuit($bNewState = Default)
; Parameters ....: $bNewState - True if the Excel application was started by function _Excel_Open
; Return values .: Success - Current state. Can be either True (Excel will be closed by _Excel_Close) or False (Excel will not be closed by _Excel_Close)
; Author ........: Valik
; Modified ......:
; Remarks .......:
; Related .......:
; Link ..........:
; Example .......:
; ===============================================================================================================================
Func __Excel_CloseOnQuit($bNewState = Default)

    Static $bState = False
    If IsBool($bNewState) Then $bState = $bNewState
    Return $bState

EndFunc   ;==>__Excel_CloseOnQuit

; #INTERNAL_USE_ONLY#============================================================================================================
; Name ..........: __Excel_ErrorHandler
; Description ...: Called if an ObjEvent error occurs.
; Syntax.........: __Excel_ErrorHandler()
; Parameters ....: None
; Return values .: @error is set to the COM error by AutoIt
; Author ........: water
; Modified ......:
; Remarks .......:
; Related .......:
; Link ..........:
; Example .......:
; ===============================================================================================================================
Func __Excel_ErrorHandler()

    Local $bHexNumber = Hex($__oExcel_Error.number, 8)
    Local $sError = "COM Error Encountered in " & @ScriptName & @CRLF & _
            "@AutoItVersion = " & @AutoItVersion & @CRLF & _
            "@AutoItX64 = " & @AutoItX64 & @CRLF & _
            "@Compiled = " & @Compiled & @CRLF & _
            "@OSArch = " & @OSArch & @CRLF & _
            "@OSVersion = " & @OSVersion & @CRLF & _
            "Scriptline = " & $__oExcel_Error.scriptline & @CRLF & _
            "NumberHex = " & $bHexNumber & @CRLF & _
            "Number = " & $__oExcel_Error.number & @CRLF & _
            "WinDescription = " & StringStripWS($__oExcel_Error.WinDescription, 2) & @CRLF & _
            "Description = " & StringStripWS($__oExcel_Error.description, 2) & @CRLF & _
            "Source = " & $__oExcel_Error.Source & @CRLF & _
            "HelpFile = " & $__oExcel_Error.HelpFile & @CRLF & _
            "HelpContext = " & $__oExcel_Error.HelpContext & @CRLF & _
            "LastDllError = " & $__oExcel_Error.LastDllError
    If $__iExcel_Debug > 0 Then
        If $__iExcel_Debug = 1 Then ConsoleWrite($sError & @CRLF & "========================================================" & @CRLF)
        If $__iExcel_Debug = 2 Then MsgBox(64, "Outlook UDF - Debug Info", $sError)
        If $__iExcel_Debug = 3 Then FileWrite($__sExcel_DebugFile, @YEAR & "." & @MON & "." & @MDAY & " " & @HOUR & ":" & @MIN & ":" & @SEC & " " & @CRLF & _
                "-------------------" & @CRLF & $sError & @CRLF & "========================================================" & @CRLF)
    EndIf

EndFunc   ;==>__Excel_ErrorHandler

Edited January 27, 2013 by nitekram

[water]

That's the reason why you need to run the UDF with the latest beta

[nitekram]

I do realize that, but that function is never called with my script, so do not have that error - in fact I had it commented out the first time I ran your test script, and did so again, and was able to run the test.

Msgbox comes up "Book 1 opened"

Msgbox comes up "Book 2 opened"

Msgbox comes up excel set to visible = true

after the last msgbox - both files are opened, but are mimized

[water]

I do realize that, but that function is never called with my script, so do not have that error - in fact I had it commented out the first time I ran your test script, and did so again, and was able to run the test.

Msgbox comes up "Book 1 opened"

Msgbox comes up "Book 2 opened"

Msgbox comes up excel set to visible = true

after the last msgbox - both files are opened, but are mimized

The additional parameter in ObjGet isn't the only change made to AutoIt after 3.3.8.1.

COM handling has changed a lot. If you have a COM error in 3.3.8.1 your script crashes, in 3.3.9.* @error is set and the script continues.

[water]

You can maximize the window using:

Global Const $xlMaximized = -4137
$oExcel.Windows(1).WindowState = $xlMaximized

See the XlWindowState Enumeration.

[water]

Msgbox comes up "Book 1 opened"

Msgbox comes up "Book 2 opened"

Msgbox comes up excel set to visible = true

after the last msgbox - both files are opened, but are mimized

Here "Book 2" is displayed maximized, "Book 1" remains minimized.

Excel 2010, Windows 7 64 bit.

[water]

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 been looking for a copy on my W7 computer - couldn't find one.

Can you please post the name of the Excel file you open and the full path + name of the copy created?

[nitekram]

Not going to be at work for a couple of days (shift work).

This is what I can recall:

I opened the folder called - @usernameMy Documents and set it so I could see it.and left it opened

with script - i put a msgbox after each command so that I could tell where and when the copy was created

Opened original excel file off a share drive, hidden

Created an array from reading the excel file with _Excel_RangeRead

After using the command _Excel_BookClose

I was able to view the creation of the copied excel file

as I said ealier, did not know about the default parameter saving a copy, but I assume you are interested in the size difference?

[water]

I would guess that after _Excel_BookClose the original file from the shared drive should be updated and get a new timestamp. Can you verify this?

Do you have write access on the shared drive?

I can't explain the size difference at them moment.

[nitekram]

Yes the shared drive file has a new times stamp as well as the copied one that is local - the local is about a two minute difference from the shared drive time stamp.

Yes, I have write access to shared drive.

[water]

Did I already ask you which version of Excel you run?

[nitekram]

Office 2010

[water]

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

So the original file is updated by _Excel_BookClose and gets a new timestamp (e.g. 09:15:20), the copy in folder "My Documents" has a timestamp 2 minute later (e.g. 09:17:20)?

Does your script exit after _Excel_BookClose or does it run for this 2 minutes or longer?

Can you provide a small reproducer script that shows this strange behaviour?

[nitekram]

This reproduces the strange behavior - remember when opening excel file, I am using a shared drive. I have not tested on any other conditions.

The time stamp is no longer changing on the original file - so I can not check out the time stamp difference. I am using your newest Alpha, but am not using the BETA version of AutoIt.

expandcollapse popup

#cs
;Global Const $xlSheetVisible = -1 ; Displays the sheet5
; #VARIABLES# ===================================================================================================================
Global $__iExcel_Debug = 3 ; Debug level. 0 = no debug information, 1 = Debug info to console, 2 = Debug info to MsgBox, 3 = Debug Info to File
Global $__sExcel_DebugFile = @ScriptDir & "\Excel_Debug.txt" ; Debug file if $__iExcel_Debug is set to 3
Global $__oExcel_Error ; COM Error handler
; ===============================================================================================================================
#ce

#include <array.au3>
#include "Excel Rewrite.au3"

Opt("TrayMenuMode", 3)

Global $LookUpItem = TrayCreateItem("Check New Alerts for GCARS")
Global $exititem = TrayCreateItem("Exit")
TraySetState()


$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)

;$oRange = $oWorkbook.Range($oOExcel.Cells(1, 1), $oOExcel.Cells($oWorkbook.UsedRange.Rows.Count, $oWorkbook.UsedRange.Columns.Count))

;MsgBox('','',$oRange)

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)

MsgBox('','','finished gathering data ==>> no errors', 1)
;_ArrayDisplay($aArrayExcel)

while 1
$Tray_msg = TrayGetMsg()
Select
Case $Tray_msg = $LookUpItem
    _ArrayDisplay($aArrayExcel)
    Case $Tray_msg = $exititem
            Exit
EndSelect
WEnd


#cs


; #INTERNAL_USE_ONLY#============================================================================================================
;__Excel_CloseOnQuit
; ===============================================================================================================================

;_Excel_ErrorNotify(2)

; #FUNCTION# ====================================================================================================================
; Name...........: _Excel_Open
; Description ...: Connects to an existing Excel instance or creates a new Excel Application Object.
; Syntax.........: _Excel_Open([$bVisible = True[, $bDisplayAlerts = False[, $bScreenUpdating = True[, $bForceNew = False]]]])
; Parameters ....: $bVisible        - Optional: True specifies that the application will be visible (default = True)
;                 $bDisplayAlerts  - Optional: False suppresses all prompts and alert messages while opening the workbook (default = False)
;                 $bScreenUpdating - Optional: False suppresses screen updating to speed up your script (default = True)
;                 $bForceNew       - Optional: True forces to create a new Excel instance even if there is already a running instance (default = False)
; Return values .: Success - Returns the Excel application object. Sets @extended to:
;                 |0 - Excel was already running
;                 |1 - Excel was not running or $bForceNew was set to True. Excel has been started by this function
;                 Failure - Returns 0 and sets @error
;                 |1 - Error returned by ObjCreate. @extended is set to the error code returned by ObjCreate
; Author ........: water
; Modified ......:
; Remarks .......: If $bDisplayAlerts is set to False and a message requires a response, Excel chooses the default response.
; Related .......:
; Link ..........:
; Example .......: Yes
; ===============================================================================================================================
Func _Excel_Open($bVisible = Default, $bDisplayAlerts = Default, $bScreenUpdating = Default, $bForceNew = Default)
    Local $oExcel, $bApplCloseOnQuit = False
    If $bVisible = Default Then $bVisible = True
    If $bDisplayAlerts = Default Then $bDisplayAlerts = False
    If $bScreenUpdating = Default Then $bScreenUpdating = True
    If $bForceNew = Default Then $bForceNew = False
    If Not $bForceNew Then $oExcel = ObjGet("", "Excel.Application")
    If $bForceNew Or @error Then
        $oExcel = ObjCreate("Excel.Application")
        If @error Or Not IsObj($oExcel) Then Return SetError(1, @error, 0)
        $bApplCloseOnQuit = True
    EndIf
    __Excel_CloseOnQuit($bApplCloseOnQuit)
    $oExcel.Visible = $bVisible
    $oExcel.DisplayAlerts = $bDisplayAlerts
    $oExcel.ScreenUpdating = $bScreenUpdating
    Return SetError(0, $bApplCloseOnQuit, $oExcel)
EndFunc   ;==>_Excel_Open

; #FUNCTION# ====================================================================================================================
; Name...........: _Excel_Close
; Description ...: Closes all worksheets and the instance of the Excel application.
; Syntax.........: _Excel_Close($oExcel[, $iSaveChanges = True[, $bForceClose = False]])
; Parameters ....: $oExcel     - Excel application object returned by a preceding call to _Excel_Open
;                 $bSaveChanges - Optional: Specifies whether changed worksheets should be saved before closing (default = True)
;                 $bForceClose  - Optional: If True the Excel application is closed even when it was not started by _Excel_Open (default = False)
; Return values .: Success - Returns 1
;                 Failure - Returns 0 and sets @error
;                 |1 - $oExcel is not an object
;                 |2 - Error returned by method Application.Quit. @extended is set to the COM error code
;                 |3 - Error returned by method Application.Save. @extended is set to the COM error code
; Author ........: water
; Modified ......:
; Remarks .......: If Excel was started by _Excel_Open then _Excel_Close closes all workbooks
;                 (even those opened manually by the user after _Excel_Open for this instance) and closes the specified Excel instance.
;                 If _Excel_Open connected to an already running instance of Excel then you have to set $bForceClose to True to do the same.
; Related .......:
; Link ..........:
; Example .......: Yes
; ===============================================================================================================================
Func _Excel_Close(ByRef $oExcel, $bSaveChanges = Default, $bForceClose = Default)
    If $bSaveChanges = Default Then $bSaveChanges = True
    If $bForceClose = Default Then $bForceClose = False
    If Not IsObj($oExcel) Then Return SetError(1, 0, 0)
    If $bSaveChanges Then
        For $oWorkbook In $oExcel.Workbooks
            If Not $oWorkbook.Saved Then
                $oWorkbook.Save()
                If @error Then Return SetError(3, @error, 0)
            EndIf
        Next
    EndIf
    If __Excel_CloseOnQuit() Or $bForceClose Then
        $oExcel.Quit()
        $oExcel = 0
        If @error Then Return SetError(2, @error, 0)
    EndIf
    __Excel_CloseOnQuit(False)
    Return 1
EndFunc   ;==>_Excel_Close

#cs
;;;;;;ERROR: ObjGet() [built-in] called with wrong number of args.
; #FUNCTION# ====================================================================================================================
; Name...........: _Excel_BookAttach
; Description ...: Attach to the first instance of a workbook where the search string matches based on the selected mode.
; Syntax.........: _Excel_BookAttach($sString[, $sMode = "FilePath"])
; Parameters ....: $sString  - String to search for
;                 $sMode    - Optional: specifies search mode:
;                 |FileName - Name of the open workbook
;                 |FilePath - Full path to the open workbook (default)
;                 |Title    - Title of the Excel window
; Return values .: Success - Returns the Excel workbook object
;                 Failure - Returns 0 and sets @error:
;                 |1 - An error occurred or $sString can't be found in any of the open workbooks. @extended is set to the COM error code
;                 |2 - $sMode is invalid
; Author ........: Bob Anthony (big_daddy)
; Modified.......: water
; Remarks .......: All instances of Excel are searched
; Related .......: _Excel_BookNew, _Excel_BookOpen
; Link ..........:
; Example .......: Yes
; ===============================================================================================================================
Func _Excel_BookAttach($sString, $sMode = Default)
    Local $oWorkbook, $iCount = 0, $sCLSID_Workbook = "{00020819-0000-0000-C000-000000000046}"
    If $sMode = Default Then $sMode = "FilePath"
    ; Loop through all workbooks
    While True
        $oWorkbook = ObjGet("", $sCLSID_Workbook, $iCount + 1)
        If @error Then Return SetError(1, @error, 0)
        Switch $sMode
            Case "filename"
                If $oWorkbook.Name = $sString Then Return $oWorkbook
            Case "filepath"
                If $oWorkbook.FullName = $sString Then Return $oWorkbook
            Case "title"
                If ($oWorkbook.Application.Caption) = $sString Then Return $oWorkbook
            Case Else
                Return SetError(2, 0, 0)
        EndSwitch
        $iCount += 1
    WEnd
EndFunc   ;==>_Excel_BookAttach
#ce


; #FUNCTION# ====================================================================================================================
; Name...........: _Excel_BookClose
; Description ...: Closes the specified workbook.
; Syntax.........: _Excel_BookClose($oExcel, $oWorkbook[, $bSave = True])
; Parameters ....: $oExcel  - Excel application object
;                 $oWorkbook - Excel workbook object
;                 $bSave     - If True the workbook will be saved before closing (default = True)
; Return values .: Success - Returns 1
;                 Failure - Returns 0 and sets @error:
;                 |1 - $oExcel is not an object
;                 |2 - $oWorkbook is not an object
;                 |3 - Error occurred when saving the workbook. @extended is set to the error code returned by the Save method
;                 |4 - Error occurred when closing the workbook. @extended is set to the error code returned by the Close method
; Author ........: SEO <locodarwin at yahoo dot com>
; Modified.......: 07/17/2008 by bid_daddy; litlmike, water
; Remarks .......: None
; Related .......:
; Link ..........:
; Example .......: Yes
; ===============================================================================================================================
Func _Excel_BookClose($oExcel, ByRef $oWorkbook, $bSave = Default)
    If Not IsObj($oExcel) Then Return SetError(1, 0, 0)
    If Not IsObj($oWorkbook) Then Return SetError(2, 0, 0)
    If $bSave = Default Then $bSave = True
    If $bSave Then
        $oWorkbook.Save()
        If @error Then Return SetError(3, @error, 0)
    EndIf
    $oWorkbook.Close()
    If @error Then Return SetError(4, @error, 0)
    $oWorkbook = 0
    Return 1
EndFunc   ;==>_Excel_BookClose

; #FUNCTION# ====================================================================================================================
; Name...........: _Excel_BookNew
; Description ...: Creates new workbook and returns its object identifier.
; Syntax.........: _Excel_BookNew($oExcel[, $iSheets = Default])
; Parameters ....: $oExcel   - Excel application object where you want to create the new workbook.
;                 $iSheets  - Optional: Number of sheets to create in the new workbook (default = Excel default value)
; Return values .: Success - Returns new Excel application object identifier
;                 Failure - Returns 0 and sets @error:
;                 |1 - $oExcel is not an object
;                 |2 - Error setting SheetsInNewWorkbook to $iSheets. @extended is set to the COM error code
;                 |3 - Error returned by method Workbooks.Add. @extended is set to the COM error code
; Author ........: SEO <locodarwin at yahoo dot com>
; Modified.......: litlmike, water
; Remarks .......:
; Related .......: _Excel_BookAttach
; Link ..........:
; Example .......: Yes
; ===============================================================================================================================
Func _Excel_BookNew($oExcel, $iSheets = Default)
    If Not IsObj($oExcel) Then Return SetError(1, 0, 0)
    With $oExcel
        If $iSheets <> Default Then
            Local $iSheetsBackup = .SheetsInNewWorkbook = $iSheets
            .SheetsInNewWorkbook = $iSheets
            If @error Then Return SetError(2, @error, 0)
        EndIf
        Local $oWorkbook = .Workbooks.Add()
        If @error Then Return SetError(3, @error, 0)
        .ActiveWorkbook.Sheets(1).Select()
        If $iSheets <> Default Then .SheetsInNewWorkbook = $iSheetsBackup
    EndWith
    Return $oWorkbook
EndFunc   ;==>_Excel_BookNew


;;;;;;;;;;WARNING: $xlSheetVisible: possibly used before declaration.
; #FUNCTION# ====================================================================================================================
; Name...........: _Excel_BookOpen
; Description ...: Opens an existing workbook and returns its object identifier.
; Syntax.........: _Excel_BookOpen($oExcel, $sFilePath[, $bReadOnly = False[, $sPassword = Default[, $sWritePassword = Default]]])
; Parameters ....: $oExcel       - Excel application object where you want to open the new workbook
;                 $sFilePath      - Path and filename of the file to be opened
;                 $bReadOnly      - Optional: Flag, whether to open the workbook as read-only (True or False) (default = False)
;                 $sPassword      - Optional: The password that was used to read-protect the workbook, if any (default is none)
;                 $sWritePassword - Optional: 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 - $oExcel is not an object
;                 |2 - Specified $sFilePatch 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 .......:
; Related .......: _Excel_BookAttach, _Excel_BookOpenText
; Link ..........:
; Example .......: Yes
; ===============================================================================================================================
Func _Excel_BookOpen($oExcel, $sFilePath, $bReadOnly = Default, $sPassword = Default, $sWritePassword = Default)
    Local $oWorkbook
    If Not FileExists($sFilePath) Then Return SetError(2, 0, 0)
    If Not IsObj($oExcel) Then Return SetError(1, @error, 0)
    If $bReadOnly = Default Then $bReadOnly = False
    $oWorkbook = $oExcel.Workbooks.Open($sFilePath, Default, $bReadOnly, Default, $sPassword, $sWritePassword)
    If @error Then Return SetError(3, @error, 0)
    ; If a readwrite workbook was opened readonly then return an error
    If $bReadOnly = False And $oWorkbook.Readonly Then
        $oWorkbook.Close(False)
        $oWorkbook = 0
        Return SetError(4, 0, 0)
    EndIf
    ; Select the first visible worksheet
    For $i = 1 To $oWorkbook.Sheets.Count
        If $oWorkbook.Sheets($i).Visible = $xlSheetVisible Then
            $oWorkbook.Sheets($i).Select()
            ExitLoop
        EndIf
    Next
    Return $oWorkbook
EndFunc   ;==>_Excel_BookOpen

; #FUNCTION# ====================================================================================================================
; Name...........: _Excel_RangeRead
; Description ...: Reads the value, formula or displayed text from a cell or range of cells of the specified workbook and worksheet.
; Syntax.........: _Excel_RangeRead($oExcel, $oWorkbook, $oWorksheet, $vRangeOrRow[, $iColumn = 1[,  $iReturn = 1]])
; Parameters ....: $oExcel    - Excel application object
;                 $oWorkbook   - Excel workbook object. If set to Default the active workbook will be used
;                 $oWorksheet  - Excel worksheet object. If set to Default the active sheet will be used
;                 $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:
;                 |1 - Value (default)
;                 |2 - Formula
;                 |3 - The displayed text
; Return values .: Success - Returns the data from the specified cell(s). A string for a cell, an zero-based array for a range of cells.
;                 Failure - Returns 0 and sets @error:
;                 |1 - $oExcel is not an object
;                 |2 - $oWorkbook is not an object
;                 |3 - $oWorksheet is not an object
;                 |4 - Specified parameter is incorrect. Sets @extended:
;                 | 0 - Row out of valid range
;                 | 1 - Column out of valid range
;                 |5 - Error occurred when reading data. @extended is set to the error code returned when accessing the Value property
;                 |6 - Parameter $iReturn is invalid. Has to be > 1 and < 3
; Author ........: SEO <locodarwin at yahoo dot com>
; Modified.......: litlmike, water
; Remarks .......:
; Related .......:
; Link ..........:
; Example .......: Yes
; ===============================================================================================================================
Func _Excel_RangeRead($oExcel, $oWorkbook, $oWorksheet, $vRangeOrRow, $iColumn = Default, $iReturn = Default)
    If Not IsObj($oExcel) Then Return SetError(1, 0, 0)
    If $oWorkbook = Default Then $oWorkbook = $oExcel.ActiveWorkbook
    If Not IsObj($oWorkbook) Then Return SetError(2, 0, 0)
    If $oWorksheet = Default Then $oWorksheet = $oWorkbook.Activesheet
    If Not IsObj($oWorksheet) Then Return SetError(3, 0, 0)
    If $iColumn = Default Then $iColumn = 1
    If $iReturn = Default Then $iReturn = 1
    If $iReturn < 1 Or $iReturn > 3 Then Return SetError(6, 0, 0)
    Local $vResult
    If Not StringRegExp($vRangeOrRow, "[A-Z,a-z]", 0) Then
        If $vRangeOrRow < 1 Then Return SetError(4, 0, 0)
        If $iColumn < 1 Then Return SetError(4, 1, 0)
        If $iReturn = 1 Then
            $vResult = $oWorksheet.Cells($vRangeOrRow, $iColumn).Value
        ElseIf $iReturn = 2 Then
            $vResult = $oWorksheet.Cells($vRangeOrRow, $iColumn).Formula
        Else
            $vResult = $oWorksheet.Cells($vRangeOrRow, $iColumn).Text
        EndIf
        If @error Then Return SetError(5, @error, 0)
    Else
        If $iReturn = 1 Then
            $vResult = $oExcel.Transpose($oWorksheet.Range($vRangeOrRow).Value)
        ElseIf $iReturn = 2 Then
            $vResult = $oExcel.Transpose($oWorksheet.Range($vRangeOrRow).Formula)
        Else
            $vResult = $oExcel.Transpose($oWorksheet.Range($vRangeOrRow).Text)
        EndIf
        If @error Then Return SetError(5, @error, 0)
    EndIf
    Return $vResult
EndFunc   ;==>_Excel_RangeRead

; #FUNCTION# ====================================================================================================================
; Name...........: _Excel_ErrorNotify
; Description ...: Sets or queries the debug level.
; Syntax.........: _Excel_ErrorNotify($iDebug[, $sDebugFile = @ScriptDir & "\Excel_Debug.txt"])
; Parameters ....: $iDebug   - Debug level. Allowed values are:
;                 |-1 - Return the current settings
;                 |0  - Disable debugging
;                 |1  - Enable debugging. Output the debug info to the console
;                 |2  - Enable Debugging. Output the debug info to a MsgBox
;                 |3  - Enable Debugging. Output the debug info to a file defined by $sDebugFile
;                 $sDebugFile - Optional: File to write the debugging info to if $iDebug = 3 (Default = @ScriptDir & "\Excel_Debug.txt")
; Return values .: Success (for $iDebug => 0) - 1, sets @extended to:
;                 |0 - The COM error handler for this UDF was already active
;                 |1 - A COM error handler has been initialized for this UDF
;                 Success (for $iDebug = -1) - one based one-dimensional array with the following elements:
;                 |1 - Debug level. Value from 0 to 3. Check parameter $iDebug for details
;                 |2 - Debug file. File to write the debugging info to as defined by parameter $sDebugFile
;                 |3 - True if the COM error handler has been defined for this UDF. False if debugging is set off or a COM error handler was already defined
;                 Failure - 0, sets @error to:
;                 |1 - $iDebug is not an integer or < -1 or > 3
;                 |2 - Installation of the custom error handler failed. @extended is set to the error code returned by ObjEvent
;                 |3 - COM error handler already set to another function
; Author ........: water
; Modified.......:
; Remarks .......:
; Related .......:
; Link ..........:
; Example .......: Yes
; ===============================================================================================================================
Func _Excel_ErrorNotify($iDebug, $sDebugFile = "")

    If Not IsInt($iDebug) Or $iDebug < -1 Or $iDebug > 3 Then Return SetError(1, 0, 0)
    If $sDebugFile = "" Then $sDebugFile = @ScriptDir & "\Excel_Debug.txt"
    Switch $iDebug
        Case -1
            Local $avDebug[4] = [3]
            $avDebug[1] = $__iExcel_Debug
            $avDebug[2] = $__sExcel_DebugFile
            $avDebug[3] = IsObj($__oExcel_Error)
            Return $avDebug
        Case 0
            $__iExcel_Debug = 0
            $__sExcel_DebugFile = ""
            $__oExcel_Error = 0
        Case Else
            $__iExcel_Debug = $iDebug
            $__sExcel_DebugFile = $sDebugFile
            ; A COM error handler will be initialized only if one does not exist
            If ObjEvent("AutoIt.Error") = "" Then
                $__oExcel_Error = ObjEvent("AutoIt.Error", "__Excel_ErrorHandler") ; Creates a custom error handler
                If @error <> 0 Then Return SetError(2, @error, 0)
                Return SetError(0, 1, 1)
            ElseIf ObjEvent("AutoIt.Error") = "__Excel_ErrorHandler" Then
                Return SetError(0, 0, 1) ; COM error handler already set by a call to this function
            Else
                Return SetError(3, 0, 0) ; COM error handler already set to another function
            EndIf
    EndSwitch
    Return 1

EndFunc   ;==>_Excel_ErrorNotify

; #INTERNAL_USE_ONLY#============================================================================================================
; Name...........: __Excel_CloseOnQuit
; Description ...: Sets or returns the state used to determine if the Excel application can be closed by _Excel_Close.
; Syntax.........: __Excel_CloseOnQuit($bNewState = Default)
; Parameters ....: $bNewState - True if the Excel application was started by function _Excel_Open
; Return values .: Success - Current state. Can be either True (Excel will be closed by _Excel_Close) or False (Excel will not be closed by _Excel_Close)
; Author ........: Valik
; Modified ......:
; Remarks .......:
; Related .......:
; Link ..........:
; Example .......:
; ===============================================================================================================================
Func __Excel_CloseOnQuit($bNewState = Default)

    Static $bState = False
    If IsBool($bNewState) Then $bState = $bNewState
    Return $bState

EndFunc   ;==>__Excel_CloseOnQuit

; #INTERNAL_USE_ONLY#============================================================================================================
; Name ..........: __Excel_ErrorHandler
; Description ...: Called if an ObjEvent error occurs.
; Syntax.........: __Excel_ErrorHandler()
; Parameters ....: None
; Return values .: @error is set to the COM error by AutoIt
; Author ........: water
; Modified ......:
; Remarks .......:
; Related .......:
; Link ..........:
; Example .......:
; ===============================================================================================================================
Func __Excel_ErrorHandler()

    Local $bHexNumber = Hex($__oExcel_Error.number, 8)
    Local $sError = "COM Error Encountered in " & @ScriptName & @CRLF & _
            "@AutoItVersion = " & @AutoItVersion & @CRLF & _
            "@AutoItX64 = " & @AutoItX64 & @CRLF & _
            "@Compiled = " & @Compiled & @CRLF & _
            "@OSArch = " & @OSArch & @CRLF & _
            "@OSVersion = " & @OSVersion & @CRLF & _
            "Scriptline = " & $__oExcel_Error.scriptline & @CRLF & _
            "NumberHex = " & $bHexNumber & @CRLF & _
            "Number = " & $__oExcel_Error.number & @CRLF & _
            "WinDescription = " & StringStripWS($__oExcel_Error.WinDescription, 2) & @CRLF & _
            "Description = " & StringStripWS($__oExcel_Error.description, 2) & @CRLF & _
            "Source = " & $__oExcel_Error.Source & @CRLF & _
            "HelpFile = " & $__oExcel_Error.HelpFile & @CRLF & _
            "HelpContext = " & $__oExcel_Error.HelpContext & @CRLF & _
            "LastDllError = " & $__oExcel_Error.LastDllError
    If $__iExcel_Debug > 0 Then
        If $__iExcel_Debug = 1 Then ConsoleWrite($sError & @CRLF & "========================================================" & @CRLF)
        If $__iExcel_Debug = 2 Then MsgBox(64, "Outlook UDF - Debug Info", $sError)
        If $__iExcel_Debug = 3 Then FileWrite($__sExcel_DebugFile, @YEAR & "." & @MON & "." & @MDAY & " " & @HOUR & ":" & @MIN & ":" & @SEC & " " & @CRLF & _
                "-------------------" & @CRLF & $sError & @CRLF & "========================================================" & @CRLF)
    EndIf

EndFunc   ;==>__Excel_ErrorHandler

#ce

Edited February 4, 2013 by nitekram

[water]

Sorry, but that's not a reproducer script. To much code unrelated to the problem.

I removed everything unnecessary. Can you please test the following script and tell me if the problem persists?

#include "Excel Rewrite.au3"

$ExcelFile = FileOpenDialog('Look GCAR report spreadsheet', 'c:\temp', "Excel (*.xls;*.xlsx)", 1 + 2)
Global $oExcel = _Excel_Open(False)
If @error <> 0 Then Exit MsgBox(16, "Excel UDF: _Excel_Open " & $ExcelFile, "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
Global $oWorkbook = _Excel_BookOpen($oExcel, $ExcelFile, True)
If @error Then
    MsgBox(16, "Excel UDF: _Excel_BookOpen " & $ExcelFile, "Error opening '" & $ExcelFile & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
Else
    $aArrayExcel = _Excel_RangeRead($oExcel, $oWorkbook, Default, "A7:BC750")
    If @error Then MsgBox(16, "Excel UDF: _Excel_RangeRead " & $ExcelFile, "Error reading '" & $ExcelFile & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
EndIf    
_Excel_BookClose($oExcel, $oWorkbook)
_Excel_Close($oExcel)

[nitekram]

OK, ran your code as posted - same issue. at the end of script, it made a copy of the excel file - location C:Documents and Settings%username%My Documents - saved with the current time and date on the computer during the running of the script.

The file is not longer being save on the share drive, as it has the original date.

Happens when I run it with F5 or Alt F5

[nitekram]

As stated in another topic, it appears that you need to close all instances of files in Scite to be able to run it from another location, otherwise it reverts to the first installed version.

So I ran this with BETA of AutoIt and still got the same results

>"C:\Program Files\AutoIt3\SciTE\AutoIt3Wrapper\AutoIt3Wrapper.exe" /run /beta /ErrorStdOut /in "P:Autoitfileswaters reproducer script.au3" /UserParams

+>13:13:04 Starting AutoIt3Wrapper v.2.1.0.33 Environment(Language:0409 Keyboard:00000409 OS:WIN_XP/Service Pack 3 CPU:X64 OS:X86)

>Running AU3Check (3.3.9.4) from:C:Program FilesAutoIt3beta

+>13:13:04 AU3Check ended.rc:0

>Running:(3.3.9.4):C:\Program Files\AutoIt3\Beta\autoit3.exe "P:Autoitfileswaters reproducer script.au3"

--> Press Ctrl+Alt+F5 to Restart or Ctrl+Break to Stop

+>13:13:21 AutoIT3.exe ended.rc:0

>Exit code: 0 Time: 18.690

[water]

Nitekram,

I tested high and low but couldn't recreate your problem.

Could this be a strange behaviour (aka bug) of Excel when running on Windows XP?