Progress bar while reading array?

[tempman]

Hi,

I have 5sec of nothing happening on screen while script read 123080 rows of xlsx file.

#include <Excel.au3>

$begin = TimerInit()

$Log= @ScriptDir & "\data.xlsx"
$oExcel = _ExcelBookOpen($Log, 0)

$aArray = _ExcelReadSheetToArray($oExcel, 1, 1, 0, 1)
$Max = $aArray[0][0]

$dif = Round((TimerDiff($begin)),0)/1000
MsgBox(0, "Time of Reading", $dif & " sec" & " for " & $Max & " rows")

_ExcelBookClose($oExcel, 1, 0)
$oExcel.Application.Quit

Exit

How to fill that gap? Any suggestion or idea will be useful.

[water]

Use a faster Excel function.

My has a _Excel_RangeRead function which is much faster. The UDF is still an early alpha version but should give you an idea how it works.

[tempman]

123080 rows, sometimes more, sometimes less, but as I can see you use exact number of rows to read...

_Excel_RangeRead.au3

; Example 1

; Read data from a single cell on the active sheet of the specified workbook

; Example 2

; Read the formulas of a cell range on the active sheet of the specified workbook

Edited January 26, 2013 by tempman

[water]

If you want to read the whole sheet then pass

$oSheet.UsedRange.Rows.Count or $oSheet.UsedRange.Columns.Count

The next version of the UDF will use this as default.

[nitekram]

sorry, i missed this, can we have an example where and how you would use those two and both?

$oSheet.UsedRange.Rows.Count or $oSheet.UsedRange.Columns.Count

[water]

Nitekram,

this post is for me?

[nitekram]

yes, sorry - should have marked it @water. Can you show an example of getting the whole excel sheet. I have tried but keep getting errors.

Edited January 26, 2013 by nitekram

[water]

To define a range of all used cells use something like this. Can't test at the moment - Ubuntu has no Excel

$oRange = $oSheet.Range($oExcel.Cells(1, 1), $oExcel.Cells($oSheet.UsedRange.Rows.Count, $oSheet.UsedRange.Columns.Count))

[nitekram]

P:Autoitfilesnot working water excel.au3 (27) : ==> 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

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]

Will have to test on monday when I'm in my office again.

[nitekram]

Thanks, looking forward to it.

[water]

To modify all used cells use the UsedRange property:

#include <Excel.au3>
$oExcel = _ExcelBookOpen("C:\temp\test.xls")
$oRange = $oExcel.ActiveSheet.UsedRange
$oRange.Font.Name = "Bell MT"

[water]

This is another approach:

#include <Excel.au3>
$oWorkbook = _ExcelBookOpen("C:\temp\test.xls")
$oExcel = $oWorkbook.Parent
$oRange = $oWorkbook.Range($oExcel.Cells(1, 1), $oExcel.Cells($oWorkbook.ActiveSheet.UsedRange.Rows.Count, $oWorkbook.ActiveSheet.UsedRange.Columns.Count))
$oRange.Font.Name = "Bell MT"

[nitekram]

Both appear are coming from the function _Excel_RangeRead()

First approach - get the following error

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

$vResult = $oWorksheet.Cells($vRangeOrRow, $iColumn).Value

$vResult = $oWorksheet.Cells($vRangeOrRow, $iColumn)^ ERROR

Second approach - get the following error

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

$vResult = $oWorksheet.Cells($vRangeOrRow, $iColumn).Value

$vResult = $oWorksheet.Cells($vRangeOrRow, $iColumn)^ ERROR

[water]

Can you post how you call _Excel_RangeRead()?

[nitekram]

This is the way I am calling and works, but does not gather all the data if rows extend past the line number

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

This is how I called it with your code approach:

_Excel_RangeRead($oOExcel, $oWorkbook, Default, $oRange)

[water]

How many rows and columns does your sheet have?

I just tested with 2 rows, 6 columns and the result is correct.

[nitekram]

The file is around 1,000 KB and can be larger, depending on how many rows there are.

869 rows in this version of the file

'AO' columns = 26+15=41 columns - this remains constant, but could be larger, if more fields get entered into the database.

edit spelling

edit - actually, i believe the columns is larger - more like 60?

Edited January 27, 2013 by nitekram

[water]

but does not gather all the data if rows extend past the line number

Don't get what you mean.

If a sheet has 900 rows and you specify a range of "A1:AO1000" you get 900 rows, 1000, less?

[nitekram]

Correct, if I guess at the number of rows and pick a number larger then how many are there. The problem is, I do not know how many rows, as the file comes out every day. I guess I could make up a high enough number that it may not exceed, but thought since there was an option to get exactly the number needed, then I would try and use it rather then - one day the file has more changes then I guessed, I would miss the data I am searching for, like what happened 2 days ago, when my number was 800 and total rows happened to be 864. I then created a new number of 1150. Does that make any more sense?