Melfritch Posted September 13, 2012 Share Posted September 13, 2012 (edited) Hi guys, Ive been searching throught the help me file and havent found any info on the subject..is there a function in auto it once _ExcelBookOpen has been called to save the file in separate files named after the alphabetical order of the cells in the first collumn? ( all words starting with ''A'' saved in in 1 file named A.csv, all words with ''B'' saved in 1 file named B.csv, etc...) basicly i want to save the entire row in those files... Any Hint on the subject would be greatly appreciated..im really stumped on the subject right now : / Edited September 13, 2012 by Melfritch Link to comment Share on other sites More sharing options...
water Posted September 13, 2012 Share Posted September 13, 2012 Read the whole worksheet into an array using _ExcelReadSheetToArray, sort the array in alphabetical order, then loop through the array and write all rows to a file until the first character changes. My UDFs and Tutorials: Spoiler UDFs: Active Directory (NEW 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs: Excel - Example Scripts - Wiki Word - Wiki Tutorials: ADO - Wiki WebDriver - Wiki Link to comment Share on other sites More sharing options...
Moderators JLogan3o13 Posted September 13, 2012 Moderators Share Posted September 13, 2012 (edited) Hi, Melfritch, welcome to the forum. My best guess would be to use _ExcelReadSheetToArray first, then loop through your array and pull out any items that start with the letter(s) you'd like. Then save those off to separate files. D'oh, too slow I would envision something like this to get you started, Melfritch. You should be able to fit this to your needs. #include <Excel.au3> Local $oExcel = _ExcelBookOpen(@DesktopDir & "\Names.xls", 0, True) Local $newbook1 = _ExcelBookNew() Local $newbook2 = _ExcelBookNew() Local $newbook3 = _ExcelBookNew() Local $aArray $aArray = _ExcelReadSheetToArray($oExcel, 1, 1) _ExcelBookClose($oExcel, 0) _ArrayDisplay($aArray) For $i = 1 To UBound($aArray) - 1 Select Case StringLeft($aArray[$i][1], 1) = "a" _ExcelWriteCell($newbook1, $aArray[$i][1], $i, 1) Case StringLeft($aArray[$i][1], 1) = "b" _ExcelWriteCell($newbook2, $aArray[$i][1], $i, 1) Case StringLeft($aArray[$i][1], 1) = "c" _ExcelWriteCell($newbook3, $aArray[$i][1], $i, 1) EndSelect Next _ExcelBookClose($newbook1, 1) _ExcelBookClose($newbook2, 1) _ExcelBookClose($newbook3, 1) Edited September 13, 2012 by JLogan3o13 "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...
Melfritch Posted September 13, 2012 Author Share Posted September 13, 2012 Thx guys really appreciated, k one last question then..how do i set _ArraySearch to search all the words starting with ''A'' and stop before ''B'' ? _ArraySearch($avArray, $sSearch, 0, 0, 0, 1) $iCase? Link to comment Share on other sites More sharing options...
water Posted September 13, 2012 Share Posted September 13, 2012 You have to do it yourself. Sort the array ascending on column 1. The process the array like this: Local $sChar = "" For $i = 1 to $avArray[0][0] If $sChar <> StringLeft($avArray[$i][0], 1) And $sChar <> "" Then ; First character has changed ; Do whatever needs to be done when the first character changes Endif $sChar = StringLeft($avArray[$i][0], 1) ; Do whatever needs to be done with the current record FileWriteLine("C:\temp\" & $sChar & ".CSV", "<here goes the data you want to write to the file>") Next My UDFs and Tutorials: Spoiler UDFs: Active Directory (NEW 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs: Excel - Example Scripts - Wiki Word - Wiki Tutorials: ADO - Wiki WebDriver - Wiki Link to comment Share on other sites More sharing options...
Melfritch Posted September 13, 2012 Author Share Posted September 13, 2012 Thank you so much JLogan ..whats the argument to save the entire row and not just the first cell at : ExcelWriteCell($newbook1, $aArray[$i][1], $i, 1) ? Link to comment Share on other sites More sharing options...
Moderators JLogan3o13 Posted September 13, 2012 Moderators Share Posted September 13, 2012 I guess I am unsure what you mean by saving an entire row; you take each index in the array and write it to a single cell. Are you saying you would rather write them all in one row, rather than in one column? "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...
Melfritch Posted September 14, 2012 Author Share Posted September 14, 2012 Lets say that excel row is like this : ___________________________________ │Arkon │arkon products │2.99$│22/02/12 │ at the moment the script only saves ''Arkon'' in the excel file named a.csv, i need it to save the entire row : │Arkon │arkon products│2.99$│22/02/12│ Link to comment Share on other sites More sharing options...
Moderators JLogan3o13 Posted September 14, 2012 Moderators Share Posted September 14, 2012 Are you gathering all of those pieces of info from the other spreadsheet? If so, you need to understand that when you read the sheet to an array each piece of info will be a different element in the array. You would have to code the logic that puts "arkon products", "2.99$", and "22/02/12" on the same row as the "Arkon" element. If you need to read to read your original sheet row by row so all that information stays together, we'll have to go about it a different way. "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...
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