Populating a non-excel form using excel data.

[cdhunt0]

I'm populating an ibm (lotus notes) form for stock items (spares) based off of an excel spread sheet that I've been given. The spreadsheet is over 1500 rows and each row requires a single form be filled out. Ideally, I'd like to arrange all my excel data in the right order, possibly into a single cell if necessary, and copy/paste into the ibm form. The form can most easily be navigated using the tab key to jump from one field to the next. There would even be a couple spots where I would need to tab multiple times. Any suggestions? I managed to manually fill out about 30 forms yesterday, so you can easily imagine that getting this to work can save weeks.

Thanks in advance.

 

[Legacy 456155]

Jeesh! It sounds like it would definitely be worthwhile to automate this. One of my specialties, but unfortunately, I don't have Lotus Notes. I suppose that you have exhausted Lotus' ability to import from Excel? VBA's SendKeys might be an option if this is going to be a one and done project. If this is something you will be doing periodically, I'm not sure if SendKeys would be my first option. I frankly have little to offer at this point and am wondering if I should even post this and kill your unanswered status. Even so, I'm willing to help if I can. Determine the progression of keystrokes that you are able to use to perform a single complete, round-trip operation and post back here with some details. Details will include something along the lines of:

Selected range A1:G1, CTRL-C, activated Lotus Form with caption "Stock Items Entry Form," Tabbed twice, CTRL-V paste, Tab once, hit enter, activated Excel, and so on.

 

[cdhunt0]

I need it to be:

business unit. tab 5 times. account number. tab. description. tab. manufacturer name. tab. manufacturers part number. tab. total in service. machine center. tab. Equipment description. tab. qty needed per occurrence. tab. equipment number. tab. estimated consumption rate. tab. min level. tab. max level. tab. tab. storage requirements. tab 5 times. class.

I have no experience with importing excel to lotus. Thanks for the help so far.

 

[cdhunt0]

Correction. I fell in populating more manually and found that there are 3 tabs between account number and description.

 

[Legacy 456155]

Does your form parse out the contents of the clipboard or must you enter each item separately for each field? In other words, does a single paste fill the entire form? If not, we need to map each cell to each field in your Lotus form.

Example:
Cell A2 -> Business Unit
Cell B2 -> Account Number
and so on.

Also, what is the caption of your Lotus form? The caption is in the title bar at the top of your window.

 

[cdhunt0]

Here is a screen shot. I hope that helps with the information you need. Afraid I don't know all the terms. I think we are going to map each cell to each field. The multiple tabs come in play with some of "click" on option areas and some of the blue areas you can hover over and it tells you how to decide on those areas. We are just tabbing through them.

 

[Legacy 456155]

Thanks for trying to fill in my blanks. This is going involve some more footwork on your end. You will need to decide if it's worth it or not. Without my having your particular application and specifically, your form, using your keyboard, I need to know a complete round-trip, each and every step including variant steps dependent upon the data. From each cell in your worksheet to each field in your form. The reason being, is that at this point, we can try emulating the keyboard to automate your process. However, the process must be absolutely defined. There is no guarantee that it will work.

Example of the first group of controls under "Requestor Information"
Activate Lotus window with caption "(Untitled) - IBM Client Application Access"
If A1 = "Add", press enter, if B1 = "Stock" tab * 3 ELSE tab * 2, press enter
If A1 = "Revise", tab once, press enter, tab * 3
If A1 = ""Obsolete", tab twice, press enter, tab * 2

 

[cdhunt0]

I was thinking I would open the new form manually. When I do, it automatically goes to the business unit field. There really isn't any if's since I'm always adding new items. The "add" is already clicked by default which sets me on the path with the tab sequence listed above. I figured that once the form was populated, I'd manually click the "stock" and the appropriate spare type (normal spare, capital spare, or working capital spare). Of course, I'd sanity check everything quickly before submitting. There is a submit button that wasn't in the screen shot.

 

[Legacy 456155]

At some point, you will need to post the relationships between the data in Excel and your fields.

 

[cdhunt0]

Ok. I think this is what you mean. I mocked it up for row 1 which would obviously change.

Business unit = cell AG1
tab
tab
tab
tab
tab
AFC-1 Number = cell AH1
tab
tab
tab
Detail Description of item = Cell J1.
Enter
Cell K1
tab
manufacturer's name = cell AI1
tab
manufacturer's part number = cell K1
tab
How many (total) in service = cell L1
tab
Machine Center = Cell AG1
tab
Equipment used on = Cell AJ1
Tab
Estimated quantity typically needed per occurrence = cell AK1
TAB
Equipment Number = cell B1
tab
Estimated Consumption Rate (per year) = cell AL1
TAB
Suggested Min/max Min = cell AM1
tab
Suggested min/max max = cell M1
tab
tab
tab
Storage/location/environmental requirements = cell AN1
tab
tab
tab
tab
tab
Tabware class = cell A1
tab


I really appreciate all the time and effort your putting into this.