webquery?

abound1 · Nov 22, 2005

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.

Sprucy · Dec 8, 2005

Talk about timing

Hey NateO,

You replied just in time, I was about to contact you for some help.
As for my macro, most of it came from other posts.

I ran my macro on a different PC today and it didn't work.
Reason: SendKey {TAB}, my code states from the Address bar do 1 TAB and yaddih yaddah...
Well guess what, the IE on this PC has an extra "Search bar" below the Address bar, therefor my 1 TAB lands in the wrong spot!
Instead of landing in the webpage "input box", it lands in the Yahoo search box!
And I thought my code was solid....

I'll try out your macro and see what gives, thanks for you advice and be on the lookout for more Q&A's

Cheers,
Sprucy

NateO · Dec 8, 2005

Re: Talk about timing

Hello,

Sprucy said:
Reason: SendKey {TAB}, my code states from the Address bar do 1 TAB and yaddih yaddah...
Well guess what, the IE on this PC has an extra "Search bar" below the Address bar, therefor my 1 TAB lands in the wrong spot!

Yep, would've failed on my PCs too then, I too have an extra tool bar.

Generally speaking, the Sendkeys Method is a nasty hack, sometimes it seems necessary and is functional, but it should be used a last resort.

You're better off learning how to use IE or the Webbrowser Control, in an Object Oriented manner, for reliable, dynamic control. I've posted examples of this a number of times, here, so you might want to search the site.

I hope the example ~~helps.~~

Sprucy · Dec 10, 2005

Can you Help?

Hey NateO,

Your code works great but I can't seem to getting going with my settings...

I have a private intranet page so I can't post-it, sorry.

What do I need to change from the "HorseWebQuery" to make this work.

My "search" value is in A1, sheet2 (only 1 search needed)
Can I specify which table I want copied into excel and how do I know what the table specs are? Do I need "View Code" info from the webpage?

Based on the search, there will be 3 outcomes:
1) 15 Columns by Variable Rows, about 600 to 1200, varies daily.
2) No Records found
3) Another type of error message.

I want my data to go in B9 if 1 is met. If scenario 2 or 3 occurs, I want a msgbox to say ... try later / verify date (cell A1) ... "OK" button, and Stop Macro at this point.

I'll provide more info, let me know what you need to help me out.

Cheers,
Sprucy.

NateO · Dec 10, 2005

Hello,

I'll provide some hi-level advice and let's see if it helps you to help yourself. Here's an overview of what I do when I develop something like this, it can be more an art than a science:

NPO said:
I generally follow the same steps, start with a similar procedure, check out the source, adjust, remove any error traps, make IE visible, then lay out the goal set, rewrite, research, rewrite, test, etc...
{snip}

Now using the HorseWeb query, here's some specific's:

NPO said:
I got all of the site's relevant info from the source, including the form's name, the textbox's name and the JavaScript function. Yep, navigating on that JavaScript calls the function.

The line that has that validation function isn't what tipped my hand, it's the following source lines:

Form:

Code:

<form name="HorseSearchForm" method="post" action="/workoutHorseSearchResult.do;jsessionid=34022977321134075201094?action=list">

Textbox:

Code:

<input type="text" name="name" maxlength="50" size="22" value="">

What's a little tricky the first time you do this is figuring out which table you want, because they're not labeled and I'm not going to count by reading the source. So, I just loop through all of the tables, scanning some text and using a counter. You don't see this in the final product, because I delete it once I know which table I'm going for... So this code, the way I do it, develops in stages.

How did I know the IE Objects? Well, you could early bind, and have a go with the Object Browser...

In this case I did a lot of searching, reading and testing, (not on Excel, I got that part down for this task...) as I normally do when writing something I'm not as familiar with. Including my example that I linked to in the thread earlier... In fact, my first step was to copy that code into a code module...

That's how I do this/did this, and it varies; it's a fluid, dynamic process.

There is a general reference, and it's to be found at the usual source, MSDN:

http://msdn.microsoft.com/workshop/browser/webbrowser/reflist_vb.asp
{snip}

And, here's an example of how one loops through Tables in an HTML Document:

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

Note the 'For Each Table In cTables' loop... I think the collection is 0-based...

And my last piece of hi-level advice:

NPO said:
What I would add, is that when you're automating IE, I find it easier to make IE visible during the drafting stages to actually watch how IE is responding to your code.

Then, when you know it's up and running, let it run in the hidden background.
{snip}

I do this for two reasons: 1) It allows you to watch your automation 2) If you crash, you can easily terminate the new IE instance.

Have a go with the concepts and examples I've mentioned and see what you come up with. My help to you is going to be very limited as I can't see what you're looking at, or test...

NateO · Dec 11, 2005

I received an interesting PM about the code I've posted to this thread basically asking:

