Writes value(s) or formula(s) to a cell or a cell range on the specified workbook and worksheet
#include <Excel.au3>
_Excel_RangeWrite ( $oWorkbook, $vWorksheet, $vValue [, $vRange = "A1" [, $bValue = True [, $bForceFunc = False]]] )
| $oWorkbook | Excel workbook object |
| $vWorksheet | Name, index or worksheet object to be written to. If set to keyword Default the active sheet will be used |
| $vValue | Can be a string, a 1D or 2D zero based array containing the data to be written to the worksheet |
| $vRange | [optional] Either an A1 range or a range object (default = "A1") |
| $bValue | [optional] If True the $vValue will be written to the value property. If False $vValue will be written to the formula property (default = True) |
| $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 object of the range the data has been written to. |
| 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 - Error occurred when writing a single cell. @extended is set to the COM error code 5 - Error occurred when writing data using the _ArrayTranspose function. @extended is set to the COM error code 6 - Error occurred when writing data using the transpose method. @extended is set to the COM error code |
If $vRange is a single cell and $vValue is an array then $vRange is extended to hold the full array.
This "expanded" range is then returned by the function.
If $vRange is not a single cell and $vValue is an array and $vValue > $vRange then the array gets truncated.
If $vRange is not a single cell and $vValue is an array and $vValue < $vRange then the exceeding cells get #NV.
The Excel transpose method has an undocumented limit on the number of cells or rows it can transpose (dependant on the Excel version).
The Excel transpose method doesn't support cells with more than 255 characters. Set $bForceFunc = True to bypass this limitation(s).
#include <Excel.au3>
#include <MsgBoxConstants.au3>
; Create application object and create a new workbook
Local $oExcel = _Excel_Open()
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeWrite Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
Local $oWorkbook = _Excel_BookNew($oExcel)
If @error Then
MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeWrite Example", "Error creating the new workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
_Excel_Close($oExcel)
Exit
EndIf
; Write a string with a line break to the active sheet in the active workbook
_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, "Test" & @CRLF & "String")
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeWrite Example 1", "Error writing to worksheet." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeWrite Example 1", "String successfully written.")
#include <Excel.au3>
#include <MsgBoxConstants.au3>
; Create application object and create a new workbook
Local $oExcel = _Excel_Open()
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeWrite Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
Local $oWorkbook = _Excel_BookNew($oExcel)
If @error Then
MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeWrite Example", "Error creating the new workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
_Excel_Close($oExcel)
Exit
EndIf
; Write a 1D array to the active sheet in the active workbook
Local $aArray1D[3] = ["AA", "BB", "CC"]
_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $aArray1D, "A3")
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeWrite Example 2", "Error writing to worksheet." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeWrite Example 2", "1D array successfully written.")
#include <Excel.au3>
#include <MsgBoxConstants.au3>
; Create application object and create a new workbook
Local $oExcel = _Excel_Open()
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeWrite Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
Local $oWorkbook = _Excel_BookNew($oExcel)
If @error Then
MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeWrite Example", "Error creating the new workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
_Excel_Close($oExcel)
Exit
EndIf
; Write a part of a 2D array to the active sheet in the active workbook
Local $aArray2D[3][5] = [[11, 12, 13, 14, 15], [21, 22, 23, 24, 25], [31, 32, 33, 34, 35]]
_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $aArray2D, "B1")
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeWrite Example 3", "Error writing to worksheet." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeWrite Example 3", "2D array successfully written.")
#include <Excel.au3>
#include <MsgBoxConstants.au3>
; Create application object and create a new workbook
Local $oExcel = _Excel_Open()
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeWrite Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
Local $oWorkbook = _Excel_BookNew($oExcel)
If @error Then
MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeWrite Example", "Error creating the new workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
_Excel_Close($oExcel)
Exit
EndIf
; Fill a range in the active sheet in the active workbook with a formula
_Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, "=B1+3", "B5:F6", False)
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeWrite Example 4", "Error writing to worksheet." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeWrite Example 4", "Range successfully filled with a formula.")