MobileMobile | Continue

Excel Bar

Excel Bar

Your excel questions will be responsed by our excel experts within 24hrs.Our service is free.

 Forgot Pass?
 Register Now
Find
Hot Search: Vlookup Match VBA
View: 401|Reply: 8

VBA scrape data from website HTML [SOLVED]

[Copy link]

2

Topics

8

Posts

20

Integral

Member

Rank: 2

Integral
20
Post on 2-11-2017 19:14:48 | All posts |Read mode
Hi,
I made a VBA code that scrapes the morningstar dates of some mutual fund quotes, and places them in a column, right to the fund id.
Nevertheless, I have been trying to code the exact same operation, but instead of placing fund ID on a column, place it on a line, and expect excel to scrape the dates on the line below, just like indicated:



Could you be so kind as to show me how can I fix my code to do so?
.xls file:
https://we.tl/BMFb7jDGAc

VBA code:
Sub DatesMSTAR()
'
Dim ie As Object
Set Rng = Range(A3:A26)
Set Row = Range(Rng.Offset(1, 0), Rng.Offset(1, 0).End(xlDown))
Set ie = CreateObject(InternetExplorer.Application)
With ie
For Each Row In Rng
.navigate http://www.morningstar.pt/pt/funds/snapshot/snapshot.aspx?id=  Range(A  Row.Row).Value  
Do
DoEvents
Loop Until ie.readyState = READYSTATE_COMPLETE
Dim doc As HTMLDocument
Set doc = ie.document
While ie.readyState  4
Wend
Application.Wait (Now + TimeValue(0:00:01))
On Error Resume Next
Range(B  Row.Row).Value = doc.getElementsByClassName(heading)(2).innerText
Next Row
ie.Quit
End With
End Sub
Thanks so much!












Reply

Props Report

11

Topics

662

Posts

1497

Integral

King

Rank: 6Rank: 6

Integral
1497
Post on 2-11-2017 19:58:09 | All posts



This will transpose the data in rows below.
Add the bolded lines of code at the bottom of your macro.

Sub
DatesMSTAR()
'

Dim
ie
As

Object

Set
Rng = Range(A3:A26)
Set
Row = Range(Rng.Offset(1, 0), Rng.Offset(1, 0).End(xlDown))
Set
ie = CreateObject(InternetExplorer.Application)
With
ie

For

Each
Row
In
Rng
.navigate http://www.morningstar.pt/pt/funds/snapshot/snapshot.aspx?id=  Range(A  Row.Row).Value  
Do
DoEvents
Loop

Until
ie.readyState = READYSTATE_COMPLETE
Dim
doc
As
HTMLDocument
Set
doc = ie.document
While
ie.readyState  4
Wend

Application.Wait (Now + TimeValue(0:00:01))

On

Error

Resume

Next
Range(B  Row.Row).Value = doc.getElementsByClassName(heading)(2).innerText
Next
Row
ie.Quit

End

With

   Rng.Resize(, 2).Copy
   Rng.Offset(Rng.Rows.Count + 1).Resize(1, 1).PasteSpecial xlPasteAll, Transpose:=
True
   Columns.AutoFit
   Rows.AutoFit


End

Sub












Reply Support Opposition

Props Report

2

Topics

8

Posts

20

Integral

Member

Rank: 2

Integral
20
 Author| Post on 2-11-2017 21:05:06 | All posts



Thanks AlphaFrog, but your change only transposes my colums.
What I need, is that excel takes my input on a line, and answers on the line below, just like on this pic:







Reply Support Opposition

Props Report

72

Topics

721

Posts

1666

Integral

King

Rank: 6Rank: 6

Integral
1666
Post on 2-11-2017 21:10:29 | All posts



May be
Sub DatesMSTAR()
    Dim ie          As Object
    Dim doc         As HTMLDocument
    Dim rng         As Range
    Dim cel         As Range
   
    Set rng = Range(C3:F3)
    Set ie = CreateObject(InternetExplorer.Application)
   
    With ie
        For Each cel In rng
            .navigate http://www.morningstar.pt/pt/funds/snapshot/snapshot.aspx?id=  cel.Value  
            Do
                DoEvents
            Loop Until ie.readyState = READYSTATE_COMPLETE
            Set doc = ie.document
            While ie.readyState  4
            Wend
            Application.Wait (Now + TimeValue(0:00:01))
            On Error Resume Next
            cel.Offset(1).Value = doc.getElementsByClassName(heading)(2).innerText
        Next cel
        ie.Quit
    End With
End Sub











Reply Support Opposition

Props Report

11

Topics

662

Posts

1497

Integral

King

Rank: 6Rank: 6

Integral
1497
Post on 2-11-2017 21:23:45 | All posts



Change the range to suit.

Sub
DatesMSTAR()
   
   
Dim
rng
As
Range, cell
As
Range
   
   
Set
rng =
[color=]Range(C3, Range(C3).End(xlToRight))
   
   
With
CreateObject(InternetExplorer.Application)
        
'.Visible = True
        
For

Each
cell
In
rng
            .navigate http://www.morningstar.pt/pt/funds/snapshot/snapshot.aspx?id=  cell.Value
            Do: DoEvents:
Loop

Until
.readyState = READYSTATE_COMPLETE
            Application.Wait Now + TimeValue(0:00:01)
            
On

Error

Resume

Next
            cell.Offset(1) = .document.getElementsByClassName(heading)(2).innerText
            
On

Error

GoTo
0
        
Next
cell
        .Quit
   
End

With
   
    rng.Columns.AutoFit
   
End

Sub










Reply Support Opposition

Props Report

2

Topics

8

Posts

20

Integral

Member

Rank: 2

Integral
20
 Author| Post on 2-11-2017 21:47:01 | All posts



Thank you very much YasserKhalil, this code was exactly what I was looking for.




Reply Support Opposition

Props Report

72

Topics

721

Posts

1666

Integral

King

Rank: 6Rank: 6

Integral
1666
Post on 2-11-2017 22:46:12 | All posts



You're welcome.Glad I can offer some help
As for Mr. AlphaFrog's post #5, I think it is working well too (Have it a test)









Reply Support Opposition

Props Report

2

Topics

8

Posts

20

Integral

Member

Rank: 2

Integral
20
 Author| Post on 2-11-2017 23:45:33 | All posts



Thank you very much as well AlphaFrog, you code works like a charm!
You two have helped me a lot!
Best of luck!




Reply Support Opposition

Props Report

72

Topics

721

Posts

1666

Integral

King

Rank: 6Rank: 6

Integral
1666
Post on 2-12-2017 01:08:26 | All posts



You're welcome. Please mark the thread as solved
Reply Support Opposition

Props Report

Points policy of this forum

Archiver|Mobile|Small dark house|Contact us|Excel Bar

GMT-5, 11-19-2017 16:15 , Processed in 0.150882 second(s), 20 queries .

Powered by Discuz! X3

© 2001-2013 Comsenz Inc.

!fastreply! Top !return_list!