"What happens if I bungle this up with a typo? As is, I am prompted with a error msgbox and the querying stops…"

And this is true, written as is. The PM then had the audacity to explain to me that all I need to do is test for an error, to which I says "Er, no, don’t do that…"

What you want to do, in this case, is test the document's text for the string 'No horse was found with the specified name', which is returned on bunk queries. So I'd rewrite the code as such:'-----------------------------------------------------------------------PrivateSub horseWebQuery()Dim ieAsObject, TableAsObjectDim tblRowAsObject, tblCellAsObjectDim strArr()AsString, iAsLong, jAsLong, fAsLongDim tmpArr()AsVariant, tmpCollAs CollectionDim qryShtAs WorksheetIfNotCBool(Len(ThisWorkbook.Worksheets( _
 Sheet1.Index).Range("a2").Value))ThenExitSubWith Application
 .StatusBar = "Please Wait: Retrieving Web Query Results..."
 .ScreenUpdating =False
 .DisplayAlerts =FalseEndWithOnErrorResumeNext
ThisWorkbook.Worksheets("Horse Errors").DeleteOnErrorGoTo 0Set qrySht = ThisWorkbook.Worksheets(Sheet1.Index)With qrySht
 Let tmpArr = .Range(.Range("a2"), _
 .Range("A65536").End(xlUp).Item(2)).Value
 .Range("B1:F65536").ClearContents
 .Range("B1:F1").Value = Array( _
 "Horse Name", "Born", "Sex", "Sire", "Dam")EndWithOn ErrorGoTo errHandlerSet ie = CreateObject("InternetExplorer.Application")

ie.navigate "http://www.drf.com/workoutHorseSearch.do"DoWhile ie.busy: DoEvents:LoopDoWhile ie.ReadyState<> 4: DoEvents:LoopSet tmpColl =New CollectionFor f =LBound(tmpArr, 1)ToUBound(tmpArr, 1)
 IfCBool(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();"
 DoWhile .busy: DoEvents:Loop
 DoWhile .ReadyState<> 4: DoEvents:Loop

 IfNotCBool(InStrB(1, .document.body.innerText, _
 "No horse was found with the specified name"))Then

 Set Table = .document.all.tags("table").Item(13)

 ReDim strArr(1To Table.Rows.Length - 2, 1To 5)

 ForEach tblRowIn Table.Rows
 Let j = 1: Let i = i + 1
 If i > 2Then
 ForEach tblCellIn tblRow.Cells
 strArr(i - 2, j) = tblCell.innerText
 Let j = j + 1
 Next tblCell
 EndIf
 Next tblRow

 qrySht.Range("b65536").End(xlUp).Item( _
 2, 1).Resize(UBound(strArr, 1), 5).Value = strArr
 Erase strArr

 Else: tmpColl.Add "A" & f + 1 & ": " & tmpArr(f, 1)

 EndIf

 EndWith

 EndIfNextWith qrySht
 .Columns("B:F").AutoFit
 .Columns("C:C").TextToColumns Destination:=.Range("C1"), _
 DataType:=xlDelimitedEndWithIfCBool(tmpColl.Count)Then
 ReDim strArr(1To tmpColl.Count, 1To 1)
 ThisWorkbook.Worksheets.Add(after:=qrySht).Name = _
 "Horse Errors"
 For i = 1To tmpColl.Count
 strArr(i, 1) = tmpColl(i)
 Next
 With ThisWorkbook.Worksheets("Horse Errors")
 .Range("A2").Resize(UBound(strArr, 1)).Value = strArr
 With .Range("A1")
 .Value = "Query Errors"
 .Font.Bold =True
 .Font.Underline = xlUnderlineStyleSingle
 .HorizontalAlignment = xlCenter
 .EntireColumn.AutoFit
 EndWith
 EndWith
 Application.Goto qrySht.Range("a1")EndIf

errHandler:
 ie.Quit:Set ie =Nothing
 Set qrySht =Nothing
 Set tmpColl =Nothing

 With Application
 .StatusBar =False
 .ScreenUpdating =True
 .DisplayAlerts =True
 EndWith

 If Err.Number<> 0Then
 MsgBox "Error: " & String$(2, vbLf) & _
 Err.Number & String$(2, vbLf) & _
 Err.Description
 Err.Clear
 EndIfEndSub'-----------------------------------------------------------------------

And, now the following is the query results Worksheet, with the query names in Column A:

HorseWebQuery2.xls

A

B

C

D

E

F

1

Horse Names

Horse Name

Born

Sex

Sire

Dam

2

Folklore

M

Folgsam

Fokkela

3

Foobar

Folklore

3/20/1993

M

Native Prospector

Fantazia

4

Point Given

Folklore (GB)

2/5/1995

M

Fairy King

Falsoola (GB)

5

Bungle in the Jungle

Folklore (JPN)

3/8/1999

M

Dance in the Dark (JPN)

Andes Lady

6

Sensation

Folklore

2/26/2003

F

Tiznow

Contrive

7

Point Given

4/26/2000

G

Apollo

Kenney Lady

8

Point Given

3/27/1998

H

Thunder Gulch

Turko's Turn

9

Sensation (GB)

4/3/1993

M

Soviet Star

Outstandingly

10

Sensation

5/9/1994

G

Key to the Mint

Azuma Brook (JPN)

11

Sensation (ITY)

1/4/2002

F

Late Parade (IRE)

Padme

12

Sensation

1/24/2003

F

Dixie Union

Ryn

Sheet1

And, because cells A3 and A5 have invalid queries, a new error worksheet 'Horse Errors' is created, and looks like:

HorseWebQuery2.xls

A

B

C

D

1

Query Errors

2

A3: Foobar

3

A5: Bungle in the Jungle

Horse Errors

I hope this helps.

Sprucy · Dec 16, 2005

Hey Nate,

Going crazy with this...

I'm getting "Run-time error 13" type mismatch.

With Sheet2
Let tmpArr = .Range("A1").Value
But when I place the mouse pointer over this line, I get Dec/12/2005 which is good...
****
I've changed a few thing below, but still no luck....

Code:

Sub NewHorseModel()
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(Sheet2.Range("A1").Value)) Then _
    Exit Sub

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

