Hoth2001 Posted September 12, 2017 Share Posted September 12, 2017 Hello all.....I've been tinkering through all of the excel help files. everything I see seems to always make references to both Row and Column. For example "A2" etc. Here is what I'm trying to do: Select a column in one workbook Copy that column and then Paste that column in a specific column on another workbook. I was going to start to go down the manual process of a bunch of MouseClick commands...but there has to be a better way?? Is there an excel function I don't know about that can target a specific column and perform actions like copy/paste?? Link to comment Share on other sites More sharing options...
water Posted September 12, 2017 Share Posted September 12, 2017 _Excel_RangeCopyPaste: Cuts or copies one or multiple cells, rows or columns to a range or from/to the clipboard Example: To process column D use "D:D" as the range specifier (parameter $vSourceRange). 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...
Hoth2001 Posted September 12, 2017 Author Share Posted September 12, 2017 19 minutes ago, water said: _Excel_RangeCopyPaste: Cuts or copies one or multiple cells, rows or columns to a range or from/to the clipboard Example: To process column D use "D:D" as the range specifier (parameter $vSourceRange). hmmmm...something not working with this code: I'm trying to open a new file open an exisiting file Copy column B from existing file Paste into Column A on new file THe files open...but the copy/paste doesn't execute. What's wrong with the code? Local $oExcel =_Excel_Open() _Excel_BookNew($oExcel) sleep(300) Local $oExcel_1 = _Excel_Open() Local $sWorkbook = ($aArray[1][3]) Local $oWorkbook = _Excel_BookOpen($oExcel_1,$sWorkbook) sleep(300) _Excel_RangeCopyPaste($sWorkbook,"B:B") _Excel_RangeCopyPaste($oExcel,"A:A", $xlPasteValues) Link to comment Share on other sites More sharing options...
water Posted September 12, 2017 Share Posted September 12, 2017 Should be something like this (untested): Local $oExcel =_Excel_Open() Local $oWorkbookOut = _Excel_BookNew($oExcel) Local $sWorkbook = $aArray[1][3] Local $oWorkbookIn = _Excel_BookOpen($oExcel, $sWorkbook) _Excel_RangeCopyPaste($oWorkbookIn, "B:B") _Excel_RangeCopyPaste($oWorkbookIn, Default, $oWorkbookOut.ActiveSheet.Range("A1"), Default, $xlPasteValues) If it doesn't work, please add some error checking (@error and @extended are set in case of a problem). 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...
Hoth2001 Posted September 12, 2017 Author Share Posted September 12, 2017 16 minutes ago, water said: Should be something like this (untested): Local $oExcel =_Excel_Open() Local $oWorkbookOut = _Excel_BookNew($oExcel) Local $sWorkbook = $aArray[1][3] Local $oWorkbookIn = _Excel_BookOpen($oExcel, $sWorkbook) _Excel_RangeCopyPaste($oWorkbookIn, "B:B") _Excel_RangeCopyPaste($oWorkbookIn, Default, $oWorkbookOut.ActiveSheet.Range("A1"), Default, $xlPasteValues) If it doesn't work, please add some error checking (@error and @extended are set in case of a problem). ummm...I'd love to do @error....etc. But I have no clue how to use that. I tried the code and it didn't work. hmmmmm.... where can I go to get my head around this @error @extended stuff? Link to comment Share on other sites More sharing options...
water Posted September 12, 2017 Share Posted September 12, 2017 When you check the help file you will see how a function returns an error. Either by setting the returnvalue or the macros @error and/or @extended. Check any of the _Excel* examples in the help file to see how this macros are used. 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...
Hoth2001 Posted September 12, 2017 Author Share Posted September 12, 2017 umm...well...I made an attempt to use the errors from the example. The code now gleefully reports to me that copying to the clipboard and pasting to my desired column was a success HOWEVER......what I want to happen does not happen. Copying and pasting Im' guessing should be a simple task to achieve. There must be a simple syntax error somewhere. Any help is MUCH appreciated! Local $oExcel_2 =_Excel_Open() Local $sWorkbook2 = "C:\Users\User\Desktop\ToGraph.xlsx" Local $oWorkbook2 = _Excel_BookOpen($oExcel_2,$sWorkbook2) Sleep(400) Local $oExcel_1 = _Excel_Open() Local $sWorkbook = ($aArray[1][3]) Local $oWorkbook = _Excel_BookOpen($oExcel_1,$sWorkbook) sleep(300) _Excel_RangeCopyPaste($sWorkbook,"B:B") Sleep(400) If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeCopy Example 3", "Error copying rows." & @CRLF & "@error = " & @error & ", @extended = " & @extended) MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeCopy Example 3", "Column B Successfuly Copied to the Clipboard.") _Excel_RangeCopyPaste($sWorkbook2,"A:A", $xlPasteValues) Sleep(400) 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", "THe Column was successfully pasted.") Link to comment Share on other sites More sharing options...
water Posted September 12, 2017 Share Posted September 12, 2017 Please remove the Sleep statements from your script. They are not needed. @error always reports the value of the last called function. In your case it is the value for the Sleep statement - which always runs successfully. 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 September 12, 2017 Share Posted September 12, 2017 BTW: Why do you create two instances of Excel? Call _Excel_Open once and use this instance for all following _Excel_* functions. 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...
Hoth2001 Posted September 12, 2017 Author Share Posted September 12, 2017 13 minutes ago, water said: BTW: Why do you create two instances of Excel? Call _Excel_Open once and use this instance for all following _Excel_* functions. I suppose adding useless code comes with the territory of lacking experience I've changed the code to this: Local $oExcel_1 = _Excel_Open() Local $sWorkbook2 = "C:\Users\User\Desktop\ToGraph.xlsx" Local $oWorkbook2 = _Excel_BookOpen($oExcel_1,$sWorkbook2) Sleep(400) Local $sWorkbook = ($aArray[1][3]) Local $oWorkbook = _Excel_BookOpen($oExcel_1,$sWorkbook) sleep(300) _Excel_RangeCopyPaste($sWorkbook,"B:B") If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeCopy Example 3", "Error copying rows." & @CRLF & "@error = " & @error & ", @extended = " & @extended) MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeCopy Example 3", "Column B Successfuly Copied to the Clipboard.") _Excel_RangeCopyPaste($sWorkbook2,"A:A", $xlPasteValues) 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", "THe Column was successfully pasted.") and yes the computer is telling me there's an error now. It is letting me know that it is unable to copy................... I've attached an image of the error message. How can I make this message useful?? and how is it giving me further insight on how to correct the syntax error in my code? Link to comment Share on other sites More sharing options...
Hoth2001 Posted September 12, 2017 Author Share Posted September 12, 2017 (edited) so..when I refer to the help files... it seems that my worksheet is not an object? since the error is 1 How is that so? Edited September 12, 2017 by Hoth2001 Link to comment Share on other sites More sharing options...
water Posted September 12, 2017 Share Posted September 12, 2017 You specified the filepath not the worksheet object for the copy/paste operation. This works for me: #include <Excel.au3> Local $sWorkbookIn = "C:\temp\TestIn.xlsx" Local $sWorkbookOut = "C:\temp\TestOut.xlsx" Local $oExcel = _Excel_Open() If @error Then Exit MsgBox(0, "", "Error when opening Excel. @error = " & @error) Local $oWorkbookIn = _Excel_BookOpen($oExcel, $sWorkbookIn) If @error Then Exit MsgBox(0, "", "Error when opening WorkbookIn. @error = " & @error) Local $oWorkbookOut = _Excel_BookOpen($oExcel, $sWorkbookOut) If @error Then Exit MsgBox(0, "", "Error when opening WorkbookOut. @error = " & @error) _Excel_RangeCopyPaste($oWorkbookIn.ActiveSheet, "B:B") If @error Then Exit MsgBox(0, "", "Error when copying WorkbookIn. @error = " & @error) _Excel_RangeCopyPaste($oWorkbookIn.ActiveSheet, Default, $oWorkbookOut.ActiveSheet.Range("A1"), Default, $xlPasteValues) If @error Then Exit MsgBox(0, "", "Error when pasting WorkbookOut. @error = " & @error) 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...
Hoth2001 Posted September 12, 2017 Author Share Posted September 12, 2017 Eureka! THat worked! Thanks for all the help! Link to comment Share on other sites More sharing options...
water Posted September 12, 2017 Share Posted September 12, 2017 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...
Karn Posted September 12, 2017 Share Posted September 12, 2017 Is that function real? I don't know that. But it sounds really useful in excel “Reality continues to ruin my life.” - Utkarsh Classes Jodhpur apk Link to comment Share on other sites More sharing options...
water Posted September 12, 2017 Share Posted September 12, 2017 Which function are you talking about? 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