Sign in to follow this  
Followers 0
BryanVest

Excel Error

2 posts in this topic

#1 ·  Posted (edited)

I cant seem to figure out this one as the error is with Excel.udf it seems.

Error:

C:Program FilesAutoIt3IncludeExcel.au3 (191) : ==> The requested action with this object has failed.:
If $sPassword = "" And $sWritePassword = "" Then .WorkBooks.Open($sFilePath, Default, $fReadOnly)
If $sPassword = "" And $sWritePassword = "" Then .WorkBooks.Open($sFilePath, Default, $fReadOnly)^ ERROR
->15:46:51 AutoIT3.exe ended.rc:1

Code:

While 1
$oExcel = _ExcelBookOpen("C:Documents and SettingsTruckSystemDesktopTrailerTrailer Status Log.xlsx", 0)
$aArray = _ExcelReadSheetToArrayEx($oExcel)
GUICtrlSetData($carspace1,$aArray[4][1])
Sleep(60000)
$nMsg = GUIGetMsg()
Switch $nMsg
  Case $GUI_EVENT_CLOSE
   Exit
EndSwitch
WEnd
; #FUNCTION# ====================================================================================================================
; Name...........: _ExcelReadSheetToArrayEx
; Description ...: Create a 2D array from the rows/columns of the active worksheet.
; Syntax.........: _ExcelReadSheetToArray($oExcel[, $iStartRow = 1[, $iStartColumn = 1[, $iRowCnt = 0[, $iColCnt = 0]]]])
; Parameters ....: $oExcel - Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew()
;                $iStartRow - Row number to start reading, defaults to 1 (first row)
;                $iStartColumn - Column number to start reading, defaults to 1 (first column)
;                $iRowCnt - Count of rows to read, defaults to 0 (all)
;                $iColCnt - Count of columns to read, defaults to 0 (all)
; Return values .: Success  - Returns a 2D array with the specified cell contents by [$row][$col]
;                Failure  - Returns 0 and sets @error on errors:
;                |@error=1  - Specified object does not exist
;                |@error=2  - Start parameter out of range
;                |@extended=0 - Row out of range
;                |@extended=1 - Column out of range
;                |@error=3 - Count parameter out of range
;                |@extended=0 - Row count out of range
;                |@extended=1 - Column count out of range
; Modified.......: litlmike (added Column shift parameter to Start Array Column on 0) and PsaltyDS 01/04/08 - 2D version _ExcelReadSheetToArray()
; Remarks .......: Returned array has row count in [0][0] and column count in [0][1].
;                Except for the counts above, row 0 and col 0 of the returned array are empty, as actual
;                cell data starts at [1][1] to match R1C1 numbers.
;                By default the entire sheet is returned.
;                If the sheet is empty [0][0] and [0][1] both = 0.
; Related .......:
; Link ..........:
; Example .......: No
; ===============================================================================================================================
Func _ExcelReadSheetToArrayEx($oExcel, $iStartRow = 1, $iStartColumn = 1, $iRowCnt = 0, $iColCnt = 0)
Local $avRET[1][2] = [[0, 0]] ; 2D return array
; Test inputs
If Not IsObj($oExcel) Then Return SetError(1, 0, 0)
If $iStartRow < 1 Then Return SetError(2, 0, 0)
If $iStartColumn < 1 Then Return SetError(2, 1, 0)
If $iRowCnt < 0 Then Return SetError(3, 0, 0)
If $iColCnt < 0 Then Return SetError(3, 1, 0); Get size of current sheet as R1C1 string
;   Note: $xlCellTypeLastCell and $x1R1C1 are constants declared in ExcelCOM_UDF.au3
Local $sLastCell = $oExcel.Application.Selection.SpecialCells($xlCellTypeLastCell).Address(True, True, $xlR1C1)
; Extract integer last row and col
$sLastCell = StringRegExp($sLastCell, "(d+)", 3)
Local $iLastRow = $sLastCell[0]
Local $iLastColumn = $sLastCell[1]; Return 0's if the sheet is blank
If $sLastCell = "R1C1" And $oExcel.Activesheet.Cells($iLastRow, $iLastColumn).Value = "" Then Return $avRET
; Check input range is in bounds
If $iStartRow > $iLastRow Then Return SetError(2, 0, 0)
If $iStartColumn > $iLastColumn Then Return SetError(2, 1, 0)
If $iStartRow + $iRowCnt - 1 > $iLastRow Then Return SetError(3, 0, 0)
If $iStartColumn + $iColCnt - 1 > $iLastColumn Then Return SetError(3, 1, 0); Check for defaulted counts
If $iRowCnt = 0 Then $iRowCnt = Number($iLastRow)
If $iColCnt = 0 Then $iColCnt = Number($iLastColumn)
;Read data
Local $aArray = $oExcel.ActiveSheet.Range($oExcel.Cells($iStartRow, $iStartColumn), $oExcel.Cells($iRowCnt, $iColCnt)).Value
Dim $avRET[UBound($aArray, 2) + 1][UBound($aArray)] = [[UBound($aArray, 2), UBound($aArray)]] ;Declare Array again and set row and col count
For $i = 0 To UBound($aArray, 1) - 1
  For $j = 0 To UBound($aArray, 2) - 1
   $avRET[$j + 1][$i] = $aArray[$i][$j]
  Next
Next
Return $avRET
EndFunc   ;==>_ExcelReadSheetToArrayEx
Edited by BryanVest

Share this post


Link to post
Share on other sites



Nevermind figured it out myself. Added the filepath as a variable and it worked.

While 1
 $filepath = "C:Documents and SettingsTruckSystemDesktopTrailerTrailer Status Log.xlsx"
$oExcel = _ExcelBookOpen($filepath, 0)
$aArray = _ExcelReadSheetToArrayEx($oExcel)
GUICtrlSetData($carspace1,$aArray[4][1])
Sleep(60000)
 $nMsg = GUIGetMsg()
 Switch $nMsg
  Case $GUI_EVENT_CLOSE
   Exit
 EndSwitch
WEnd

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  
Followers 0