With Sheet2
    Let tmpArr = .Range("A1").Value
End With

On Error GoTo errHandler

Set ie = CreateObject("InternetExplorer.Application")

ie.navigate "http://private intranet..."
Do While ie.busy: DoEvents: Loop
Do While ie.ReadyState <> 4: DoEvents: Loop

Sheet2.Range("A2:O2").Value = Array( _
    "#", "Date", "O", "D", "Dd", "A", "On", "P", "F", "G", "D/L", "B-T", "C")

'Sheet2.Range("A2: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("Operational_date").Name.Value = tmpArr(f, 1)
            .navigate "JavaScript:if (fnValidate()) document.Operational_date.submit();"
            Do While .busy: DoEvents: Loop
            Do While .ReadyState <> 4: DoEvents: Loop
            
            Set Table = .document.all.tags("table").Item(3)
            
            ReDim strArr(1 To Table.Rows.Length, 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
        
        Sheet2.Range("b65536").End(xlUp).Item(2, 1).Resize( _
            UBound(strArr, 1), 5).Value = strArr
    End If

Next

With Sheet2
    .Columns("A:O").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

Quote:

Form:

YOUR CODE:

Code:

<form name="HorseSearchForm" method="post" action="/workoutHorseSearchResult.do;jsessionid=34022977321134075201094?action=list">

MY CODE

Code:

<form BOTID="0" METHOD="POST" ACTION="dcc.asp">

Textbox:

YOUR CODE

Code:

<input type="text" name="name" maxlength="50" size="22" value="">

MY CODE

Code:

<input TYPE="TEXT" NAME="Operational_date" VALUE=""
size="20"></font></td>

What am I doing wrong?

Cheers,
Sprucy

Edited by NateO: Disabled HTML and added code tags. You can't just slam a bunch of HTML in your posts, you collapsed the thread.

NateO · Dec 18, 2005

Hello,

Well, with all due respect, quite a bit...

tmpArr() is an array, you can't just pass a string or a cell's value to it.... You'll need to change the data type declaration. And don't loop through the array, just do it once (get rid of the 'For f' loop).

And you can't use this JavaScript from a different site, to automate your website:

Code:

.navigate "JavaScript:if (fnValidate()) document.Operational_date.submit();"

This is not a universal IE document thing, it's specific to the website the OP wants to interface with. You need to get rid of this.

Now, when I look at your header Array... Does your Web Document's table have 13 columns? Then you can't use an array that's 5 vectors wide, dimensioned right here:

Code:

ReDim strArr(1 To Table.Rows.Length, 1 To 5)

Also:

Code:

.document.Forms("Operational_date")

Is a bogus Form reference, I see nothing in the source code that you've posted to indicate there's a Form with that name. The reason this worked for the original site, is that the form was named, yours is not. You'll need to refer to it by item number, go back to my first link in this post for an example:

http://www.mrexcel.com/board2/viewtopic.php?t=139085

Which item number is beyond me, you'll need to test... I believe this collection is 0-based. Start with 0, not sure what BotId is...

Good hunting.

abound1 · Dec 21, 2005

wow Nate,

thanks a lot for the code. That is really impressive. I really appreciate you doing this.

I just ran the code on my list of several hundred horses (which took me hours to collect the data for), and it only took a matter of minutes with the code, hahaha. Also, the error-checking feature was a great idea, and your way of handling it, generating a separate error sheet is terrific. I just had to try it with a few bogus names, just to see how it works. It works great. This will surely come in handy for the next K-Derby. Excellent work. You are a true master programmer.

Many thanks again.

NateO · Dec 22, 2005

You're welcome.

And thank you for the nice feedback, glad you tried it out and had some success.

Too bad it was a little late, but perhaps it will be of use next year, and hopefully it provides the board a nice working example of an approach to, what I consider, to be advanced, dynamic, Web Querying... I used more white-space than I normally would, in an attempt to break the code out a little...

Incidentally, use of white-space is also a bit of an art vs. science, I find. If you have too much, I find code hard to read, but just the right amount can help distinguish your intent by quite a bit, in my estimation...

And, keep in mind that the general technique of looping through dynamic, Web Document Tables and stacking arrays is a good one, along with the appraoch to error-trapping, etc... While site-specific forms, urls and other code semantics need to be maintained by site, and perhaps changed with changes to existing sites.

These procedures are a challenging to build, I find, but you can have some fun doing so, if you enjoy this type of work...

Sprucy · Jan 7, 2006

Still working on this one....

Hi Nate,

Alright, I really like your Horse query, but I still can’t get it going with my data.

My webpage is basic, you will find in the following order:

Table 1: which shows “Date”
Input box: where the date goes, *to be retrieve from cell A1, sheet3.Test
2 buttons: Submit & Reset
Table 2: the table I need to copy into excel, which has a Header (15 columns) that is always visible as a Template.

After pressing Submit, Table 2 will return about 600-800 rows of data.

Ex:
Table 2 (Before pressing Submit)
Date Time 3 4 5 6 7 …up to 15 columns. (No data below this line)

After:
Date Time 3 4 5 … 15
01JAN2006 1400 in out next
01JAN2006 1430 in out next

and so on for the next +/- 600 rows.

The code below is the closest I got to getting Table 2 into excel, but, this only retrieves the Template.
I need to submit the date from A1 into the Input box, then have Table 2 copied into excel.
Any ideas?

Basically, I need a cross from your Horse code and this one?
Like I’ve said before, I have the “View Source” info if you wish.

*************************

'Retrieve USD exchange rates using a Web Query
'By ?? got this from "www.mrexcel.com" forums
Sub GetRatesWithWebQuery()

Dim oBk As Workbook
Dim oQT As QueryTable

'Store the current settings of Excel's number formatting
Dim sDecimal As String
Dim sThousand As String
Dim bUseSystem As Boolean

'Create a new workbook
Set oBk = Workbooks.Add

'Create a query table to download USD rates
With oBk.Worksheets(1)
Set oQT = .QueryTables.Add( _
Connection:="URL;http://www.x-rates.com/d/USD/table.html", _
Destination:=.Range("A1"))
'Set oQT = .QueryTables.Add( _
Connection:="URL;http://my.private.webpage", _
Destination:=.Range("A2"))
End With

