import search result from a website
 

Hi All,

I want to create a web query in which I can import result data of a search
in the website.

1. How can I provide search criteria and press a search button through VBA?
2. How can I import the search result? (I tried to select result table and
press import, but Excel told me that the web query didn't provide any data.)

I guess that it requires other techniques as well, any guidance appreciated
putting me in the right direction in this field.

Thanks,
Stefi

import search result from a website
 

the code below takes data from cell B2 and performs a google search. the
code put a plus sign between each key word like a real google search.
Actually this is a real google search.

Public Sub GoogleSearch()
'Use and input box for typing in the search words
Dim szSearchWords As String
Dim szResults As String
With Sheets("Sheet1")
szSearchWords = .Range("B2").Value
End With
If Not Len(szSearchWords) 0 Then Exit Sub

'Get keywords and validate by adding + for spaces between
szSearchWords = Replace$(szSearchWords, " ", "+")

Dim ie As Object 'InternetExplorer
Set ie = CreateObject("InternetExplorer.Application")
ie.Navigate "http://www.google.com/search?hl=en&q=" & _
szSearchWords & "&meta="

'Loop until the page is fully loaded
Const READYSTATE_COMPLETE = 4
Do Until ie.ReadyState = READYSTATE_COMPLETE
With ie
.Visible = True
End With
Loop

Set Results = ie.document.getelementsbytagname("P")
For Each itm In Results
If InStr(UCase(itm.innertext), "RESULTS") Then
MsgBox (itm.innertext)
Exit For
End If
Next itm

With Sheets("Sheet2")
RowCount = 1
For Each itm In ie.document.all
.Range("A" & RowCount) = itm.tagname
.Range("B" & RowCount) = itm.classname
.Range("C" & RowCount) = Left(itm.innertext, 1024)

RowCount = RowCount + 1
Next itm
.Cells.VerticalAlignment = xlTop
End With

Set Results = ie.document.getelementsbytagname("LI")
With Sheets("Sheet3")
RowCount = 1
For Each itm In Results
.Range("A" & RowCount) = itm.innertext
RowCount = RowCount + 1
Next itm
.Cells.VerticalAlignment = xlTop
End With
'Explicitly clear memory
Set ie = Nothing
End Sub






"Stefi" wrote:

Hi All,

I want to create a web query in which I can import result data of a search
in the website.

1. How can I provide search criteria and press a search button through VBA?
2. How can I import the search result? (I tried to select result table and
press import, but Excel told me that the web query didn't provide any data.)

I guess that it requires other techniques as well, any guidance appreciated
putting me in the right direction in this field.

Thanks,
Stefi

import search result from a website
 

Thanks Joel, I'm going to test and study it!
Stefi


€žJoel€� ezt Ã*rta:

the code below takes data from cell B2 and performs a google search. the
code put a plus sign between each key word like a real google search.
Actually this is a real google search.

Public Sub GoogleSearch()
'Use and input box for typing in the search words
Dim szSearchWords As String
Dim szResults As String
With Sheets("Sheet1")
szSearchWords = .Range("B2").Value
End With
If Not Len(szSearchWords) 0 Then Exit Sub

'Get keywords and validate by adding + for spaces between
szSearchWords = Replace$(szSearchWords, " ", "+")

Dim ie As Object 'InternetExplorer
Set ie = CreateObject("InternetExplorer.Application")
ie.Navigate "http://www.google.com/search?hl=en&q=" & _
szSearchWords & "&meta="

'Loop until the page is fully loaded
Const READYSTATE_COMPLETE = 4
Do Until ie.ReadyState = READYSTATE_COMPLETE
With ie
.Visible = True
End With
Loop

Set Results = ie.document.getelementsbytagname("P")
For Each itm In Results
If InStr(UCase(itm.innertext), "RESULTS") Then
MsgBox (itm.innertext)
Exit For
End If
Next itm

With Sheets("Sheet2")
RowCount = 1
For Each itm In ie.document.all
.Range("A" & RowCount) = itm.tagname
.Range("B" & RowCount) = itm.classname
.Range("C" & RowCount) = Left(itm.innertext, 1024)

RowCount = RowCount + 1
Next itm
.Cells.VerticalAlignment = xlTop
End With

Set Results = ie.document.getelementsbytagname("LI")
With Sheets("Sheet3")
RowCount = 1
For Each itm In Results
.Range("A" & RowCount) = itm.innertext
RowCount = RowCount + 1
Next itm
.Cells.VerticalAlignment = xlTop
End With
'Explicitly clear memory
Set ie = Nothing
End Sub






"Stefi" wrote:

Hi All,

I want to create a web query in which I can import result data of a search
in the website.

1. How can I provide search criteria and press a search button through VBA?
2. How can I import the search result? (I tried to select result table and
press import, but Excel told me that the web query didn't provide any data.)

I guess that it requires other techniques as well, any guidance appreciated
putting me in the right direction in this field.

Thanks,
Stefi