Jump to content
Sign in to follow this  
tempman

Progress bar while reading array?

Recommended Posts

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.

Share this post


Link to post
Share on other sites
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.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
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 by tempman

Share this post


Link to post
Share on other sites
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.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
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


All by me:

"Sometimes you have to go back to where you started, to get to where you want to go." 

"Everybody catches up with everyone, eventually" 

"As you teach others, you are really teaching yourself."

From my dad

"Do not worry about yesterday, as the only thing that you can control is tomorrow."

 

WindowsError.gif

WIKI | Tabs; | Arrays; | Strings | Wiki Arrays | How to ask a Question | Forum Search | FAQ | Tutorials | Original FAQ | ONLINE HELP | UDF's Wiki | AutoIt PDF

AutoIt Snippets | Multple Guis | Interrupting a running function | Another Send

StringRegExp | StringRegExp Help | RegEXTester | REG TUTOR | Reg TUTOT 2

AutoItSetOption | Macros | AutoIt Snippets | Wrapper | Autoit  Docs

SCITE | SciteJump | BB | MyTopics | Programming | UDFs | AutoIt 123 | UDFs Form | UDF

Learning to script | Tutorials | Documentation | IE.AU3 | Games? | FreeSoftware | Path_Online | Core Language

Programming Tips

Excel Changes

ControlHover.UDF

GDI_Plus

Draw_On_Screen

GDI Basics

GDI_More_Basics

GDI Rotate

GDI Graph

GDI  CheckExistingItems

GDI Trajectory

Replace $ghGDIPDll with $__g_hGDIPDll

DLL 101?

Array via Object

GDI Swimlane

GDI Plus French 101 Site

GDI Examples UEZ

GDI Basic Clock

GDI Detection

Ternary operator

Share this post


Link to post
Share on other sites
water

Nitekram,

this post is for me?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
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 by nitekram

All by me:

"Sometimes you have to go back to where you started, to get to where you want to go." 

"Everybody catches up with everyone, eventually" 

"As you teach others, you are really teaching yourself."

From my dad

"Do not worry about yesterday, as the only thing that you can control is tomorrow."

 

WindowsError.gif

WIKI | Tabs; | Arrays; | Strings | Wiki Arrays | How to ask a Question | Forum Search | FAQ | Tutorials | Original FAQ | ONLINE HELP | UDF's Wiki | AutoIt PDF

AutoIt Snippets | Multple Guis | Interrupting a running function | Another Send

StringRegExp | StringRegExp Help | RegEXTester | REG TUTOR | Reg TUTOT 2

AutoItSetOption | Macros | AutoIt Snippets | Wrapper | Autoit  Docs

SCITE | SciteJump | BB | MyTopics | Programming | UDFs | AutoIt 123 | UDFs Form | UDF

Learning to script | Tutorials | Documentation | IE.AU3 | Games? | FreeSoftware | Path_Online | Core Language

Programming Tips

Excel Changes

ControlHover.UDF

GDI_Plus

Draw_On_Screen

GDI Basics

GDI_More_Basics

GDI Rotate

GDI Graph

GDI  CheckExistingItems

GDI Trajectory

Replace $ghGDIPDll with $__g_hGDIPDll

DLL 101?

Array via Object

GDI Swimlane

GDI Plus French 101 Site

GDI Examples UEZ

GDI Basic Clock

GDI Detection

Ternary operator

Share this post


Link to post
Share on other sites
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))


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
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

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

All by me:

"Sometimes you have to go back to where you started, to get to where you want to go." 

"Everybody catches up with everyone, eventually" 

"As you teach others, you are really teaching yourself."

From my dad

"Do not worry about yesterday, as the only thing that you can control is tomorrow."

 

WindowsError.gif

WIKI | Tabs; | Arrays; | Strings | Wiki Arrays | How to ask a Question | Forum Search | FAQ | Tutorials | Original FAQ | ONLINE HELP | UDF's Wiki | AutoIt PDF

AutoIt Snippets | Multple Guis | Interrupting a running function | Another Send

StringRegExp | StringRegExp Help | RegEXTester | REG TUTOR | Reg TUTOT 2

AutoItSetOption | Macros | AutoIt Snippets | Wrapper | Autoit  Docs

SCITE | SciteJump | BB | MyTopics | Programming | UDFs | AutoIt 123 | UDFs Form | UDF

