webquery?

abound1

Board Regular
Joined
Aug 8, 2005
Messages
84
Hi,

is there anyone here that knows how to automate a web query whereby names on a list in Excel are pasted into a website’s text box search and the returned data is copied into Excel? I have a list of racehorses that I need data for which is available at: http://www.drf.com/workoutHorseSearch.do

Thanks, I appreciate any help with this.
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
thanks Nate,

but I don't know enough VBA to know how to adapt your code to my needs. Can it be modified to automatically work down a list of names, copy each name from Col A into the website text box and copy/paste the returned data into Excel starting say in Col B, adjacent to each corresponding searched name.

Can you please provide some guidance as to how or what to change in the code to facilitate the above set of tasks?

Thanks again :)
 
Upvote 0
It needs to be heavilly modified, all of the names of the controls in the form need to be flipped based on the source of your website.

You can use the concepts though. I provide more details on this here:

http://www.mrexcel.com/board2/viewtopic.php?p=868291#868291

You want to think about like manipulating ActiveX controls in a Userform, you need to have the correct names of those controls in the form. You need to look at the html source to figure out what these are.

Instead of passing a string, you can pass a Range's Value, e.g.,

.Control1.Value = Range1.Value

Learning to code in VBA by interfacing IE with Excel is like learning how to swim in the deep end.

Can you provide an example of a horse to be searched on and what kind of data is returned?
 
Upvote 0
Swimming in the deep end – good analogy. I’m barely able to dog-paddle, in the shallow end, haha. Here are a few names: Folklore, Point Given, Sensation. I selected these names to illustrate a potential problem that may occasionally arise which I hope won’t be too much of trouble – that is, some horses have the same names. But they will of course have differing birth dates and sire & dam, and countries of origin (3-letter code after the name). The search returns the following 5 data fields: (original horse name), born, sex, sire and dam. These 4 fields are what need to go into Excel next to each name, ie. in col B - E.

Your help with this is really appreciated, Nate
 
Upvote 0
can anyone help me figure this out?

Hi,

is there anyone that can help me figure out how to do an automated web query whereby data in col A is used in a website search and the resultant data is copied into Excel in col B, C, D...? I was unable to find any info on this, so I would really appreciate any help from those who know how to do it.

Thanks a lot!
 
Upvote 0
You will need to modify this....
--------------------------
Sub I_GetWebData()
'
' Macro recorded 11/20/2005 by
'Open site if it requires a PassWord Model or field input validation

Dim ie As Object

Set ie = CreateObject("InternetExplorer.Application")
ie.Visible = True

'Go to this Web Page: enter below
ie.Navigate "www.yourwebpage.com"
'Check for good connection to web page loop!
Do
If ie.ReadyState = 4 Then
ie.Visible = False
Exit Do
Else
DoEvents
End If
Loop

'Wait for window to open!
Application.Wait (Now + TimeValue("0:00:03"))
'MsgBox "Done"
ie.Visible = True
AppActivate IE.LocationName & " - Microsoft Internet Explorer"
'Send date/info based on Cell A1!
SendKeys "{TAB}", True
SendKeys "{TAB}", True
'May need additional SendKeys as needed?
'go to webpage and count how many times you must hit tab to get
'to the right "box" where your info would be typed in..
'Determine by the actual key-stroks needed!
SendKeys Sheets("nameofyoursheet").[a1].Value, True
SendKeys "{TAB}", True
SendKeys "{ENTER}", True
'add tabs if needed...
Application.Wait (Now + TimeValue("0:00:03"))
With ie
.Visible = True
DoEvents

.ExecWB 17, 2
.ExecWB 12, 2
Sheet2.Paste Sheets("nameofyoursheet").Range("A1")
End With
--------------------------------

Good Luck,
Sprucy
 
Upvote 0
Thanks a lot for the code, Sprucy!

