GhostLine Posted July 21, 2014 Share Posted July 21, 2014 Hi guys ! I'm trying to merge a random number of worksheets stored in a spreadsheet in the first worksheet of this spreadsheet. I was thinking of using some Excel to array, then to Excel trick (in order to eliminate all empty lines and to simply sort the whole thing), but there's no more traces of this kind of functions in the new Excel UDF. So if there is someone kind enough to help me ... Thanks ! Link to comment Share on other sites More sharing options...
water Posted July 21, 2014 Share Posted July 21, 2014 To read the content of a Worksheet please have a look at function _Excel_RangeRead and the examples that can be found in the help file. The script breaking changes section in AutoIt 3.3.12.0 shows exactly what has changed from the old to the new Excel UDF. 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...
GhostLine Posted July 21, 2014 Author Share Posted July 21, 2014 (edited) I feel so dumb ... I wasn't aware of the existence of the script breaking changes. Anyway, thanks ! Can I leave this topic open, in order to give the solution when I'll find it ? Edited July 21, 2014 by GhostLine Link to comment Share on other sites More sharing options...
water Posted July 21, 2014 Share Posted July 21, 2014 Sure. Post any questions you have or a loud HURRA when you finished your project 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...
GhostLine Posted July 25, 2014 Author Share Posted July 25, 2014 I've done it ! #include <Array.au3> #include <Excel.au3> #include <File.au3> #include <MsgBoxConstants.au3> Local $chem_imp = "C:\Users\ghost.line\Documents\AutoIt\TecReg\Import\" Local $oAppl = _Excel_Open(False) Dim $sResult[1][106] $liste_fichiers = _FileListToArray($chem_imp, Default, Default, True) For $i = 1 To $liste_fichiers[0] $test = $liste_fichiers[$i] Local $oWorkbook = _Excel_BookOpen($oAppl, $test, False, False) If @error Then MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example", "Error opening workbook '" & $test & "." & @CRLF & "@error = " & @error & ", @extended = " & @extended) _Excel_Close($oAppl) Exit EndIf Local $sArray = _Excel_RangeRead($oWorkbook, 2, "A2:CZ65") If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example 1", "Error reading from workbook." & @CRLF & "@error = " & @error & ", @extended = " & @extended) If $i <> 1 Then ReDim $sResult[UBound($sResult) + UBound($sArray)][104] _ArrayAdd($sResult, $sArray) Next _ArrayDisplay($sResult) I've a question, in bonus : when I put Local $sArray = _Excel_RangeRead($oWorkbook, 2, "A2:CZ65"), I collect lot of blank lines. Is there a simple way to know how much lines are not empty ? Link to comment Share on other sites More sharing options...
water Posted July 25, 2014 Share Posted July 25, 2014 You just want to read all rows that have been used? Means: Do not read "empty" rows: at the end? 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...
GhostLine Posted July 25, 2014 Author Share Posted July 25, 2014 In fact, I've empty lines in all the xls files I'm using. So, after "data compilation", my array looks like that : AAAA|YES|NO|YES <--- begining of the first xls file BBBB|YES|YES|YES <--- end of the first xls file ZZZZ|YES|YES|YES <--- begining of the second xls file [...] Link to comment Share on other sites More sharing options...
water Posted July 25, 2014 Share Posted July 25, 2014 To only read the used range try this: Replace Local $sArray = _Excel_RangeRead($oWorkbook, 2, "A2:CZ65") with Local $sArray = _Excel_RangeRead($oWorkbook, 2, $oWorkbook.Sheets(2).UsedRange) This returns all cells that ever have been touched. So if you get an empty cell then it is empty or had a content which was removed. If the first cell thas has ever been touched is e.g. B5 then the returned array starts with this cell. 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