[3 in 1] Excel + Array + StringRegExp

[Seminko]

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

[water]

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)

[water]

Second:

Is the sequence of the elements important or could they be sorted?

[jguinch]

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

[jguinch]

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

[water]

Great solution for problem #2

[jguinch]

Thanks Water... It's an idea to avoid an array-search. Maybe a dictionnary based method could be good to.

[Seminko]

 

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.

[jdelaney]

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

[Seminko]

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?

[jguinch]

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

[Seminko]

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

[Seminko]

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

[jdelaney]

$string = "[ a]s *b / \b ?e :c "
$string = StringRegExpReplace($string,"[\[\]\*\/\\\?\:]"," ")
ConsoleWrite($string & @CRLF)

[water]

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.

[jguinch]

For the third, did you try my code in >#5 ?

[mikell]

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

[Seminko]

Thanks fellas. I will try your solutions for question 3 tomorrow at work.