Excel VBA: Pull data from web

tinfanide · #1 12-09-2011, 02:11 AM

I've encountered a minor problem when doing the VBA coding
I've found in Yahoo Dictionary Online (HK)
Different words which we look up have different tag indexes

You can download the attached xlsm file and cross out the highlighted code lines and you will know what I mean
PullDataFromYahooDict.xlsm

I want to find a solution so that I don't need to put 8 or 12 as the index.

This question is asked as I need to write VBA codes for the task at work. Your answer does not make me earn more at work, though. Help me a bit if you think is suitable.

Code:

Sub YahooDicWebQuery()
On Error Resume Next
For x = 1 To Range("A" & Rows.Count).End(xlUp).Row
Dim IE As New InternetExplorer
IE.Visible = True
IE.navigate "http://hk.dictionary.yahoo.com/dictionary?p=" & Cells(x, 1).Value
Do
DoEvents
Loop Until IE.readyState = READYSTATE_COMPLETE
Dim Doc As HTMLDocument
Set Doc = IE.document
    
    Cells(x, 2).Value = "/" & WorksheetFunction.Substitute(WorksheetFunction.Substitute(WorksheetFunction.Replace(Doc.getElementById("results").getElementsByTagName("div")(8).innerText, 1, WorksheetFunction.Search("DJ", Doc.getElementById("results").getElementsByTagName("div")(8).innerText) + 2, ""), "[", ""), "]", "") & "/"
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''    
If Cells(x, 2).Value = "" Then
    Cells(x, 2).Value = "/" & WorksheetFunction.Substitute(WorksheetFunction.Substitute(WorksheetFunction.Replace(Doc.getElementById("results").getElementsByTagName("div")(12).innerText, 1, WorksheetFunction.Search("DJ", Doc.getElementById("results").getElementsByTagName("div")(12).innerText) + 2, ""), "[", ""), "]", "") & "/"
End If
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Next x
IE.Quit
On Error GoTo 0
End Sub

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Pull Outlook Meeting Information into Word	alhare	Word	0	02-01-2011 12:51 PM
help me to manipulate excel data	ibor	Excel	2	02-01-2011 03:14 AM
macro to pull data from multiple files	psrs0810	Excel	2	10-25-2010 01:49 PM
Template pull down menus	terpsichore	Word	0	03-02-2010 02:39 PM
Handling Excel Data	msofficeno475	Excel	1	01-17-2010 09:56 AM