Extract Data from a Web Page - using VBA

Question

Using VBA, I need to extract data from webpage http://emops.tse.com.tw/t21/sii/t21sc03_2011_9_e.htm

I am able to fetch all the data using following code:

With ActiveSheet.QueryTables.Add(Connection:="URL;http://emops.tse.com.tw/t21/sii/t21sc03_2012_2_e.htm", Destination:=Range("$A$1"))
        .Name = "67083361_zpid"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlEntirePage
        .WebFormatting = xlWebFormattingNone
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
End With

But the problem is I don't want data from whole page. I want data from the table where Industry name is Electron (It is the last table in this case)

Any trick for the same please?

See if this helps? http://stackoverflow.com/questions/8798260/html-parsing-of-cricinfo-scorecards/8846791#8846791 — Siddharth Rout, Dec 18 '12 at 13:26
You might want to mark some of your questions solved, your questions appear to be answered, but you haven't acknowledged them - people are disinclined to help if you don't acknowledge solutions — SWa, Dec 18 '12 at 15:29
They decided to do it all as *one* table for some reason, simplest to fetch the lot and, find "Industry: Electron" in A:A and delete everything above it — Alex K., Dec 18 '12 at 15:54

score 3 · Answer 1 · edited Apr 30 '14 at 15:51

3

Change:

.WebSelectionType = xlEntirePage to .WebSelectionType = xlSpecifiedTables

Add:

.WebTables = "2" below .WebFormatting = xlWebFormattingNone

'You will have to use trial and error with the "2" to find the exact table you are wanting to grab

edited Apr 30 '14 at 15:51

Xaver Kapeller

49,491
11
98
86

answered Apr 30 '14 at 15:29

Michael B

31
2

Extract Data from a Web Page - using VBA

1 Answers1

Linked