Excel Help Please!

[Dfields]

First let me say I am new to the forum, and please excuse me if my approach to asking for help is incorrect.

I need some help writing what seems to me to be an easy script, but for the sake of me I can not get it to work. I have been working on a project for a little over two years now and the latest chunk I have been given to accomplish is way more than I can get done manually, really more than I even want to do manually. Here is where I am and what I need help from a script to do:

1. I have created an Excel workbook,the first worksheet is called Index. In the Index worksheet in column A, I have placed 2000 different codes to be used in another program. Each code is unique.

2. I have created worksheets in the same workbook based on each code in the Index worksheet, so I have 2000 tabed worksheets, one for each code in column A in the Index worksheet.

3 I have been manually copying each code from the Index worksheet one cell at a time, pasting it in another program, this program is outside the MS Office environment, pressing enter and that program searches for the code, provides the results, then I have been using copy a to select all the results, moving back to Excel copy v and pasting it back into the respective worksheet that matches the code at which point I move to the second code in the Index worksheet and repeat the process. As you can imagine, this is going to take a long time to accomplish. 

4. I have tried creating a script using AutoIT record to capture mouse/key movements but I need a loop that will process down each cell in the Index worksheet and then to each worksheet that matches the code. Try as I may, I can not get it to work. If it would be possible could someone assist me with this? It will not only save me a HUGE amount of time but will also save my sanity as I am about to go out and play in the middle of the highway over this!

I have done "some" writing of VBA scripts myself and I understand what it is like to be asked from something like this, please forgive me for my seeming laziness it is not, if I could I would take care of it myself. If this were all in the MS Office environment VBA would handle it however, that is not the case. This seems like it would be a simple script to write and it may be for those who fluent in writing code, but my ability's fall short of being able to accomplish this. Thank you for your time and if anyone could provide me with some help it would be GREATLY appreciated.

[water]

I suggest not to automate the Excel GUI by recording mouse movements and key presses.

Use the Excel UDF that comes with AutoIt and automate Excel via COM.

The rewritten version of the Excel UDF that comes with the latest bneta of Autoit has much more functions to do what you want to do.

So:

• Which version of Autoit dow you run at the moment?
• Can you use the latest AutoIt beta (has some other script breaking changes)?

[Dfields]

Water,

Thank you so much for your reply. I have downloaded the newest AutoIT beta, none the less I am stalled. As I mentioned before, my ability's fall short in this area. Any help you or anyone else could provide would be greatly appreciated. I believe that if I could get this script then I would be able to use it in other places in the project I have been involved in for the last couple of years seeing it is always the same type of thing. from Excel to the program. Again thank you for you reply and the direction you have given.

[redpicker]

What is this "other program" and what (how?) do you paste the information you copy from Excel into it?

The reason I ask is that you may be able to interact directly with the controls in that program.  If you can do that, it will be a lot easier than trying to capure key/mouse movements.

Is this "other program" an interface for a database?  If so, can just read its files and not really need the other program?  You might be able to do this in VBA.

To get Help from this forum, post code that you have tried and explain why it isn't working correctly.  That way, we can offer specific suggestions, and you may learn quite a bit along the way.

[Dfields]

Water the other program is called theWord free download, I suppose behind it somewhere is a sql database. All that I have tried is just recording keystrokes and mouse movements and it works, but no way to build a loop that will proceed down the Index column and after getting the results from the other program going to the next tab. The only way I could do it to record every keystroke/mouse movement for each code (this is not code as in program code, it is just text used in the other program for search values). I think the forum is counting my posts and I only have 4-5 left after this one . I do appreciate your help in this matter.

[water]

It was redpicker who asked the last question

I suggest to use the Excel UDF that comes with the latest beta version of AutoIt.

Check function _Excel_RangeRead which could read the index page into an array. Then loop through the array to process each corresponding page.

[Dfields]

Redpicker,

I do apologize for mistakenly replying to Water. I have entered:

#include <Excel.au3>

_Excel_RangeRead(,$oWorkbook[,$oWorksheet = Default [,&vRange = Default [, $oReturn = 1 [,$bForcefunction = False,]]]])

 

I am assuming that I would fill in $oWorkbook with the name of the excel workbook and all the other specific information for this between the [, ], would that be a correct assumption? I am sure you guys are having a great chuckle, and I don't blame you!

[water]

If you have a look at the example script _Excel_RangeRead.au3 you will see that you need to call the following functions:

#include <Excel.au3>
$oExcel = _Excel_Open() ; To start up Excel or to connect to a running instance
$oWorkbook = _Excel_BookOpen($oExcel, $sFilePath, True) ; To open the Excel workbook specified in $sFilePath in read-only mode
$aData = _Excel_RangeRead($oWorkbook, 1) ; Reads the whole worksheet with index 1 into array $aData

Edited April 24, 2014 by water

[Dfields]

I would like to thank you for attempting to assist me in making what I thought would be a fairly simple script. My skills are so wanting in this area that I am unable to proceed with the chore. I will continue with the manual exchange between the two programs, sooner or later (later I am sure lol ) I will finish the task. Thank you again for your help.

[water]

I'm leaving for vacation on Saturday for two weeks.

If you are still interested then I will show you how to do it with the Excel UDF. It will be much more reliable and faster compared to automating the Excel GUI.

[Dfields]

Water,

That would be great! As I said this part of a much larger project and any time I can save would be very helpful and if I can use the script again for basically the same procedure it will in the end save so much more time. 

[water]

Could be something like this (currently no error checking and some code needs to be added to interact with the other application. marked with "<==="):

#include <Excel.au3>
$oExcel = _Excel_Open() ; Start Excel or connect to a running instance
$oBook = _Excel_BookOpen($oExcel, "C:\temp\test.xlsx") ; Open the Workbook
$aIndex = _Excel_RangeRead($oBook, "Index", "A") ; Read column A from the sheet named "Index"
For $i = 0 To UBound($aIndex) - 1
    If $aIndex[$i] <> "" Then ProcessSheet($aIndex[$i])
Next

Func ProcessSheet($iSheetCode)
        Local $oSheet = $oBook.Sheets($iSheetCode)
        ; Send the sheet code to the other program, copy the data to the clipboard: Code needs to be inserted here <====
        _Excel_RangeCopyPaste($oSheet, Default, "A1") ; Copies the data from the clipboard to a range starting with cell A1
EndFunc

Edited May 4, 2014 by water