Site navigation with Excell Vba
I can fetch data from known ulr, but can I navigate the site using excel.
For example, you can search Google and put the results in a spreadsheet. Or go to the framed site.
This code fetches 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
a source to share
Hey, so I figured this out and thought I'd post if 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
To do this successfully, you need to know the element names (you can do this by looking at the source code or by installing an addon that will help you like firebug). You can also use .getElementsByID ("someIDhere").
a source to share
I know this is a little late, but here is the solution. You will need to execute the interface to go through the search terminal, check / code it if needed, etc. It requires Internet Explorer on your PC. It will provide you with the raw HTML and you will 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
a source to share