Seminko

[3 in 1] Excel + Array + StringRegExp

18 posts in this topic

#1 ·  Posted (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 by Seminko

Share this post


Link to post
Share on other sites



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 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Second:

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#4 ·  Posted (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 by jguinch

Share this post


Link to post
Share on other sites

#5 ·  Posted (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 by jguinch

Share this post


Link to post
Share on other sites

Great solution for problem #2 :thumbsup:


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

 

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.

Share this post


Link to post
Share on other sites

#9 ·  Posted (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 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.

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

#11 ·  Posted (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 by jguinch

Share this post


Link to post
Share on other sites

#12 ·  Posted (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 by Seminko

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#17 ·  Posted (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 by mikell

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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 account

Sign in

Already have an account? Sign in here.


Sign In Now