Program to copy from excel file, wait and repeat

[Bucks]

I am not exactly sure how to even accomplish this, but I need to create a batch or windows executable that can do the following.

I have a list of ID numbers in excel. I want to copy one id number from excel into another program (based on AS400). Then, in this AS400 shell, a button has to clicked to export a data file (.csv). This process takes about 5 minutes before being complete. This csv file needs to be renamed with a name that is also included in the excel file. Once this iis complete the next ID number can be used and the process can start over.

So I need a way to automate copying the id from excel into AS400, waiting for AS400 to run it's process, renaming the file that is outputted based on a name also inside the excel file, then selecting the next id number and repeating.

Any ideas on where to start? I haven't the slightest on how this may be accomplished. Probably something similar to sendkeys in excel?

 

[Markbnj]

Ah, it's been a long time. There are a few .NET components you can download to open and read excel files. One of those should do it for you, either using C# or VB script (or powershell).

 

[Cogman]

Java, C++, and even ruby have some excel readers. You just need to pick your poison. (Python probably has one too, but I don't know that for sure).

 

[KB]

I would start with the "Record Macro" feature of Excel since you can do the steps, then it will write the code for you to review. The problem is it will have an issue with the SendKeys and waiting and that is where you will need to learn some VBA to tweak the macro.

http://www.dummies.com/how-to/content/how-to-record-a-macro-to-automate-tasks-in-excel-2.html

 

[mv2devnull]

ID number .. into program (based on AS400). Then, in this AS400 shell, a button has to clicked .. csv file needs to be renamed

I don't know what AS400 is, but that "click a button" looks to me as the most scary part. How does one "enter a number and click"?

If it were a CLI program, it could allow with a (number,word) pair to

program fetch number > word.csv

If such feature exists (and does not return before completion),
then it is trivial to export the (ID,name) pairs from Excel to a text/csv file and loop over that list.

 

[mrjminer]

IIRC, Microsoft.Office.Interop.Excel. You can access and do pretty much everything programmatically (create worksheets, workbooks, save, etc.). I had to use it a several years ago for a small project to grab / save data to a couple of cells. Note, of course, that you have to have the appropriate licensing, and I think the server had to Excel installed since it will actually launch Excel to perform the work, or it may have to have the DLL distributed with the program (yea, this was before stuff like VDI, so I don't know what you would have to do for it now). I believe there is an option to have it do so as a process only so as to not expose the Excel GUI (ie: only have it show up in task manager, rather than as a launched program you can interact with). Been a while, but those are some of the tidbits that I may or may not remember correctly 😛

For the AS400 process, you could launch it from the same program and tie in an event, or schedule something with task scheduler when the process closes. IIRC, Task Scheduler API is a C++ COM library, but someone did write a C# wrapper here: http://taskscheduler.codeplex.com/