Sign in to follow this  
Followers 0
sdfg4545

Excel: copying formulas from cell to cell

6 posts in this topic

I have to add data to the bottom of a large excel worksheet.

The last row has many formulas that have to be copied into the new row at the bottom.

The ExcelReadCell function only copies the data not the underlying formula.

How can I copy a cell formula and not the cell value?

Share this post


Link to post
Share on other sites



Try this (not tested):

; #FUNCTION# ====================================================================================================================
; Name...........: _ExcelReadFormula
; Description ...: Read the formula from a cell
; Syntax.........: _ExcelReadFormula($oExcel, $sRangeOrRow[, $iColumn = 1])
; Parameters ....: $oExcel - Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew()
;                  $sRangeOrRow - Either an A1 range, or an integer row number to read from if using R1C1
;                  $iColumn - The column to read from if using R1C1 (default = 1)
; Return values .: Success      - Returns the formula string from the specified cell
;                  Failure        - Returns 0 and sets @error on errors:
;                  |@error=1     - Specified object does not exist
;                  |@error=2     - Specified parameter is incorrect
;                  |@extended=0 - Row out of valid range
;                  |@extended=1 - Column out of valid range
; Author ........: PsaltyDS, modified from SEO <locodarwin at yahoo dot com> _ExcelReadCell()
; Modified.......: 
; Remarks .......: This function will only read one cell per call - if the specified range spans
;                  multiple cells, only the content of the top left cell will be returned.
; Related .......:
; Link ..........:
; Example .......: Yes
; ===============================================================================================================================
Func _ExcelReadFormula($oExcel, $sRangeOrRow, $iColumn = 1)
    If Not IsObj($oExcel) Then Return SetError(1, 0, 0)
    If Not StringRegExp($sRangeOrRow, "[A-Z,a-z]", 0) Then
        If $sRangeOrRow < 1 Then Return SetError(2, 0, 0)
        If $iColumn < 1 Then Return SetError(2, 1, 0)
        Return $oExcel.Activesheet.Cells($sRangeOrRow, $iColumn).FormulaR1C1
    Else
        Return $oExcel.Activesheet.Range($sRangeOrRow).Formula
    EndIf
EndFunc   ;==>_ExcelReadFormula

It's just a version of _ExcelReadCell() pulling a different property from the same object.

;)


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites

I see some people have this :

#include <ExcelCOM_UDF.au3>

From where can I download that file?

Share this post


Link to post
Share on other sites

Resolved.

Found this code in the post by PsaltyDS:

$val = $Excel_DARTTarget.Activesheet.Range("AW3").Formula

Why isnt that in the AutoIt documentation?

Was I looking in the wrong spot?

What is ExcelCOM_UDF.au3 and where can I get some?

Share this post


Link to post
Share on other sites

That was the old version of the UDF created originally by Locodarwin before it became one of the standard UDFs included with the AutoIt distribution. A subset of the old version functions were updated and given standardized documentation as the current Excel.au3. The old version is not supported anymore, but it is instructional if you are looking for some examples.

;)


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites

No need to open two topics and necro another, about more or less the same subject. You've now been answered in all three, so it's hardly like one topic wouldn't have gotten you your answers.

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