Using Excel Data frm Web to collet data from a table

Hi All,

I am having a query to the web:  http://www.hkjc.com/english/racing/horse.asp?HorseNo=N175

Excel -> Data-> From Web -> Address -> http://www.hkjc.com/english/racing/horse.asp?HorseNo=N175

For the time being I need to collect data in the Web Table that contains the history of the Horse Racing Record, that is the table with Title : Horse Form Records (Recent 3 seasons) - MILITARY ATTACK .

When I do it in the Excel it always come back with error of :

This web query returned no data. To change the query .........

Please help to find out where I have done anything wrong. The best solution will be :

1.  Telling me where I was wrong and why
2, Send me the Excel file that has the solution to this problem.

Many thanks

David
tindavidAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

aikimarkCommented:
That's strange.  I can see the text using the MSXML2.XMLHTTP object, but not the querytables
0
RobOwner (Aidellio)Commented:
This is actually a web issue because the page is malformed.  There is no <doctype> nor is there a starting <html> or <head> tags.  Excel just bombs on trying to parse it as html.  If you do a text based query you'll see that it can connect and download the html of the page, the issue is having it parse it.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
tindavidAuthor Commented:
Well, if the excel is unable to extract the information, perhaps I will either close this question or someone able to provide a Perl script to extract information from that table:

Say, from this url, you will see the Horse Name, its characteristic then there will exist a table of history for that horse's racing history.  I need to extract the data in that table base on the Horse ID:

Try this URL:
http://www.hkjc.com/english/racing/horse.asp?HorseNo=T133

Also if the data in the table is also a link to another information, the link needs to be stored

For example:

602 06 09/05/15 ST / "Turf" / "B+2 "   1200 GF 4  4  059 P F Yiu   N Callan   1-1/4  3.7 132 6  6  6       1.10.65 1077 --
    Race Replay      
 

602 should also have a link of http://www.hkjc.com/english/racing/results.asp?racedate=09/05/2015&raceno=05&venue=ST

P F Yiu also has a link of http://www.hkjc.com/english/racing/TrainerWinStat.asp?trainercode=YPF&season=Current
N Callan http://www.hkjc.com/english/racing/JockeyProfile.asp?JockeyCode=CAN&season=Current
0
tindavidAuthor Commented:
I have found the solution myself by execute the following Macro in my VBA

     With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;http://racing.hkjc.com/racing/info/horse/smartform/english/K019", _
        Destination:=Range("$A$1"))
        .Name = horse_id
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlSpecifiedTables
        .WebFormatting = xlWebFormattingNone
        .WebTables = "2,3"
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
     End With
     '
0
RobOwner (Aidellio)Commented:
That's a different URL!

It works without vba.

see attached

Book1.xlsx

With the original links you'd provided IT CAN'T BE DONE
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
HTML

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.