AshishS Posted December 6, 2019 Share Posted December 6, 2019 Hi friends, I am new to Autoit and would like to automate excel task. I am trying to bring source formatted vlookup text to another sheet. I am looking for autoit to help me but I got stuck in autoit code. My idea is to select the filtered excel cell value with this code, copy it and paste at destination cell but I don't know how to select specific filtered cell so that I can copy it. Need your help to get rid of this. $oRange = $oExcel.ActiveSheet.Range("A1:AK871") For $oRow In $oRange.Rows If $oRow.Hidden = False Then ConsoleWrite($oRow.Columns(37).Value) Send("^c") EndIf Next Also let me know if there is a better way to do this task in excel only. Link to comment Share on other sites More sharing options...
water Posted December 6, 2019 Share Posted December 6, 2019 Welcome to AutoIt and the forum! How to retrieve all filtered rows can be found in the wiki. 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...
AshishS Posted December 6, 2019 Author Share Posted December 6, 2019 Water, Thanks for reply. I don't need to store my data in array or variable, as it will not copy the source formatting. I just need to select the filtered specific cell and copy that output in excel and paste at destination. Link to comment Share on other sites More sharing options...
Nine Posted December 6, 2019 Share Posted December 6, 2019 It is much more simple and efficient to store it in an array and write it to the target excel destination. Copy / paste is for human. Arrays are for machine. Take the suggestion of @water and look at _Excel_RangeWrite. You will get script in no time... “They did not know it was impossible, so they did it” ― Mark Twain Spoiler Block all input without UAC Save/Retrieve Images to/from Text Monitor Management (VCP commands) Tool to search in text (au3) files Date Range Picker Virtual Desktop Manager Sudoku Game 2020 Overlapped Named Pipe IPC HotString 2.0 - Hot keys with string x64 Bitwise Operations Multi-keyboards HotKeySet Recursive Array Display Fast and simple WCD IPC Multiple Folders Selector Printer Manager GIF Animation (cached) Screen Scraping Multi-Threading Made Easy Link to comment Share on other sites More sharing options...
water Posted December 6, 2019 Share Posted December 6, 2019 As $oArea is a Range object you could use function _Excel_CopyPaste to copy each Area of the filtered sheet. 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...
Moderators JLogan3o13 Posted December 7, 2019 Moderators Share Posted December 7, 2019 Moved to the appropriate forum, as the Developer General Discussion forum very clearly states: Quote General development and scripting discussions. If it's super geeky and you don't know where to put it - it's probably here. Do not create AutoIt-related topics here, use the AutoIt General Help and Support or AutoIt Technical Discussion forums. Moderation Team "Profanity is the last vestige of the feeble mind. For the man who cannot express himself forcibly through intellect must do so through shock and awe" - Spencer W. Kimball How to get your question answered on this forum! Link to comment Share on other sites More sharing options...
AshishS Posted December 10, 2019 Author Share Posted December 10, 2019 Thanks all for your reply. Hey @water, I implemented _ExcelRangeCopyPaste and it works great for me. Thanks a ton! Only problem is that it will copy the header cell as well, which is not required. Please help me to find where I am wrong. Thanks in advance. For $oRow In $oExcel.Worksheets("1").AutoFilter.Range.Rows If $oRow.Hidden = False Then _Excel_RangeCopyPaste($oExcel.ActiveSheet, $oRow(1).Columns(3), $oExcel.Worksheets("2").Range($Fi)) EndIf Next Link to comment Share on other sites More sharing options...
water Posted December 10, 2019 Share Posted December 10, 2019 You could simply delete the header range after you have copied all filtered ranges. 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