Learning to script | Tutorials | Documentation | IE.AU3 | Games? | FreeSoftware | Path_Online | Core Language

Programming Tips

Excel Changes

ControlHover.UDF

GDI_Plus

Draw_On_Screen

GDI Basics

GDI_More_Basics

GDI Rotate

GDI Graph

GDI  CheckExistingItems

GDI Trajectory

Replace $ghGDIPDll with $__g_hGDIPDll

DLL 101?

Array via Object

GDI Swimlane

GDI Plus French 101 Site

GDI Examples UEZ

GDI Basic Clock

GDI Detection

Ternary operator

Share this post


Link to post
Share on other sites
water

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
nitekram

Thanks, looking forward to it.


All by me:

"Sometimes you have to go back to where you started, to get to where you want to go." 

"Everybody catches up with everyone, eventually" 

"As you teach others, you are really teaching yourself."

From my dad

"Do not worry about yesterday, as the only thing that you can control is tomorrow."

 

WindowsError.gif

WIKI | Tabs; | Arrays; | Strings | Wiki Arrays | How to ask a Question | Forum Search | FAQ | Tutorials | Original FAQ | ONLINE HELP | UDF's Wiki | AutoIt PDF

AutoIt Snippets | Multple Guis | Interrupting a running function | Another Send

StringRegExp | StringRegExp Help | RegEXTester | REG TUTOR | Reg TUTOT 2

AutoItSetOption | Macros | AutoIt Snippets | Wrapper | Autoit  Docs

SCITE | SciteJump | BB | MyTopics | Programming | UDFs | AutoIt 123 | UDFs Form | UDF

Learning to script | Tutorials | Documentation | IE.AU3 | Games? | FreeSoftware | Path_Online | Core Language

Programming Tips

Excel Changes

ControlHover.UDF

GDI_Plus

Draw_On_Screen

GDI Basics

GDI_More_Basics

GDI Rotate

GDI Graph

GDI  CheckExistingItems

GDI Trajectory

Replace $ghGDIPDll with $__g_hGDIPDll

DLL 101?

Array via Object

GDI Swimlane

GDI Plus French 101 Site

GDI Examples UEZ

GDI Basic Clock

GDI Detection

Ternary operator

Share this post


Link to post
Share on other sites
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"

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
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"

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
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


All by me:

"Sometimes you have to go back to where you started, to get to where you want to go." 

"Everybody catches up with everyone, eventually" 

"As you teach others, you are really teaching yourself."

From my dad

"Do not worry about yesterday, as the only thing that you can control is tomorrow."

 

WindowsError.gif

WIKI | Tabs; | Arrays; | Strings | Wiki Arrays | How to ask a Question | Forum Search | FAQ | Tutorials | Original FAQ | ONLINE HELP | UDF's Wiki | AutoIt PDF

AutoIt Snippets | Multple Guis | Interrupting a running function | Another Send

StringRegExp | StringRegExp Help | RegEXTester | REG TUTOR | Reg TUTOT 2

AutoItSetOption | Macros | AutoIt Snippets | Wrapper | Autoit  Docs

SCITE | SciteJump | BB | MyTopics | Programming | UDFs | AutoIt 123 | UDFs Form | UDF

Learning to script | Tutorials | Documentation | IE.AU3 | Games? | FreeSoftware | Path_Online | Core Language

Programming Tips

Excel Changes

ControlHover.UDF

GDI_Plus

Draw_On_Screen

GDI Basics

GDI_More_Basics

GDI Rotate

GDI Graph

GDI  CheckExistingItems

GDI Trajectory

Replace $ghGDIPDll with $__g_hGDIPDll

DLL 101?

Array via Object

GDI Swimlane

GDI Plus French 101 Site

GDI Examples UEZ

GDI Basic Clock

GDI Detection

Ternary operator

Share this post


Link to post
Share on other sites
water

Can you post how you call _Excel_RangeRead()?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
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)


All by me:

"Sometimes you have to go back to where you started, to get to where you want to go." 

"Everybody catches up with everyone, eventually" 

"As you teach others, you are really teaching yourself."

From my dad

"Do not worry about yesterday, as the only thing that you can control is tomorrow."

 

WindowsError.gif

WIKI | Tabs; | Arrays; | Strings | Wiki Arrays | How to ask a Question | Forum Search | FAQ | Tutorials | Original FAQ | ONLINE HELP | UDF's Wiki | AutoIt PDF

