BryanVest Posted June 8, 2012 Share 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 Link to comment Share on other sites More sharing options...
BryanVest Posted June 8, 2012 Author Share 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 Link to comment Share on other sites More sharing options...
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