CODEWITHSUNDEEP
vbaexcelclipboardcopy-paste
orangehairbandit
orangehairbandit

Reputation: 323

copyshortcut feature lag/behind

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`

Upvotes: 1

Views: 240

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149277

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

Upvotes: 2

Related Questions