Pokemon Posted November 5, 2013 Share Posted November 5, 2013 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. Link to comment Share on other sites More sharing options...
water Posted November 5, 2013 Share Posted November 5, 2013 (edited) 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 My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Pokemon Posted November 5, 2013 Author Share Posted November 5, 2013 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?) Link to comment Share on other sites More sharing options...
Solution water Posted November 6, 2013 Solution Share Posted November 6, 2013 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 (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
water Posted November 6, 2013 Share Posted November 6, 2013 (edited) 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 My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
water Posted November 6, 2013 Share Posted November 6, 2013 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 (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Pokemon Posted November 6, 2013 Author Share Posted November 6, 2013 $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. Link to comment Share on other sites More sharing options...
water Posted November 6, 2013 Share Posted November 6, 2013 My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now