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

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

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