Jump to content
Sign in to follow this  
Pokemon

Excel : Copy/Paste with link in COM

Recommended Posts

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.

Share this post


Link to post
Share on other sites
water

Welcome to AutoIt and the forum!

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

Edited by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
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?)

Share this post


Link to post
Share on other sites
water

AutoIt doesn't support named parameters.

Needs to be something like this:

$oExcel.ActiveSheet.Paste(Default, True)

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
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 by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
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

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
water

:D


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

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  

×