Reads the value, formula or displayed text from a cell or range of cells of the specified workbook and worksheet
#include <Excel.au3>
_Excel_RangeRead ( $oWorkbook [, $vWorksheet = Default [, $vRange = Default [, $iReturn = 1 [, $bForceFunc = False]]]] )
| $oWorkbook | Excel workbook object |
| $vWorksheet | [optional] Name, index or worksheet object to be read. If set to keyword Default the active sheet will be used (default = keyword Default) |
| $vRange | [optional] Either a range object or an A1 range. If set to Default all used cells will be processed (default = keyword Default) |
| $iReturn | [optional] What to return from the specified cell: 1 - Value (default) 2 - Formula 3 - The displayed text 4 - Value2. The only difference between Value and Value2 is that the Value2 property doesn’t use the Currency and Date data types |
| $bForceFunc | [optional] True forces to use the _ArrayTranspose function instead of the Excel transpose method (default = False). See the Remarks section for details. |
| Success: | the data from the specified cell(s). A string for a cell, a zero-based array for a range of cells. |
| Failure: | 0 and sets @error. |
| @error: | 1 - $oWorkbook is not an object or not a workbook object 2 - $vWorksheet name or index are invalid or $vWorksheet is not a worksheet object. @extended is set to the COM error code 3 - $vRange is invalid. @extended is set to the COM error code 4 - Parameter $iReturn is invalid. Has to be >= 1 and <= 4 5 - Error occurred when reading data using the transpose method. @extended is set to the COM error code 6 - Maximum size of an AutoIt array exceeded (2^24 = 16,777,216 elements) 7 - Error occurred when reading data using the _ArrayTranspose function. @extended is set to the COM error code 8 - $iReturn = 3 can only return a single cell. $vRange specified > 1 cell. |
Parameter 2 will be ignored if parameter 3 specifies a range on another worksheet then specified in parameter 2.
The Excel transpose method has an undocumented limit on the number of cells it can transpose (depends on the Excel version).
If you transpose > 65535 cells then $bForceFunc is forced to True.
The Excel transpose method doesn't support cells with more than 255 characters. Set $bForceFunc = True to bypass this limitation(s).
$iReturn = 3 only allows to get the displayed text of a single cell. That's an Excel limitation.
#include <Excel.au3>
#include <MsgBoxConstants.au3>
; Create application object and open an example workbook
Local $oExcel = _Excel_Open()
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
Local $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\Extras\_Excel1.xls")
If @error Then
MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example", "Error opening workbook '" & @ScriptDir & "\Extras\_Excel1.xls'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
_Excel_Close($oExcel)
Exit
EndIf
; Read data from a single cell on the active sheet of the specified workbook
Local $sResult = _Excel_RangeRead($oWorkbook, Default, "A1")
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example 1", "Error reading from workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example 1", "Data successfully read." & @CRLF & "Value of cell A1: " & $sResult)
#include <Array.au3>
#include <Excel.au3>
#include <MsgBoxConstants.au3>
; Create application object and open an example workbook
Local $oExcel = _Excel_Open()
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
Local $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\Extras\_Excel1.xls")
If @error Then
MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example", "Error opening workbook '" & @ScriptDir & "\Extras\_Excel1.xls'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
_Excel_Close($oExcel)
Exit
EndIf
; Read the formulas of a cell range on sheet 2 of the specified workbook
Local $aResult = _Excel_RangeRead($oWorkbook, 2, "A1:C1", 2)
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example 2", "Error reading from workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example 2", "Data successfully read." & @CRLF & "Please click 'OK' to display the formulas of cells A1:C1 of sheet 2.")
_ArrayDisplay($aResult, "Excel UDF: _Excel_RangeRead Example 2 - Cells A1:C1 of sheet 2")
#include <Array.au3>
#include <Excel.au3>
#include <MsgBoxConstants.au3>
; Create application object and open an example workbook
Local $oExcel = _Excel_Open()
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
Local $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\Extras\_Excel1.xls")
If @error Then
MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example", "Error opening workbook '" & @ScriptDir & "\Extras\_Excel1.xls'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
_Excel_Close($oExcel)
Exit
EndIf
; Read the formulas of a cell range (all used cells in column A)
Local $aResult = _Excel_RangeRead($oWorkbook, Default, $oWorkbook.ActiveSheet.Usedrange.Columns("A:A"), 2)
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example 3", "Error reading from workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example 3", "Data successfully read." & @CRLF & "Please click 'OK' to display all formulas in column A.")
_ArrayDisplay($aResult, "Excel UDF: _Excel_RangeRead Example 3 - Formulas in column A")