Seminko Posted February 6, 2015 Share Posted February 6, 2015 (edited) Hey fellas, First question: I have not found an option for _Excel_SheetCopyMove to copy and paste a tab as the last one. I tested the following but found that when there are more than 20 sheets, it takes the script a really long time: $SheetList = _Excel_SheetList($oWorkbook) $SheetListCount = UBound($SheetList) Local $oCopiedSheet = _Excel_SheetCopyMove($oWorkbook, 4, Default, $SheetListCount, False) Is there an easier way for the script to paste the tab as the last one? Second question: How do I make items in an array unique? Let's say I have this: Local $arr[6] $arr[0] = "Earth" $arr[1] = "Fire" $arr[2] = "Fire" $arr[3] = "Earth" $arr[4] = "Water" $arr[5] = "Air" But I need this: Local $arr[6] $arr[0] = "Earth" $arr[1] = "Fire" $arr[2] = "Fire(2)" $arr[3] = "Earth(2)" $arr[4] = "Water" $arr[5] = "Air" And with every other unique name the number in brackets would obviously increase by one. Third question: StringRegExp still goes beyond me. What I need is for StringRegExp to discard any of these characters from a string: [ ] * / ? : and if there is a space before or after the character replace those just with a single space. For example: Earth / Fire --> Earth Fire Wind * :? Water --> Wind Water . . But If there were no spaces and just the special character I would like to replace it with a space For example: Water*Fire --> Water Fire . . Don't know if this is possible but if the special character would happen to be at the end of a word, no space would be needed. For example: Water / Fire * --> Water Fire Fire * Air : Earth*** --> Fire Air Earth . . Thanks, Seminko Edited February 6, 2015 by Seminko Link to comment Share on other sites More sharing options...
water Posted February 6, 2015 Share Posted February 6, 2015 First: Set Parameter $bBefore to False. $bBefore [optional] The copied/moved sheet will be placed before $vTargetSheet if True, after it if False (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 February 6, 2015 Share Posted February 6, 2015 Second: Is the sequence of the elements important or could they be sorted? 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...
Solution jguinch Posted February 6, 2015 Solution Share Posted February 6, 2015 (edited) For the second question : #Include <Array.au3> Local $arr[6] $arr[0] = "Earth" $arr[1] = "Fire" $arr[2] = "Fire" $arr[3] = "Earth" $arr[4] = "Water" $arr[5] = "Air" Local $iCount Local $sElems = $arr[0] & @CRLF For $i = 1 To UBound($arr) - 1 $sElems &= $arr[$i] & @CRLF $iCount = UBound( StringRegExp($sElems, "(?i)\Q" & $arr[$i] & "\E\R", 3)) If $iCount > 1 Then $arr[$i] &= "(" & $iCount & ")" Next _ArrayDisplay($arr) Edit : This method only works if the strings don't contain any carriage return Edited February 6, 2015 by jguinch Spoiler Network configuration UDF, _DirGetSizeByExtension, _UninstallList Firefox ConfigurationArray multi-dimensions, Printer Management UDF Link to comment Share on other sites More sharing options...
jguinch Posted February 6, 2015 Share Posted February 6, 2015 (edited) For the third : Local $aStrings[2] = [ "Earth / Fire", "Wind * :? Water"] For $i = 0 To UBound($aStrings) - 1 $sNewString = StringRegExpReplace($aStrings[$i], "[\h\Q[]*/\?:\E]+", " ") ; could be [\h\[\]\*\/\\\?\:]+ to ConsoleWrite($aStrings[$i] & " --> " & $sNewString & @CRLF) Next Edited February 6, 2015 by jguinch Spoiler Network configuration UDF, _DirGetSizeByExtension, _UninstallList Firefox ConfigurationArray multi-dimensions, Printer Management UDF Link to comment Share on other sites More sharing options...
water Posted February 6, 2015 Share Posted February 6, 2015 Great solution for problem #2 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...
jguinch Posted February 6, 2015 Share Posted February 6, 2015 Thanks Water... It's an idea to avoid an array-search. Maybe a dictionnary based method could be good to. Spoiler Network configuration UDF, _DirGetSizeByExtension, _UninstallList Firefox ConfigurationArray multi-dimensions, Printer Management UDF Link to comment Share on other sites More sharing options...
Seminko Posted February 6, 2015 Author Share Posted February 6, 2015 First: Set Parameter $bBefore to False. $bBefore [optional] The copied/moved sheet will be placed before $vTargetSheet if True, after it if False (default = True) Sure, but you have to specify the TargetSheet. That's why I had the Ubound function to determine the last sheet. But that's not ideal. Link to comment Share on other sites More sharing options...
jdelaney Posted February 6, 2015 Share Posted February 6, 2015 (edited) Local $arr[6] $arr[0] = "Earth" $arr[1] = "Fire" $arr[2] = "Fire" $arr[3] = "Earth" $arr[4] = "Water" $arr[5] = "Air" Local $aTemp[1][2]=[[$arr[0],1]] For $i = 1 To UBound($arr)-1 $iSubItem = _ArraySearch($aTemp,$arr[$i],0,0,0,0,1,0) If $iSubItem>=0 Then $aTemp[$iSubItem][1] += 1 $arr[$i]&="(" & $aTemp[$iSubItem][1] & ")" Else ReDim $aTemp[UBound($aTemp)+1][2] $aTemp[UBound($aTemp)-1][0] = $arr[$i] $aTemp[UBound($aTemp)-1][1] = 1 EndIf Next _ArrayDisplay($arr) Output: [0]|Earth [1]|Fire [2]|Fire(2) [3]|Earth(2) [4]|Water [5]|Air Another example (same code): Local $arr[6] $arr[0] = "Earth" $arr[1] = "Fire" $arr[2] = "Fire" $arr[3] = "Earth" $arr[4] = "Earth" $arr[5] = "Earth" Local $aTemp[1][2]=[[$arr[0],1]] For $i = 1 To UBound($arr)-1 $iSubItem = _ArraySearch($aTemp,$arr[$i],0,0,0,0,1,0) If $iSubItem>=0 Then $aTemp[$iSubItem][1] += 1 $arr[$i]&="(" & $aTemp[$iSubItem][1] & ")" Else ReDim $aTemp[UBound($aTemp)+1][2] $aTemp[UBound($aTemp)-1][0] = $arr[$i] $aTemp[UBound($aTemp)-1][1] = 1 EndIf Next _ArrayDisplay($arr) output: [0]|Earth [1]|Fire [2]|Fire(2) [3]|Earth(2) [4]|Earth(3) [5]|Earth(4) Edited February 6, 2015 by jdelaney IEbyXPATH-Grab IE DOM objects by XPATH IEscriptRecord-Makings of an IE script recorder ExcelFromXML-Create Excel docs without excel installed GetAllWindowControls-Output all control data on a given window. Link to comment Share on other sites More sharing options...
Seminko Posted February 6, 2015 Author Share Posted February 6, 2015 For the second question : #Include <Array.au3> Local $arr[6] $arr[0] = "Earth" $arr[1] = "Fire" $arr[2] = "Fire" $arr[3] = "Earth" $arr[4] = "Water" $arr[5] = "Air" Local $iCount Local $sElems = $arr[0] & @CRLF For $i = 1 To UBound($arr) - 1 $sElems &= $arr[$i] & @CRLF $iCount = UBound( StringRegExp($sElems, "(?i)\Q" & $arr[$i] & "\E\R", 3)) If $iCount > 1 Then $arr[$i] &= "(" & $iCount & ")" Next _ArrayDisplay($arr) Edit : This method only works if the strings don't contain any carriage return Works great, thx. BTW, what is a carriage return? Link to comment Share on other sites More sharing options...
jguinch Posted February 6, 2015 Share Posted February 6, 2015 (edited) A new line : $arr[5] = "Air" & @CRLF & "Water" In this case, it does not work with my code. So if you use my code, be sure to have simple words in you array. Edit : jdelaney's code is a sure way... Edited February 6, 2015 by jguinch Spoiler Network configuration UDF, _DirGetSizeByExtension, _UninstallList Firefox ConfigurationArray multi-dimensions, Printer Management UDF Link to comment Share on other sites More sharing options...
Seminko Posted February 6, 2015 Author Share Posted February 6, 2015 (edited) A new line : $arr[5] = "Air" & @CRLF & "Water" In this case, it does not work with my code. So if you use my code, be sure to have simple words in you array. Edit : jdelaney's code is a sure way... There will be no line breaks. Thanks EDIT: oooh, CR - Carriage Return, LF - Line Feed Edited February 6, 2015 by Seminko Link to comment Share on other sites More sharing options...
Seminko Posted February 6, 2015 Author Share Posted February 6, 2015 First question solved. I will just reverse the For loop to go backwards. If the third question is far to complicated or even impossible let's just do: replace these chars [ ] * / ? : with a space Link to comment Share on other sites More sharing options...
jdelaney Posted February 6, 2015 Share Posted February 6, 2015 $string = "[ a]s *b / \b ?e :c " $string = StringRegExpReplace($string,"[\[\]\*\/\\\?\:]"," ") ConsoleWrite($string & @CRLF) IEbyXPATH-Grab IE DOM objects by XPATH IEscriptRecord-Makings of an IE script recorder ExcelFromXML-Create Excel docs without excel installed GetAllWindowControls-Output all control data on a given window. Link to comment Share on other sites More sharing options...
water Posted February 6, 2015 Share Posted February 6, 2015 Sure, but you have to specify the TargetSheet. That's why I had the Ubound function to determine the last sheet. But that's not ideal. You do not have to calculate $SheetListCount. $oWorkbook.Sheets.Count should give the same result. 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...
jguinch Posted February 7, 2015 Share Posted February 7, 2015 For the third, did you try my code in >#5 ? Spoiler Network configuration UDF, _DirGetSizeByExtension, _UninstallList Firefox ConfigurationArray multi-dimensions, Printer Management UDF Link to comment Share on other sites More sharing options...
mikell Posted February 7, 2015 Share Posted February 7, 2015 (edited) jguinch, Depending on what can be in the array elements, it may fail e.g. $arr[0] = "0Earth" Anyway it could be done also like this #Include <Array.au3> Local $arr[7] $arr[0] = "0Earth" $arr[1] = "Fire" $arr[2] = "Fire" $arr[3] = "Earth" $arr[4] = "Water" $arr[5] = "Air" $arr[6] = "Earth" Local $iCount, $sElems, $tmp For $i = 0 To UBound($arr) - 1 $tmp = ChrW(9830) & $arr[$i] & ChrW(9830) ; use any character which can't exist in the array elements $sElems &= $tmp StringReplace($sElems, $tmp, $tmp) $iCount = @extended If $iCount > 1 Then $arr[$i] &= "(" & $iCount & ")" Next _ArrayDisplay($arr) Edit: typo Edited February 7, 2015 by mikell Link to comment Share on other sites More sharing options...
Seminko Posted February 8, 2015 Author Share Posted February 8, 2015 Thanks fellas. I will try your solutions for question 3 tomorrow at work. 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