microsoft excel vba copying data from webpage

Question

through vba - I need to go to the page , then select option "Hawb/direct awb no." in Track by drop down, enter a cell copied from excel into "Number" field and then click "submit" button.

how can i do it? I am using excel 10 and IE 10. I tried variety of commands like below but no success :(

ie.document.getElementById("ctl00_contentPlaceHolderRoot_cboTrackBy").Focus                            
ie.document.getElementById("ctl00_contentPlaceHolderRoot_cboTrackBy").Click
ie.document.getElementById("ctl00_contentPlaceHolderRoot_cboTrackBy").Value = "aw"
ie.document.getElementById("ctl00_contentPlaceHolderRoot_cboTrackBy").FireEvent ("onchange")
ie.document.getElementById("ctl00_contentPlaceHolderRoot_txtTrackBy").Value = b 'ctl00_contentPlaceHolderRoot_linkButtonSubmit
ie.document.getElementById("ctl00_contentPlaceHolderRoot_linkButtonSubmit")(0).Click

update:

I followed ron's answer below. I want to copy tables that i get once ron's code finishes it's processing. I used tracking number - PEN91227308

I tried below commands. But the data that it copies is not in a table format. When i paste it in excel entire row appears on a single cell. How could i convert that data into table format as on the webpage?

Dim clipboard As MSForms.DataObject
Set clipboard = New MSForms.DataObject
clipboard.PutInClipboard
clipboard.SetText Doc.getElementById("ctl00_contentPlaceHolderRoot_grdVwHistory").innerHTML

You could try out [selenium](http://www.seleniumhq.org/download/), as was [recommended to me](http://stackoverflow.com/questions/20662625/excel-vba-query-external-aspx-page-and-retrieve-data) — Raystafarian, Apr 01 '14 at 16:11
hhh..need some help. I installed the excel plugin and then open firefox and then selenium but nothing seems to work :( any help ? how to record a script....also i went to excel and "Dim driver As New SeleniumWrapper.WebDriver" but i am getting an error :( I have installed excel wrapper only — user2543622, Apr 01 '14 at 16:47

score 1 · Accepted Answer · answered Apr 01 '14 at 17:58

The elements you are trying to interact with are contained within an IFrame. The source code shows that the IFrame can be found at

   <iframe name="Shipment Tracking" width="100%" height="450" id="content_iframe" src="http://apps.dbschenkerusa.com/apps/Tracking/" frameborder="0" scrolling="auto">

At that url, you can use the following code to 1) select the correct drop-down box entry, 2) enter your numeric value in the "Number" text box and 3) click the "Submit" button

Sub test1()
' open IE, navigate to the website of interest and loop until fully loaded
    Set ie = CreateObject("InternetExplorer.Application")
  '  my_url = "http://www.dbschenkerusa.com/log-us-en/start/eschenkerusa/shipmenttracking.html"
    my_url = "http://apps.dbschenkerusa.com/apps/Tracking/"

    With ie
        .Visible = True
        .navigate my_url
        .Top = 50
        .Left = 530
        .Height = 400
        .Width = 400

    Do Until Not ie.Busy And ie.readyState = 4
        DoEvents
    Loop

    End With

' Select the appropriate item from the drop-down box
    ie.Document.getElementById("ctl00_contentPlaceHolderRoot_cboTrackBy").Value = "aw"

' Enter a value in the "Number" text box
    ie.Document.getElementById("ctl00_contentPlaceHolderRoot_txtTrackBy").Value = "1234"

' Click the submit button
    ie.Document.getElementById("ctl00_contentPlaceHolderRoot_linkButtonSubmit").Click

End Sub

microsoft excel vba copying data from webpage

1 Answers1