CODEWITHSUNDEEP
excelvba
Ommit
Ommit

Reputation: 1984

Navigate a website with Excell Vba

I can pull data from known ulr, but can I navigate a website with excel.

For example can excel do a google search and put the results on a spreadsheet. Or navigate an in-frame site.

This code pulls data from a website.

With ActiveSheet.QueryTables.Add(Connection:= _
    PUT_URL_HERE, _
    Destination:=Range("A1"))
    .Name = "I need serious help"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = False
    .RefreshPeriod = 0
    .WebSelectionType = xlAllTables
    .WebFormatting = xlWebFormattingNone
    .WebPreFormattedTextToColumns = True
    .WebConsecutiveDelimitersAsOne = True
    .WebSingleBlockTextImport = False
    .WebDisableDateRecognition = False
    .WebDisableRedirections = False
    .Refresh BackgroundQuery:=False
End With

Upvotes: 2

Views: 36538

Answers (2)

Ommit
Ommit

Reputation: 1984

Hey so I figure this one out and thought I'd post in case anyone had a similar problem.

Sub googlesearch()
    Set objIE = CreateObject("InternetExplorer.Application")
    WebSite = "www.google.com"
    With objIE
        .Visible = True
        .navigate WebSite
        Do While .Busy Or .readyState <> 4
            DoEvents
        Loop

        Set Element = .document.getElementsByName("q")
        Element.Item(0).Value = "Hello world"
        .document.forms(0).submit
        '.quit
        End With

End Sub

So to do this successfully you'll need to know element names (you can do this by looking at the source code or installing an addin that helps you like firebug) you can also use .getElementsByID("someIDhere").

Upvotes: 5

Charles Byrne
Charles Byrne

Reputation: 834

I know it's a little late, but here's a solution. You'll need to do the interface to pass in the search term, validate/encode it if needed, etc. It requires Internet Explorer on your PC. It will give you the raw HTML and you'll have to parse it as needed.

Function GoogleSearch(strSearchTerm As String) As String
   Dim ie As Object
   Dim sHTML As String

   On Error GoTo ZERR
   Set ie = CreateObject("InternetExplorer.Application")

   With ie
       .Visible = False
       .navigate ("http://www.google.com/search?q=" & strSearchTerm)
       Do Until .readystate = 4
           DoEvents
       Loop
       sHTML = .document.Body.innerHTML
   End With
   Set ie = Nothing
GoogleSearch = sHTML    
ZERR:
   Set ie = Nothing
End Function

Upvotes: 1

Related Questions