Sending array value, adding extra space

[Ibet]

Hi all, 

 

Fairly new to AutoIt, Day 2. I promise I've done my due diligence in searching forums, Google and the all-powerful help file. Just not sure if I'm searching by the right terminology.

I am needing to run a find command in an excel document, then put text into the text field of the Find and Replace window. I'm using an array[3] to store 3 pieces of information from one document, and switch to the other document to search. Because of sensitive information, I will include a sterile snippit of my process below.
 

Func SearchValue($Entry)
   WinActivate ("Dest.xlsx - Excel")
   WinWait ("Dest.xlsx - Excel")
   Send("^f")
   Sleep(100)
   Send($Entry[0])
   Sleep(100)
   Send("{ENTER}")
   Send("{RIGHT}")
   Send($Entry[1])
EndFunc

I've been able to verify (using MsgBox) that the text from document 1 is being captured into the array and that the SearchValue function is receiving the correct values in the array. The problem I'm having is that it seems to be adding a space (or maybe return) character at the end of the text. So, when I force excel to find/match the text explicitly, it doesn't match due to the extra invisible character at the end. Is this because of something in the array storage?

I prefer to stay away from the excel specific commands included in Excel.au3 until I have more time to practice with them and learn to use them properly. I've attempted to use ConsoleWrite but it seems Excel doesn't like STDOUT, as well as various other methods of writing text to a file without closing/saving it.

Any suggestions? What am I missing?

Thanks in advance.

[Ibet]

Was also hoping to avoid any kind of ToString steps for simplicity. However, if it's the only way to make it work like I need it to, I can do that.

[iamtheky]

look at the excel UDF, it will make all of this much easier.

[Ibet]

4 minutes ago, iamtheky said:

look at the excel UDF, it will make all of this much easier.

I've looked through it a bit before posting, and have mainly stayed away from it because I haven't wanted to define a cell location when writing a value. Have been using keystrokes. Given, I know it's more tedious and less efficient, until I felt comfortable with those commands I've been keeping it very basic. Having looked at it again, I can see that defining a cell is an optional parameter but if you don't define a cell, does it default to A1? When I read the UDF, I interpret it as defaulting to A1.

 

Also, if _Excel_RangeWrite writes to a cell, will it actually put the text into the Find/Replace box that is already open?

[Subz]

When adding your text to the array, use something like the following, assuming you're using clipboard to get your data from the first Excel spreadsheet.

_ArrayAdd($Entry, StringStripWS(ClipGet(), 3))

As iamthekey mentioned it would be a lot cleaner if you used Excel udf, you can add prompts between each action to determine where you are going to paste the data.

[iamtheky]

5 hours ago, Subz said:

can add prompts between each action to determine where you are going to paste the data

Moreover, you can do anything the object can do once you start playing over there, not just limited to the functions in the pretty wrapper.  activecell.offset()  becomes an option if you just want to paste relative to other things.

Edited January 30, 2019 by iamtheky

[Ibet]

Fantastic suggestions from the both of you. Thank you. I will make some adjustments this morning, pretty sure I can get it to work with that information.

[Ibet]

Alright, got it to work using the StringStripWS as seen below. I didn't use _ArrayAdd because that adds to the end of the array and I'm defining specific spaces for specific information on each iteration. So, it looks like this in my capture function: (for reference)

expandcollapse popup

Func GetValues(ByRef $Entry)
   ;--Code here to get the values from spreadsheet and store in array
   WinActivate ("SOURCE.xlsx  [Read-Only] - Excel","")
   WinWait ("SOURCE.xlsx  [Read-Only] - Excel","")
   Send("{DOWN}")
   Sleep(100)
   Send("^c")
   Sleep(500)
   $Entry[0] = StringStripWS(ClipGet(),2) ;-- Store Name in array slot 0, clip any whitespace at the end
   Sleep(100)
   Send("{RIGHT}")
   Sleep(100)
   Send("{RIGHT}")
   Sleep(100)
   Send("{RIGHT}")
   Sleep(100)
   Send("^c")
   Sleep(500)
   $Entry[1] = StringStripWS(ClipGet(),2)  ;-- Store priority in array slot 1, clip any whitespace at the end
   Sleep(100)
   Send("{RIGHT}")
   Sleep(100)
   Send("^c")
   Sleep(500)
   $Entry[2] = StringStripWS(ClipGet(),2)  ;-- Store status in array slot 2, clip any whitespace at the end
   Sleep(100)
   Send("^{LEFT}")  ;-- Return to left column for next iteration
EndFunc

Func PutValues($Entry)
   ;--Code here to put the values into spreadsheet from array
   WinActivate ("DEST.xlsx - Excel","")
   WinWait ("DEST.xlsx - Excel","")
   Send("^f")
   Sleep(100)
   Send($Entry[0])
   Sleep(100)
   Send("{ENTER}")
   Sleep(100)
   Send("{ESC}")
   Sleep(100)
   Send("{RIGHT}")
   Sleep(100)
   Send($Entry[2])
   Sleep(100)
   ColorCell()
   Send("{RIGHT}")
   Sleep(1000)
   Send($Entry[1])
   Sleep(100)
   ColorCell()
EndFunc

I think next, I'll be adding a function to check if the value in the existing destination cell is the same as the value from the source cell, and coloring it a different color if it's different. Since I'm going to be doing this on about 4000 rows in excel, are there any precautions I should take with cycling those values in the array or will it cleanly overwrite the pre-existing value every time?