Value Copy in Excel

[GOSM]

Hey,

as part of a larger Programm I am trying to copy the values from one cell in an Excel file to another. The code I am using is the folowing.

 

Local $oExcel = _Excel_Open()
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeCopy Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)


  _Excel_RangeCopyPaste($oExcel.ActiveSheet, "V4:W43", "X4", False, $xlPasteValues, Default, True)
   If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeCopy Example 4", "Error copying rows." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
   MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeCopy Example 4", "2 Rows successfully pasted from the clipboard to row 1.")

As I understood it the option $xlPasteValues should do the trick  but it is still copying the inserted formulas. I attached a test code and excel file.

Thanks for the help Simon

Copy Values Test.zip

[water]

Can't test at the moment but I think this should work:

Global $oActiveSheet = $oExcel.ActiveSheet
  _Excel_RangeCopyPaste($oActiveSheet, $oActiveSheet.Range("V4:W43"), $oActiveSheet.Range("X4"), False, $xlPasteValues, Default, True)

I have never tried to access a worksheet without opening the workbook by _Excel_BookOpen or by attaching to an already open workbook by _Excel_BookAttach.

[GOSM]

Hi Water,

thanks for the answer. Sadly it is still not only copying the values but the formula. 😐

[water]

The last line in the Remarks section for _Excel_CopyPaste explains what happens: "If $vSourceRange and $vTargetRange are specified parameters $iPaste, $iOperation, $bSkipBlanks and $bTranspose are ignored"

In this case I suggest to use _Excel_RangeRead to retrieve the value and _Excel_RangeWrite to copy the value to the target cell.

Or you use an absolute cell reference ("=MittelWert($W$2:$W$11") so the copied formula still returns the same result.

[water]

Or you call _Excel_RangeCopyPast twice. First call the TargetRange is set to Default. This copies the cell to the clipboard. Then call _Excel_RangeCopyPaste a second time and set SourceRange to Default. In this case no parameters get ignored and values can be copied.
Example:

#include <Excel.au3>
Global $oExcel = _Excel_Open()
ConsoleWrite(@error & @CRLF)
Global $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\test.xlsx")
ConsoleWrite(@error & @CRLF)
Global $oWorkSheet = $oWorkBook.Sheets(1)
_Excel_RangeCopyPaste($oWorkSheet, $oWorkSheet.Range("C1"), Default)
_Excel_RangeCopyPaste($oWorkSheet, Default, $oWorkSheet.Range("C7"), False, $xlPasteValues, Default, True)

A bit complex but this is the way Excel works.

[GOSM]

Hello Water,

 

Thank you I missed that in the remarks. Calling it Twice works.

 

Thank You for your Help

[water]

I would have preferred an easier solution. But using the ClipBoard for every copy/paste operation would have led to user questions as well.
So I have stayed with the solution with least user confusion.