BryanVest Posted June 8, 2012 Posted June 8, 2012 (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: expandcollapse popupWhile 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 June 8, 2012 by BryanVest
BryanVest Posted June 8, 2012 Author Posted June 8, 2012 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
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now