Jump to content
nooneclose

[SOLVED] How to update an array after storing an Excel Column then changing it.

Recommended Posts

nooneclose

Hey. I'm working on a new project and was wondering if there is a better way to "update" my Column E array. 

Here is my code: 

Local $nI  = 0                                                            ;Creates a name index of 0: nI = Name index
Local $nII = 1                                                            ;Creates a name index of 1 for second loop: nII = Name Index 2

For    $iN = 0 To $IndexRows Step 1                                       ;Checks the roster for any names that appear twice
     For $iN2 = 0 To $IndexRows Step 1
         if $d_Names[$nI] == $d_Names[$nII] And $d_Names[$nII] <> "" Then

             Local $timeSheetName = _ArraySearch($e_Names, $d_Names[$nI], 0, 0, 0, 0, 1)
             ;MsgBox($MB_SYSTEMMODAL, "Found it", $d_Names[$nI] & " In column E on Row " & $timeSheetName)

             Local $eI  = $timeSheetName + 1

             ;ConsoleWrite($timeSheetName & @CRLF)
             ;ConsoleWrite($eI & @CRLF)
             ;ConsoleWrite(@CRLF)


             _Excel_RangeInsert($OpenWorkbook.ActiveSheet, "E" & $eI & ":F" & $eI, $xlShiftDown)
                                                                          ;Inserts a empty cell in columns E and F.
             _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $d_Names[$nII], "E" & $eI)
                                                                          ;Fills the empty cell in columns E with the doubled name

             $aArray_Index = 2                                            ;Array element counter

             For $Index = 2 To $IndexRows Step 1                          ;Loops through every row in the Excel file unto no rows are found or a null row is found
                 $Array_Value_E = _Excel_RangeRead($OpenWorkbook, Default, "E"&$Index)
                 $e_names[$aArray_Index] = $Array_Value_E                 ;While the code loops every value in column E is stored in the E array (updating the array)

                 $aArray_Index += 1
             Next

             ExitLoop
         EndIf

     Next

     $nI  += 1
     $nII += 1
Next

Basically, It checks a roster for people whose name appears twice then inserts a new "row" for that person because they work in two different departments.

I have to find that name however in Column E if two appear in column D. My code works but I think it is not as efficient as it could be. 

Any ideas on how to improve the "update" for my array?

(once it finds the double names in Column D it then searches for that name by going name by name in the Column E array and once it finds it inserts a new row. However, the E array doesn't have that new row stored in it so I have to "update" the array to properly find the next name)

Any and all tips would be greatly appreciated. 

 

NOTE: Just assume I'm opening the excel file properly please do not add that code in, it only complicates your answer. 

Edited by nooneclose

Share this post


Link to post
Share on other sites
water

Do not use _Excel_RangeRead to loop through column E, use _Excel_RangeFind and search column E for the name.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
nooneclose

@water Thank you! I will try that and get back with you. 

Share this post


Link to post
Share on other sites
nooneclose

@water I tried using 

$Array_Value_E = _Excel_RangeFind($OpenWorkbook, $d_Names[$nII], "E1:E176")

but I can't seem to get this function to work. It doesn't find the value that I'm looking for. 

Share this post


Link to post
Share on other sites
water

More information please. What is the return value and the value of @error and @extended after calling _Excel_RangeFind?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
nooneclose

@water  I was able to figure it out on my own. thank you again for guiding me to the proper function.

This is what I'm using and it works at least 20 times faster.  (my code basically runs instantly now)

For $iN = 0 To $IndexRows Step 1                                        ;Checks the roster for any names that appear twice
     For $iN2 = 0 To $IndexRows Step 1
         if $d_Names[$nI] == $d_Names[$nII] And $d_Names[$nII] <> "" Then
             Local $timeSheetName = _Excel_RangeFind($OpenWorkbook, $d_Names[$nII], "E1:E" & $IndexRows)
             Local $eI = $timeSheetName[0][2]
             Local $Cell_Number = StringSplit($eI, "A, B, C, D, E, F, G, H, $")
             Local $refined_number = $Cell_Number[4] + 1
            
             _Excel_RangeInsert($OpenWorkbook.ActiveSheet, "E" & $refined_number & ":F" & $refined_number, $xlShiftDown)
                                                                        ;Inserts a empty cell in columns E and F.
             _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $d_Names[$nII], "E" & $refined_number)
                                                                        ;Fills the empty cell in columns E with the doubled name
             ExitLoop
         EndIf
     Next
     $nI  += 1
     $nII += 1
