Excel : Copy/Paste with link in COM

[Pokemon]

Hello everyone,

 

I'm trying to translate an Excel VBA macro and I exposed the ultimate step: paste Link.

 

VBA:

...

Selection.Copy

Range ("A186"). Select

ActiveSheet.Paste Link: = True

 

I translate as:

 

AU3:

....

$ oExcel.Selection.Copy

$ oExcel.Range ("A186"). Select

$ oExcel.Application.CutCopyMode = False

 

then ...

 

With $ oExcel

. Activesheet.PasteSpecial (0xFFFFEFBD, Default, Default, Default) (which copies only the values​​)

or

. ActiveSheet.Paste (which copies all)

EndWith

 

I do not know how to pass the parameter Link: = True by $ oExcel object.

 

Can anyone help me?

 

Thank you in advance.

[water]

Welcome to AutoIt and the forum!

Link: = True translates to the second parameter of the Paste method as described here.

Edited November 5, 2013 by water

[Pokemon]

First of all, thank's for your reply.

 Ok so in my case it would be something like :

With $oExcel

. ActiveSheet.Paste Link:=True

EndWith

 

Isn't it ? (spaces are allowed in this syntax?)

[water]

AutoIt doesn't support named parameters.

Needs to be something like this:

$oExcel.ActiveSheet.Paste(Default, True)

[water]

With my rewritten Excel UDF the solution looks like this (copies the values from one worksheet to another):

#include <Excel Rewrite.au3>

; Connect to Excel or open a new instance
Global $oExcel = _Excel_Open()
; Create a new empty Workbook with 3 sheets
Global $oWorkbook = _Excel_BookNew($oExcel, 3)
$oSheetFrom = $oWorkbook.Sheets.Item(1)
$oSheetTo = $oWorkbook.Sheets.Item(2)
; Fill the source sheet with formulas
Global $aData[1][2] = [[1,2]]
_Excel_RangeWrite($oWorkbook, $oSheetFrom, $aData, "A1:B1")
_Excel_RangeWrite($oWorkbook, $oSheetFrom, "=A1+1", "A2:B10")
; Copy all cells to the clipboard
_Excel_RangeCopyPaste($oSheetFrom, $oSheetFrom.UsedRange)
; Paste just the values to a new worksheet
_Excel_RangeCopyPaste($oSheetFrom, Default, $oSheetTo.Range("A1"), Default, $xlPasteValues)

Edited November 6, 2013 by water

[water]

Or without using (and overwriting) the clipboard:

#include <Excel Rewrite.au3>

; Connect to Excel or open a new instance
Global $oExcel = _Excel_Open()
; Create a new empty Workbook with 3 sheets
Global $oWorkbook = _Excel_BookNew($oExcel, 3)
$oSheetFrom = $oWorkbook.Sheets.Item(1)
$oSheetTo = $oWorkbook.Sheets.Item(2)
; Fill the source sheet with formulas
Global $aData[1][2] = [[1,2]]
_Excel_RangeWrite($oWorkbook, $oSheetFrom, $aData, "A1:B1")
_Excel_RangeWrite($oWorkbook, $oSheetFrom, "=A1+1", "A2:B10")
; Directly copy all cells (without overwriting the clipboard
$oRangeTo = $oSheetTo.Range("A1").Resize($oSheetFrom.UsedRange.Rows.Count, $oSheetFrom.UsedRange.Columns.Count)
$oRangeTo.Value = $oSheetFrom.UsedRange.Value

[Pokemon]

  $oExcel.ActiveSheet.Paste(Default, True) works perfectly.

One more time, thank's to share your experience.

I think I will definitely take your Excel UDF instead of the official UDF

Have a good day.

[water]