AutoIt Snippets | Multple Guis | Interrupting a running function | Another Send

StringRegExp | StringRegExp Help | RegEXTester | REG TUTOR | Reg TUTOT 2

AutoItSetOption | Macros | AutoIt Snippets | Wrapper | Autoit  Docs

SCITE | SciteJump | BB | MyTopics | Programming | UDFs | AutoIt 123 | UDFs Form | UDF

Learning to script | Tutorials | Documentation | IE.AU3 | Games? | FreeSoftware | Path_Online | Core Language

Programming Tips

Excel Changes

ControlHover.UDF

GDI_Plus

Draw_On_Screen

GDI Basics

GDI_More_Basics

GDI Rotate

GDI Graph

GDI  CheckExistingItems

GDI Trajectory

Replace $ghGDIPDll with $__g_hGDIPDll

DLL 101?

Array via Object

GDI Swimlane

GDI Plus French 101 Site

GDI Examples UEZ

GDI Basic Clock

GDI Detection

Ternary operator

Share this post


Link to post
Share on other sites
water

How many rows and columns does your sheet have?

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
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 by nitekram

All by me:

"Sometimes you have to go back to where you started, to get to where you want to go." 

"Everybody catches up with everyone, eventually" 

"As you teach others, you are really teaching yourself."

From my dad

"Do not worry about yesterday, as the only thing that you can control is tomorrow."

 

WindowsError.gif

WIKI | Tabs; | Arrays; | Strings | Wiki Arrays | How to ask a Question | Forum Search | FAQ | Tutorials | Original FAQ | ONLINE HELP | UDF's Wiki | AutoIt PDF

AutoIt Snippets | Multple Guis | Interrupting a running function | Another Send

StringRegExp | StringRegExp Help | RegEXTester | REG TUTOR | Reg TUTOT 2

AutoItSetOption | Macros | AutoIt Snippets | Wrapper | Autoit  Docs

SCITE | SciteJump | BB | MyTopics | Programming | UDFs | AutoIt 123 | UDFs Form | UDF

Learning to script | Tutorials | Documentation | IE.AU3 | Games? | FreeSoftware | Path_Online | Core Language

Programming Tips

Excel Changes

ControlHover.UDF

GDI_Plus

Draw_On_Screen

GDI Basics

GDI_More_Basics

GDI Rotate

GDI Graph

GDI  CheckExistingItems

GDI Trajectory

Replace $ghGDIPDll with $__g_hGDIPDll

DLL 101?

Array via Object

GDI Swimlane

GDI Plus French 101 Site

GDI Examples UEZ

GDI Basic Clock

GDI Detection

Ternary operator

Share this post


Link to post
Share on other sites
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?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-01-27 - Version 1.3.3.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites
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?


All by me:

"Sometimes you have to go back to where you started, to get to where you want to go." 

"Everybody catches up with everyone, eventually" 

"As you teach others, you are really teaching yourself."

From my dad

"Do not worry about yesterday, as the only thing that you can control is tomorrow."

 

WindowsError.gif

WIKI | Tabs; | Arrays; | Strings | Wiki Arrays | How to ask a Question | Forum Search | FAQ | Tutorials | Original FAQ | ONLINE HELP | UDF's Wiki | AutoIt PDF

AutoIt Snippets | Multple Guis | Interrupting a running function | Another Send

StringRegExp | StringRegExp Help | RegEXTester | REG TUTOR | Reg TUTOT 2

AutoItSetOption | Macros | AutoIt Snippets | Wrapper | Autoit  Docs

SCITE | SciteJump | BB | MyTopics | Programming | UDFs | AutoIt 123 | UDFs Form | UDF

Learning to script | Tutorials | Documentation | IE.AU3 | Games? | FreeSoftware | Path_Online | Core Language

Programming Tips

Excel Changes

ControlHover.UDF

GDI_Plus

Draw_On_Screen

GDI Basics

GDI_More_Basics

GDI Rotate

GDI Graph

GDI  CheckExistingItems

GDI Trajectory

Replace $ghGDIPDll with $__g_hGDIPDll

DLL 101?

Array via Object

GDI Swimlane

GDI Plus French 101 Site

GDI Examples UEZ

GDI Basic Clock

GDI Detection

Ternary operator

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×