copyshortcut feature lag/behind

Question

i'm writing a macro to go into internet explorer, search and employee number, find the manager from that search, and finally copy+paste it to a cell in the workbook.... however, it seems to always be one behind "copy" behind. By that i mean, if i copy something before running the macro, then run it, what is copied goes in the cell. Yet, i hit run again, and it copies the manager name. it seems to always be one "copy" behind, or selects the second most recent item on the clipboard. Also if i clear the clipbaord before copying and pasting in the coade, i get an error. Why is that?

`Sub Macro1()
'
Dim ie As Object
Set ie = CreateObject("internetexplorer.application") 'start up IE

Dim HWNDSrc As Long
HWNDSrc = ie.HWND   'to setup for focusing internet explorer

ie.Visible = True

ie.navigate "http://url.com/" 'address to find

While ie.Busy   'loop until ie is done loading
     DoEvents
Wend

Call WaitForIE(ie, HWNDSrc, 7) 'to check and make sure ie is done loading

ie.document.getElementById("SSOID").Value = "m1z016p32" 'input into search box

ie.document.getElementById("SSOID").Select
SetForegroundWindow HWNDSrc 'focuses the active application
Application.SendKeys "~"    'enter key
ie.document.getElementById("Advanced").Checked = False  'make sure the advanced box is unchecked


For i = 1 To 200000000      'loop to load the search
i = i + 1
Next i


ie.document.getElementById("Advanced").Checked = False

ie.document.getElementById("SSOID").Select  'focuses the cursor so the tabs will align

SetForegroundWindow HWNDSrc
Application.SendKeys "{TAB 6}"  'tab to location

Application.SendKeys "+{F10}"   'right click on the manger name
Application.SendKeys "{DOWN}"   'goes down to 'copyshortcut'
Application.SendKeys "{DOWN}"

Application.SendKeys "{DOWN}"
Application.SendKeys "{DOWN}"
Application.SendKeys "{DOWN}"
Application.SendKeys "{DOWN}"
Application.SendKeys "{DOWN}"
Application.SendKeys "{DOWN}"
Application.SendKeys "~"        '[presses enter

Windows("Book21").Activate      'workbook o activate
Range("A1").Select          'selects the cell
ActiveSheet.Paste           'past the data



End Sub`

If you are open for a better alternative which doesn't use sendkeys and the clipboard then maybe I can help. The code will interact with the IE object and after it enters the data and navigates to the next page, it simply retrieve the value of the html object into the spreadsheet. — Siddharth Rout, Jul 27 '12 at 18:01
well hte URL requires an SSO and password to log into, so i dont know how much help that will be... — orangehairbandit, Jul 27 '12 at 18:44

score 2 · Answer 1 · edited May 23 '17 at 12:06

Ok here is the logic.

After you navigate to the url, find the HTML objects where you need to enter the SSO and password and then simply pass them values using .Value. I see that you are already doing that.

Next is is to find the Submit button and simulate the click event using .Click instead of Application.SendKeys "~"

Once you reach the destination url, instead of tabbing, find the element ID of the Object and retrieve it's value and write that to the excel file.

You have got most of it covered. All you need to do is instead of using sendkeys, directly interact with the browser element.

You might want to see this. An interesting read which show how to retrieve data to Excel.

html parsing of cricinfo scorecards

how do you call the object using vba?? sorry its taken so long to reply, i am on a trip right now. I do know the object, however the java object is not on the html code page. The object is named javascript:getmngrDtl(123456,"jane","doe") and is displayed as a link saying Jane Doe on a table — orangehairbandit, Aug 02 '12 at 00:16

copyshortcut feature lag/behind

1 Answers1