[RESOLVED] Getting Web Page data into Excel

[doasidont]

I'm using the code below to go to a web page, copy all of the data and paste to clipboard. The web page, when first opened, has its focus set on a field that is expecting user input. So, when I paste what's in the clipboard into Excel, I get "Enter a name or symbol", which is the field's solicitation. How can I skip, unfocus, or otherwise get around this solicitation, so that when I 'copy all' I in fact get the entire web page and not the field solicitation? Thanks.

    Set IE = CreateObject("InternetExplorer.Application")
    With IE
        .Visible = True
        .Navigate (url)
        Do Until .ReadyState = 4: DoEvents: Loop
        .ExecWB 17, 0 '// SelectAll
        .ExecWB 12, 2 '// Copy selection
        .Quit           
    End With    
    ActiveSheet.Paste

[RobDog888]

I think you would rather use a more solid technique.

Check out Get External Data > From Web

What version of Excel are you using?

[doasidont]

2007. Thanks.

[RobDog888]

Did you check out the get external data? Its in the Data tab

[doasidont]

When I use the 'Get External Data from Web' method (which I would then record as a macro because I'll be looping throough several web retrievals), I get only text data. I need to gain access to the hyperlinks that are on the web page. Thanks.

[Siddharth Rout]

@ROB: AH!!! Look who is here Long time

@doasidont: You can use .InnerHTML to get the text Contents of the webpage

[Siddharth Rout]

Here is an example

Option Explicit

Sub Sample()
    Dim IE As Object
    
    Set IE = CreateObject("InternetExplorer.Application")
    
    With IE
        .Visible = True
        .Navigate "Google.Com"
        Do Until .ReadyState = 4: DoEvents: Loop
        
        Range("A1").Value = IE.Document.documentElement.innerhtml
        .Quit
    End With
End Sub

[doasidont]

Thanks, but that does not seem to be working. All I get is a lot of text data in "A1", but no hyperlinks that are down further on the web page.

[Siddharth Rout]

Can you share the URL with us?

[doasidont]

This is my starting url: http://www.nasd100.com/2012/01/stron...an-9-2012.html

On the web page that's retrieved, I'm trying to capture the urls that go with 'Balance Sheet' and 'Income Statement'. I'll use these urls to load up Excel with the repective data. Balance Sheet url is: http://moneycentral.msn.com/investor...tr&symbol=ANAC

[doasidont]

Sorry, the starting url is: http://investing.money.msn.com/inves...ce?symbol=ANAC

[Siddharth Rout]

>>>On the web page that's retrieved, I'm trying to capture the urls that go with 'Balance Sheet' and 'Income Statement'.

I have opened that page but I am unable to locate that link. Where exactly is that link?

Edit: Ok. Looking at the new URL now.

[incidentals]

within body line 341+ after <spn>finacials</span>

nothing particularly unique about this part of page

her to help if help is needed

[Siddharth Rout]

Ok the code is ready. Do you specifically want the URL OF 'Balance Sheet' and 'Income Statement' or just the innerHTML?

EDIT

Here is the code to get just the URL's

Option Explicit

Sub Sample()
    Dim MyArray1() As String, MyArray2() As String, TempARR() As String
    Dim InComeURL As String, BalanceURL As String
    Dim IE As Object
    Dim i As Long
    Dim tbl
    
    Set IE = CreateObject("InternetExplorer.Application")
    With IE
        .Visible = True
        .Navigate "http://investing.money.msn.com/investments/stock-price?symbol=ANAC&"
        Do Until .ReadyState = 4: DoEvents: Loop
        
        Set tbl = .Document.getElementById("LeftNav")
        
        MyArray1 = Split(tbl.innerHTML, vbCrLf)
        
        For i = 0 To UBound(MyArray1)
            If InStr(1, MyArray1(i), "Income Statement") Then
                TempARR = Split(MyArray1(i), "><A href=""")
                MyArray2 = Split(TempARR(1), """>")
                InComeURL = MyArray2(0)
            End If
            
            If InStr(1, MyArray1(i), "Balance Sheet") Then
                TempARR = Split(MyArray1(i), "><A href=""")
                MyArray2 = Split(TempARR(1), """>")
                BalanceURL = MyArray2(0)
            End If
        Next i
    End With
    
    MsgBox "Income Statement URL is : " & vbCrLf & InComeURL _
    & vbCrLf & vbCrLf & _
    "Balance Sheet URL is : " & vbCrLf & BalanceURL
    
    IE.Quit
    
    Set IE = Nothing
End Sub

[doasidont]

Very nice. Thanks to all of you.

[doasidont]

The code you gave works and I'm really appreciative of it, but there seems to be a little glitch in the Balance Sheet url that causes it not to work. "AMP;" occurs twice and it is not in the Properties url of the Balance Sheet hyperlink. No big deal as I can remove these characters with code, but if there's a simple change to your code that would fix it, that would be great. Thanks.