Next

 

Share this post


Link to post
Share on other sites
water

Glad you got it working ;)


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-10-19 - Version 1.4.10.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

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

  • Similar Content

    • SharpDressedMan
      By SharpDressedMan
      I have an array of subarrays, eg:
      local $a[3] = [ 1, 2, 3 ] local $b[3] = [ 4, 5, 6 ] local $c[2] = [ $a, $b ] I can read read a subarray element of the array, as follows:
      local $c12 = ($c[1])[2] ; result: $c12 = 6 However, when I try to set a subarray element as follows, it fails:
      ($c[1])[2] = 12 ; this fails to set subarray element local $c12 = ($c[1])[2] ; result: $c12 = 6 How to set a subarray element ?
      Thanks
    • nooneclose
      By nooneclose
      Good morning everyone, I thought I had already solved this issue but it turns out I did not. 
      My code finds unread emails with this specific subject line of "request" but when I change the subject to SKIPPED + "request" = ("SKIPPED request") the program still finds the email and tries to process it. I only want to process emails with the exact match subject of "request".
      Here is my code that "finds" the unread emails with the subject of "request" or so I thought. 
      Func ListUnreadEmails() ;******************************************************************************* ; Lists all unread E-mails from the folder Outlook-UDF-Test ;******************************************************************************* ; Stores all the unRead emails into an array Global $aItems = _OL_ItemFind($oOutlook, "*\Outlook-UDF-Test", $olMail, _ "[UnRead]=True", "Subject", "request", "EntryID,Subject", "", 1) ; Displays the array of unRead emails If IsArray($aItems) Then ;_ArrayDisplay($aItems, "OutlookEX UDF: _OL_ItemFind - Unread mails") Else MsgBox(48, "OutlookEX UDF: _OL_ItemFind Example Script", _ "Could not find an unread mail. @error = " & @error & ", @extended: " & @extended) EndIf ; Gets the number of unread emails Global $numberOfUnRead = UBound($aItems, $UBOUND_ROWS) - 1 ;MsgBox("", "Number of Unread emails", $numberOfUnRead) EndFunc It acts as if any part of the subject containing the word "request" and the email is unread that it will try to process it. (I think)
    • KhalidAnsari
      By KhalidAnsari
      Hi,
      I need to automate Java application using AutoIt, I have refer forum for the same, as per forum I am using Java Access Bridge to finding control name. But could not set value to control. Below is my sample code 
      Send("#r")
      sleep(1500)
      Send("D:\javaapplication\javaapplication\Hello.jar{Enter}")
      WinActivate("Penjumlahan")
      sleep(1500)
      ControlSetText("Penjumlahan", "", "Name:  A :", "demo")
      Java Application and Java Ferret screen

    • QaisBsharat
      By QaisBsharat
      Hello, 
      May i know how can i automation qt application?
      Using controlclick not mouse and positions
       
      Thanks
    • nooneclose
      By nooneclose
      Good afternoon, I am in need of some help. I am sure this is a stupid question requiring only one or two lines of code. However, I would greatly appreciate the help I cannot figure this out. I also tried searching for the answer on the internet but no one except me apparently seems to be having a hard time figuring this out and or is asking about it. 
      I simply want to change the "status" of an email from unread to read once I have processed it. My code is over 500 lines and I would like not to clutter this post with it. Assume I have all my includes and connections properly defined and stuff. 
      Here is the bit of code where I am trying to change the email that was used from unread to read:
      Func ChangeEmailStatus() ;******************************************************************************* ; changes the status of an email from unread to Read ;******************************************************************************* Local $iRows = UBound($aItems, $UBOUND_ROWS) MsgBox("", "Number of Unread emails (Before Change)", $iRows) _OL_ItemModify($oOutlook,$aItems[$i][0], Default, "Read=True") MsgBox("", "Array Display 1", $aItems[1][0]) MsgBox("", "Array Display 2", $aItems[2][0]) Local $iRows = UBound($aItems, $UBOUND_ROWS) MsgBox("", "Number of Unread emails (After Change)", $iRows) EndFunc  
×