Formating fetched data from web in VBA

**devilhack** · 11-20-2012, 03:54 AM

Hi guys,

i was looking a macro to fetch table from http://www.flipkart.com/canon-powers...f-5af8eb547815 and i did but the data fetch by the macro saves in single row. I want the table in the same format.
I tried many things but didnt work.
This is the script im using

Sub Extract_TD_text()

    Dim URL As String
    Dim IE As InternetExplorer
    Dim HTMLdoc As HTMLDocument
    Dim TDelements As IHTMLElementCollection
    Dim TDelement As HTMLTableCell
    Dim r As Long
    
    URL = Range("sURL")
    
    Set IE = New InternetExplorer
    
    With IE
        .navigate URL
        .Visible = True
    
        'Wait for page to load
        While .Busy Or .readyState <> READYSTATE_COMPLETE: DoEvents: Wend
    
        Set HTMLdoc = .document
    End With
    Set TDelements = HTMLdoc.getElementsByTagName("table")
    r = 0
    For Each TDelement In TDelements
        'Look for required TD elements - this check is specific to VBA Express forum - modify as required
        If TDelement.className = "fk-specs-type2" Then
        Sheet1.Range("A2").Offset(r, 0).Value = TDelement.innerText
            r = r + 1
        End If
    Next
            
End Sub

Thanks in Advance
-
AND I KNOW ITS EASY FOR YOU GUYS & IM JUST A NOOB

**Kyle123** · 11-20-2012, 05:04 AM

Why not use a web query? Data > From Web

No need to use VBA then

**devilhack** · 11-20-2012, 05:37 AM

Hei. I already tried that but i want fectch same data from 100s of link and the data links are on the same location.
This script work perfectly till formatting comes. all the table data save is in one Row...

plz help me out

**devilhack** · 11-20-2012, 06:09 AM

Book1.xlsm
HI attached and example what i actually want to do.
I hope it helps.

**Kyle123** · 11-20-2012, 05:40 AM

You could have a script that loops through the urls and creates a webquery for each one?

What sort of formatting are you talking about? The font/colour etc or the layout?

**Kyle123** · 11-20-2012, 06:15 AM

It still doesn't answer my question as to why you don't just use a web query and write code to loop through.

You're getting formatting like that since you aren't looping through table cells, you are looping through tables, so what you are calling TDElements are actually tables, not TDs.

Therefore when you extract the inner text of the TDElement, it is the text for the entire table, not the text for the td. You need to loop through all the rows and all the cells also, so you need another 2 nested loops.