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]

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

Well, time might be a problem, but I won't forget about you. Just don't get your hopes up. I would be pleasantly surprised if I can cook up something that will actually work for you without being able to test things out on my own. You're in my inbox, succeed or fail.

 

[Legacy 456155]

Here's the first attempt. If it works, it will only process the first row of data. If we make progress, I'll help you edit the macro for multiple rows of data and possibly further steps to fully automate your sequence. When using SendKeys, which is, as I said before, a last resort, avoid using keyboard shortcuts to fire up your code. Use a button or the mouse.

Open your Lotus form to the default starting position.
Run "Sub DontHoldYourBreath()"

I noticed that some of the range address are repeated in your sequence. K, AG, AL. Was that intended?

Sub DontHoldYourBreath()
    Dim op As Byte, ra As String
    AppActivate "(Untitled) - IBM Client Application Access"
    Application.Wait Now + TimeValue("00:00:01") 'wait to clear keyboard buffer
    For op = 1 To 18
        ra = Choose(op, "AG1", "AH1", "J1", "K1", "AI1", "K1", "AI1", _
            "K1", "L1", "AG1", "AJ1", "AK1", "B1", "AL1", "AM1", "M1", "AN1", "A1")
        Range(ra).Copy
        Select Case op
            Case 1: tp 0 'just paste
            Case 2, 18: tp 5 'items 2 and 18 preceeded by 5 tabs
            Case 3, 17: tp 3
            Case 4: SendKeys "{ENTER}", True: tp 0
            Case 5 To 14: tp 1 'items 5 to 14 preceeded by 1 tab
        End Select
    Next
End Sub

Private Sub tp(cnt) 'tab 'n paste
    Do Until cnt = 0
        SendKeys "{TAB}", True
        cnt = cnt - 1
    Loop
    SendKeys "^v", True 'CTRL-V paste
End Sub