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.
 

abound1

Board Regular
Joined
Aug 8, 2005
Messages
84
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 :)
 

NateO

Legend
Joined
Feb 17, 2002
Messages
9,700
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?
 

abound1

Board Regular
Joined
Aug 8, 2005
Messages
84
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
 

abound1

Board Regular
Joined
Aug 8, 2005
Messages
84
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!
 

Sprucy

Board Regular
Joined
Oct 31, 2005
Messages
92
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
 

abound1

Board Regular
Joined
Aug 8, 2005
Messages
84
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. :)
 

Sprucy

Board Regular
Joined
Oct 31, 2005
Messages
92
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
 

NateO

Legend
Joined
Feb 17, 2002
Messages
9,700
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. :)
 

Forum statistics

Threads
1,078,471
Messages
5,340,514
Members
399,381
Latest member
impurnasekar

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top