'Set the QueryTable's properties
With oQT
.Name = "USD"

'State that we're selecting a specific table
.WebSelectionType = xlSpecifiedTables

'Import the 5th table on the page
.WebTables = "5"
'.WebTables = "2" ' I need to import Table 2

'Ignore the web page's formatting
.WebFormatting = xlWebFormattingNone

'Do not try to recognise dates
.WebDisableDateRecognition = True

'Don't automatically refresh the query each time the file is opened
.RefreshOnFileOpen = False

'Waiting for the query to complete before continuing
.BackgroundQuery = True

'Save the query data with the workbook
.SaveData = True

'Adjust column widths to autofit new data
.AdjustColumnWidth = True
End With

With Application
'Remember Excel's current number format settings
sDecimal = .DecimalSeparator
sThousand = .ThousandsSeparator
bUseSystem = .UseSystemSeparators

'Set Excel's separators to match those of the web site
.DecimalSeparator = "."
.ThousandsSeparator = ","
.UseSystemSeparators = True

'Ignore any errors raised by the query failing
On Error Resume Next

'Perform the query, waiting for it to complete
oQT.Refresh BackgroundQuery:=False

'Reset Excel's number format settings
.DecimalSeparator = sDecimal
.ThousandsSeparator = sThousand
.UseSystemSeparators = bUseSystem
End With

End Sub

***********

Cheers,
Sprucy

webquery?

abound1

Board Regular

Sprucy

Board Regular

Excel Facts

NateO

Legend

Sprucy

Board Regular

NateO

Legend

NateO

Legend

Sprucy

Board Regular

NateO

Legend

abound1

Board Regular

NateO

Legend

Sprucy

Board Regular

Similar threads

Forum statistics

Share this page

webquery?

Board Regular

Board Regular

Excel Facts

Legend

Board Regular

Legend

Legend

Board Regular

Legend

Board Regular

Legend

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock