• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Specific Data and HTML - Beyond Excel's capabilities

S

Shaun

Member
Hi All


I am wondering if it is possible for Excel to obtain two specific pieces of information caontained with HTML source; Delta Whiskey and 7517.png from:


Code: 
<td class='horse'><a class='GreenLink' href="../InteractiveForm/HorseFullForm.aspx?horsecode=ODE0NjYyNjg0" target="_blank">DELTA WHISKEY</a></td>


and


<!--  <td><img class="minimizeStyle" onmouseover="showBigImage(this);" onmouseout="showSmallImage(this);" src="http://www.risa.com.au/JockeySilks/7517.png" /></td> -->


then dump it into a two column spread sheet?


If not in Excel would anyone have any clues?


Cheers


Shaun
 
Hi Shaun...


Interesting question. This is definitely possible with text formulas like FIND, MID & LEN.


But I have a question before attempting the answer. Is your HTML source in one cell or in a column? (with each cell containing a TR element...?)


If you have one TR in each cell in a column, then you can look for
Code: 
target="_blank"> and fetch next words until [code]</a>

and src="
and fetch entire image URL until />[/code]

Once you have image URL, extracting just the file name is easy, you just find last / and then fetch everything from there.
 
Hi Chandoo


Thank you for taking a look at my query.


The source can be found at:


http://www.risa.com.au/FreeFields/Results.aspx?Key=2013Apr09,VIC,Yarra Valley


then selecting view source in your browser


I am not sure how to get the source into Excel or Word or wherever for Excel to do it's thing.


Can Excel look at the source directly?


Cheers


Shaun
 
Hi Shaun ,


Excel can look at the source directly , but can you clarify whether DELTA WHISKEY is your only interest ?


There are 8 races in all in the posted website , and a generic procedure can be developed so that given the horse name , the relevant result is displayed ; is this what you are looking for ? What is your ultimate objective in reading from this website ?


Narayan
 
Hi Narayan


What you allude to is correct, I would be interested in obtaining that information for all horses, in all races, across all meets.


My previous XML query (which you solved - thank you) allows me to build a list of race meets which then allowas me to construct the address above so that Excel can cycle through the list and obtain the information from an alternate site.


What will eventually happen (fairly quickly) is that the list will already contain the the file name and horse name so some checking will be required.


Cheers


Shaun
 
Hi Shaun ,


Thanks for clarifying ; just one more doubt , which I should have asked earlier ; is this the only website from where you will retrieve information ? What I mean is whether the query is to be tailored for just this one website ?


Narayan
 
Last edited:
Hi SirJB7


Welcome back.


The link you have posted is appears to be similar to what I am trying to achieve except the web query picks up the relevant information, where as mine does not.


Using the page as a reference, if I import the tables, Excel will retrieve the results found in Sheet1, however what I would like to achieve is on Sheet2, specifically A12:A19 (and same for the remaining races).


For Race 1 this information is available (when viewing page source) on rows 369, 386, 403, 420, 437, 454, 471 and 488. Each line is identical except for the file name and is:


Code: 
<td><img class="minimizeStyle" src="http://www.risa.com.au/JockeySilks/Generic.png" /></td>


See file:

http://rapidshare.com/files/2543327305/Getdata.xlsm


Cheers


Shaun
 
Hi, Shaun!


Give a look at this file:

https://dl.dropboxusercontent.com/u/60558749/Specific%20Data%20and%20HTML%20-%20Beyond%20Excel%27s%20capabilities%20%28for%20Shaun%20at%20chandoo.org%29.xlsm


The procedure is:

a) get your data from the web as usual

b) run macro "ChoreoHTML"


As a gift you have the links for each shirt too.


This is the code:

-----

[pre]
Code: 
Option Explicit

'constants
Const gksWebPage = "Hoja1"

Sub ChoreoHTML()
' constants
Const ksURL = "http://www.risa.com.au/FreeFields/Results.aspx?Key=2013Apr09,VIC,Yarra%20Valley"
Const ksURLSilks = "http://www.risa.com.au/JockeySilks/"
Const ksSilkPrefix = "<td><img class=""minimizeStyle"" src="""
Const ksSilksuffix = """ /></td>"
Const kiColor = 1
Const kiHorse = 4
Const ksHorse = "Horse"
' declarations
Dim sSourceCode As String
Dim I As Long, J As Long, K As Long, A As String, lIndex As Long
Dim XML As Object, ws As Worksheet
' start
'  source code
Set XML = CreateObject("MSXML2.XMLHTTP")
With XML
.Open "GET", ksURL, False
.send ""
If .ReadyState = 4 Then
If .Status = 200 Then sSourceCode = .responseText
End If
End With
Set XML = Nothing
'  output range
Set ws = Worksheets(gksWebPage)
' process
'  string parsing
I = 1
J = InStr(I, sSourceCode, ksSilkPrefix & ksURLSilks)
K = 0
'  output index
lIndex = 0
'  cycle
With ws
Do Until J = 0
' identify element
K = InStr(J, sSourceCode, ksSilksuffix)
A = Mid$(sSourceCode, J + Len(ksSilkPrefix & ksURLSilks), K - J - Len(ksSilkPrefix & ksURLSilks))
' search next target
Do
lIndex = lIndex + 1
Loop Until .Cells(lIndex, kiHorse).Value <> "" And .Cells(lIndex, kiHorse) <> ksHorse
DoEvents
' output
.Cells(lIndex, kiColor).Value = A
' hyperlink
.Hyperlinks.Add ws.Cells(lIndex, kiColor), ksURLSilks & A
' next cycle
I = J + 1
J = InStr(I, sSourceCode, ksSilkPrefix & ksURLSilks)
Loop
End With
' end
Beep
End Sub
[/pre]
-----


Just advise if any issue.


Regards!
 
Hi SirJB7


Thank you, your code worked an absolute treat.


Is anything beyond the capabilities of Excel or more importantly the very much appreciated contributors?


Cheers


Shaun
 
Hi, Shaun!

Glad you solved it. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.

Regards!

PS: I didn't managed to get Excel bringing me the newspaper, neither going to the hypermarket, put gas on my car, ... if I think, Excel does almost nothing.
 
Hi Everyone,


I am new to VBA and I need to fetch values from following form into excel sheet. https://www.moneygram.com/wps/portal/moneygramonline/home/estimator?LC=en-US


Once the user clicks on estimate, the exchange rate is displayed. The excel sheet will have column headings as the field names in the form.


In the form, The 'send amount' will be 50, 100, 500, 1000, 2500 and 7000. The 'From' Country will always be 'United States'. So the worksheet should be populated with all the possible selections in all the other fields of the form against 'united states' and their corresponding exchange rates.


Any help is appreciated. Thanks in Advance.
 
@debmala

Hi!

Would you please start a new topic instead of writing on another user's one? It might be seen as hijacking. More indeed when it's such and old topic. If needed you could add a reference in your new one.

Perhaps you'd want to read the green sticky posts at this forums main page so as to know the guidelines that will lead to know how this community operates (introducing yourself, posting files, netiquette rules, and so on).

Regards!
 
Hi SirJB7


Thank you :) I have created a new post at http://chandoo.org/forums/topic/read-data-from-web-form-to-excel-sheet-using-vba?replies=1#post-95046


Regards


Debmala
 
You must log in or register to reply here.
Back
Top