Progress bar while reading array?

[water]

Give this latest version of _Excel_RangeRead a try. If you do not specify a range then all used cells are being read:

expandcollapse popup

; #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 = Default[, $oWorksheet = Default[, $vRange = Default[, $iReturn = 1]]]])
; Parameters ....: $oExcel   - Excel application object
;                 $oWorkbook  - Optional: Excel workbook object. If set to Default the active workbook will be used
;                 $oWorksheet - Optional: Excel worksheet object. If set to Default the active sheet will be used
;                 $vRange    - Optional: Either a range object or an A1 range. If set to Default all used cells will be processed
;                 $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 - Parameter $iReturn is invalid. Has to be > 1 and < 3
;                 |5 - Error occurred when reading data. @extended is set to the error code returned when accessing the Value property
; Author ........: SEO <locodarwin at yahoo dot com>
; Modified.......: litlmike, water
; Remarks .......:
; Related .......:
; Link ..........:
; Example .......: Yes
; ===============================================================================================================================
Func _Excel_RangeRead($oExcel, $oWorkbook = Default, $oWorksheet = Default, $vRange = 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 $vRange = Default Then $vRange = $oWorksheet.Usedrange
    If IsString($vRange) Then $vRange = $oExcel.Range($vRange)
    If Not IsObj($vRange) Then Return SetError(1, 0, 0)
    If $iReturn = Default Then $iReturn = 1
    If $iReturn < 1 Or $iReturn > 3 Then Return SetError(4, 0, 0)
    Local $vResult
    If $iReturn = 1 Then
        $vResult = $oExcel.Transpose($vRange.Value)
    ElseIf $iReturn = 2 Then
        $vResult = $oExcel.Transpose($vRange.Formula)
    Else
        $vResult = $oExcel.Transpose($vRange.Text)
    EndIf
    If @error Then Return SetError(5, @error, 0)
    Return $vResult
EndFunc   ;==>_Excel_RangeRead

[nitekram]

Using the one you just posted with this call -->> _Excel_RangeRead($oOExcel, $oWorkbook, Default, Default)

P:AutoitfilesQtip_NMC.au3 (4827) : ==> The requested action with this object has failed.:

$vResult = $oExcel.Transpose($oWorksheet.Range($vRangeOrRow).Value)

$vResult = $oExcel.Transpose($oWorksheet.Range($vRangeOrRow)^ ERROR

[water]

Do you run the latest beta version of AutoIt? I'm testing with 3.3.9.4 and I have no problem reading a workbook with 869 rows and 40 columns.

Which version do you see in the SciTE output pane?

[nitekram]

AutoItVersion = 3.3.8.1

OSArch = X86

OSBuild = 2600

OSServicePack = Service Pack 3

OSType = WIN32_NT

OSVersion = WIN_XP

[nitekram]

Just to make sure we are on the same page. I have no issues, running your (original) code with hard coded numbers. Nor do I have issues with your NEW code with entering hard coded numbers - here is my call:

_Excel_RangeRead($oOExcel, $oWorkbook, Default, "A6:BC1150")

Edited January 27, 2013 by nitekram

[water]

Do you have one of the latest beta versions of AutoIt installed?

[nitekram]

Not here, I will have to wait until I get home to run with beta. I will test and get back to you and see if that resolves the issue - thanks

[water]

I noticed that I have done many changes to the UDF since posting the latest alpha.

The new UDF is meant to be run with the latest version of AutoIt (latest beta right now) and will not run with 3.3.8.1.

I wonder why you don't get an error with the Alpha version of the UDF because it uses an ObjGet parameter which doesn't exist in 3.3.8.1?

Just noticed that you "removed" all functions not compatible with 3.3.8.1

For you to test with 3.3.9.4 I need to post the latest Alpha of the UDF - because that's what I use to test here.

Edited January 27, 2013 by water

[nitekram]

OK: Here is the code that I am using. These are all your functions, but did not add your other UDF in your above test, as I added that to my production script. I know I should test first, but that function is still in testing phase anyway, and I am the only one able to get to it.

So, for the test, you can send me the file or just post it here?

expandcollapse popup

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
; ===============================================================================================================================

#include <array.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




; #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

[water]

I see.

As the UDF was created with AutoIt > 3.3.8.1 in mind I didn't test it with 3.3.8.1.

Here is the latest version of the UDF. But keep in mind that it is still an early alpha version. I do and will add/remove/change parameters and functionality if I think it is necessary.

Excel Rewrite.au3

Edited January 24, 2016 by water

[nitekram]

Shoot. I am in a bind...need to first get a current version of excel for my home. I have used other applications (office.org) at home to get around with my office projects.

I did download the current BETA though, and installed it. Has been a long time since I played with any BETA AutoIt. Use to use it all the time, and do not know why I stopped.

Let you know when I can test this - thanks again for your help.

[water]

I will check the creating of the copy you described. If it's deleted on exit and if there is a difference between manual or automated processing.

I will keep you informed.

[nitekram]

Here are my results with running the BETA

ALT F5 (run beta)

AutoItVersion = 3.3.8.1

OSArch = X86

OSBuild = 2600

OSServicePack = Service Pack 3

OSType = WIN32_NT

OSVersion = WIN_XP

>"P:AutoitinstallSciteAutoIt3WrapperAutoIt3Wrapper.exe" /run /beta /ErrorStdOut /in "P:Autoitfilesnot working water excel.au3" /UserParams

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

>Running AU3Check (1.54.22.0) from:P:Autoitinstall

+>08:26:06 AU3Check ended.rc:0

>Running:(3.3.8.1):Autoitinstallautoit3.exe "P:Autoitfilesnot working water excel.au3"

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

P:Autoitfilesnot working water excel.au3 (28) : ==> The requested action with this object has failed.:

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

$oRange = $oWorkbook.Range($oOExcel.Cells(1, 1), $oOExcel.Cells($oWorkbook.UsedRange^ ERROR

->08:26:17 AutoIT3.exe ended.rc:1

>Exit code: 1 Time: 13.074

STRANGE - why when I run the @AutoItVersion with the BETA and the Production does it show the same?

F5 run:

AutoItVersion = 3.3.8.1

OSArch = X86

OSBuild = 2600

OSServicePack = Service Pack 3

OSType = WIN32_NT

OSVersion = WIN_XP

[water]

Did I already ask you which version of Excel you run on Windows XP?

[nitekram]

2010

XP pro SP3

[water]

This line was wrong:

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

use

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

But as function _Excel_RangeRead uses the used range as a default there is no need to calculate it yourself.

[nitekram]

I have not tested your newest line from post #36, but here is the results of the use of the DEFAULT for _Excel_RangeRead - I will see what I can do about getting the other line tested today.

>"P:AutoitinstallSciteAutoIt3WrapperAutoIt3Wrapper.exe" /run /beta /ErrorStdOut /in "P:AutoitfilesQtip_NMC.au3" /UserParams

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

>Running AU3Check (1.54.22.0) from:P:Autoitinstall

+>07:47:32 AU3Check ended.rc:0

>Running:(3.3.8.1):Autoitinstallautoit3.exe "P:AutoitfilesQtip_NMC.au3"

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

+>Beginning Import of EXCEL File... TIME = 07:48:15

P:AutoitfilesQtip_NMC.au3 (4835) : ==> The requested action with this object has failed.:

$vResult = $oExcel.Transpose($oWorksheet.Range($vRangeOrRow).Value)

$vResult = $oExcel.Transpose($oWorksheet.Range($vRangeOrRow)^ ERROR

->07:48:16 AutoIT3.exe ended.rc:1

>Exit code: 1 Time: 47.128

[water]

Why do you get

>Running:(3.3.8.1)

when you run the beta version of AutoIt (should be 3.3.9.x)?

[nitekram]

Yea, I asked about that in another post (above) - to run the BETA I use Alt F5 and it runs and gives this - notice the first line says /run /beta

>"P:AutoitinstallSciteAutoIt3WrapperAutoIt3Wrapper.exe" /run /beta /ErrorStdOut /in "P:AutoitfilesQtip_NMC.au3" /UserParams

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

>Running AU3Check (1.54.22.0) from:P:Autoitinstall

+>12:06:13 AU3Check ended.rc:0

>Running:(3.3.8.1):Autoitinstallautoit3.exe "P:AutoitfilesQtip_NMC.au3"

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

+>Beginning Import of EXCEL File... TIME = 12:06:26

P:AutoitfilesQtip_NMC.au3 (4835) : ==> The requested action with this object has failed.:

$vResult = $oExcel.Transpose($oWorksheet.Range($vRangeOrRow).Value)

$vResult = $oExcel.Transpose($oWorksheet.Range($vRangeOrRow)^ ERROR

->12:06:26 AutoIT3.exe ended.rc:1

>Exit code: 1 Time: 16.019

[BrewManNH]

It's not running the Beta, where did you install the beta version?