Using AutoIt in Excel

[Kiti]

Hello Everyone!

I have two documents in excel. For easy understanding, I'll suppose in the first one are complete names like Jonathan Smith, Eddie Mackenzie and so on, and in the second there are only the short names like Smith and Eddie. I want to copy the first name from the first cell in the second doc and paste it to the right of the full name. This is what it looks like:

Sleep(2000)

MouseClick('Left', 565, 172, 1, 0)  ;selects the second doc
Send("{Alt}+H+C")                    ;copy the first cell
MouseClick('Left', 72, 172, 1, 0)   ;select the first doc
Send("{Alt}+H+FD+F")                ;get the Find box
Send("^V")                               ;paste
MouseClick('Left', 393, 669, 1, 0)  ;click on the "Find" button
MouseClick('Left', 55, 180, 1, 0)   ;select the first doc
Send("{RIGHT}")                     ;one cell right
Send("{Alt}+H+V+P")              ;paste

But, in the first collumn I have more than one Bob (actually there are 65.000 in the first doc and 5.000 in the second) so, I want to paste Bob to the right of each cell which contains the name "Bob" (first doc). And I don't know how to do this.

My first thought was to find and copy many times till it gets the "No more matches found" error and put the condition to stop if the color of a certain pixel is gray and not white (the error is gray, the sheet is white), so when the error pops up, the pixel changes color and the program knows there aren't any more matches, and return to doc2 for the next name. But unfortunately, I don't know why that error is not appearing... It just starts searching again from top...

The second idea was to use somehow the AutoIt Window Info. Because I saw that if I click Find All in Excel and put the Finder Tool (that crosshair) on the bottom part of the Find window, I can see on the status bar "Value: x cell(s) found, and this also appears on the Summary tab, under the Visible text. And I thought I could get in some way that value, x, and tell the program to search and paste for that x times. But I don't know how.

(The third thing I was thinking (but it's not really related to AutoIt but more to Excel) was a way if I could replace the whole cell containing what I was looking for. e.g. I take "Bob", Ctrl+F, Find All, (Bob Smith, Bob Johnson, Bob Anderson), and replace each of these three cells with Bob, but, as well, I don't know how to replace an entire cell which is containing something I searched for.)

I hope you understood what I am trying to say, sorry for the looong post, and I hope someone will tell me how to tell AutoIt how many times I want it to search and replace a specific word.

Thank you in advance,

Kiti

[Kerros]

I would try Locodarwins Excel UDF

That should take out most of the guessswork for you.

Edited May 28, 2008 by Kerros

[someone]

Your best bet by far is to use ExcelCOM UDF to interact with Excel. I'd recommend reading both sheets to an array and working from there.

Its a lot to learn, but the result will be much much better. Try to learn some of the excel functions (in particular _ExcelReadSheetToArray) and feel free to post back with any questions.

[Kiti]

Ok, I've read all the descriptions in ExcelCOM UDF file, but I'm not quite sure what's helpful or not for this task.

And what can I do next if I've created a 2D array from those columns ?

[Kerros]

Once you've created the array, you can then search through the array, and find the information you are looking for.

or you stated that you wanted to put the two columns together into one sheet, using _ExcelWriteArray you can easily write the array from the second sheet back to the first.