#1
|
|||
|
|||
Excel VBA: Pull data from web
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 |