I made the necessary changes and ran the code, and although it works without generating any errors, it does not do what I need it to do, which is to use each of the values in col A as inputs in the web page and then parse only a select few fields from the returned data into col B. The way it is now, the whole web page is pasted into Excel, without having performed the searches.

Could you please give me some guidance as to how to change to code to have it perform the above tasks.

Thanks again! I really apreciate your help. :)
 
Upvote 0
Hey again,

I know my code isn't perfect, but that's because I'm new at this.
My code depends on 1 value only, yours, a whole range!
Also, I was unable to specify which web "Table" to copy, so I had to copy the whole page, add a second macro to copy from worksheet1(results from Web) to worksheet2(Finish stage) the exact stuff I wanted.
In my case, Rows 1 to 8 were pictures & Web headers, my data starts in A9:D2000.

Good luck.
Sprucy
 
Upvote 0
Hello again,

Sprucy, don't use Sendkeys here, it requires focus and isn't a very nice experience for the end-user. Still, nice work in trying to help. :)

The example I provided was a good one, I followed it myself with the following, which is one [and a pretty good] way to do this:

Code:
Private Sub horseWebQuery()
Dim ie As Object, Table As Object
Dim tblRow As Object, tblCell As Object
Dim strArr() As String, i As Long, j As Long, f As Long
Dim tmpArr() As Variant

If Not CBool(Len(Sheet1.Range("a2").Value)) Then _
    Exit Sub

Application.StatusBar = "Please Wait: Retrieving Web Query Results..."
Application.ScreenUpdating = False

With Sheet1
    Let tmpArr = .Range(.Range("a2"), _
        .Range("A65536").End(xlUp).Item(2)).Value
End With

On Error GoTo errHandler

Set ie = CreateObject("InternetExplorer.Application")

ie.navigate "http://www.drf.com/workoutHorseSearch.do"
Do While ie.busy: DoEvents: Loop
Do While ie.ReadyState <> 4: DoEvents: Loop

Sheet1.Range("b1:f1").Value = Array( _
    "Horse Name", "Born", "Sex", "Sire", "Dam")

Sheet1.Range("B2:F65536").ClearContents

For f = LBound(tmpArr, 1) To UBound(tmpArr, 1)
    If CBool(Len(tmpArr(f, 1))) Then
        Let i = 0:  Let j = 0
        With ie
            .document.Forms("HorseSearchForm").Name.Value = tmpArr(f, 1)
            .navigate "JavaScript:if (fnValidate()) document.HorseSearchForm.submit();"
            Do While .busy: DoEvents: Loop
            Do While .ReadyState <> 4: DoEvents: Loop
            
            Set Table = .document.all.tags("table").Item(13)
            
            ReDim strArr(1 To Table.Rows.Length - 2, 1 To 5)
            
            For Each tblRow In Table.Rows
                Let j = 1:  Let i = i + 1
                If i > 2 Then
                    For Each tblCell In tblRow.Cells
                        strArr(i - 2, j) = tblCell.innerText
                        Let j = j + 1
                    Next tblCell
                End If
            Next tblRow
        
        End With
        
        Sheet1.Range("b65536").End(xlUp).Item(2, 1).Resize( _
            UBound(strArr, 1), 5).Value = strArr
    End If

Next

With Sheet1
    .Columns("B:F").AutoFit
    .Columns("C:C").TextToColumns Destination:=.Range("C1"), _
        DataType:=xlDelimited
End With

errHandler:
    ie.Quit: Set ie = Nothing
    Application.StatusBar = False
    Application.ScreenUpdating = True
    If Err.Number <> 0 Then
        MsgBox "Error: " & String$(2, vbLf) & _
            Err.Number & String$(2, vbLf) & _
            Err.Description
        Err.Clear
    End If
End Sub
This assumes that the list is in A2:Axxx of Sheet1. Your bottleneck is your connection to the site... You can multi-task with it, but I'm not sure if I would be navigating with another IE instance while it's running, it's pretty intensive... And note, I only tested this against the horses you mentioned...

Merry Christmas. :)
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top