Sunday, May 25, 2008

Google Search Using VBA

Search Webpages using VBA / Search Text in Google using Excel VBA / Programmaticaly search text in Google

This code requires Microsoft Internet Controls reference (Tools > References from code window)

The following function will search for given data in google and save the search as an HTML File. The Timeout parameter is set to 1 min.

Option Explicit

Private ieBrowser As InternetExplorer
Private Const sSite As String = "" ' Change this Appropriately
Private Const sProofPath As String = "d:\temp\" ' Path to Save Searched Pages

Sub Check_Data_From_Google(ByVal sData As String, ByRef sReturn As String, ByRef sSavePath As String)

'Requires Microsoft Internet Controls reference (Tools > References from code window)
Dim sSearchString As String ' Combination of Google Search String + Data
Dim dtStartTime As Date ' Start Time
Dim dtCurrentTime As Date ' Current Time
Dim iMaxWaitTime As Integer ' Maximum waiting time (in Secs)

Dim sDocText ' WebPage as Text
Dim sDocHTML ' WebPage as HTML

On Error GoTo Err_Clearer
' ---------------------------------
' Build the Search String
' ---------------------------------
sSearchString = sSite & sData

' ---------------------------------
' Start Time
' ---------------------------------
dtStartTime = Now
iMaxWaitTime = 60 'Seconds to be waited
ieBrowser.Navigate (sSearchString)

' ieBrowser.Visible = True

Do While ieBrowser.ReadyState <> READYSTATE_COMPLETE 'wait for page to load
dtCurrentTime = Now
' ---------------------------------
' Exit Process if it is taking long time
' ---------------------------------
If DateDiff("s", dtStartTime, dtCurrentTime) > iMaxWaitTime Then sReturn = "TimeOut": Exit Sub

' Assign the Webpage Results to Variable
sDocText = ieBrowser.Document.documentElement.innertext
sDocHTML = ieBrowser.Document.documentElement.innerhtml

If InStr(sDocText, "did not match any documents") <> 0 Then
sReturn = "NotFound"
If InStr(1, sDocText, sData) <> 0 Then
sReturn = "Found"
sReturn = "NotFound"
End If
End If

sSavePath = sProofPath & sData & "_" & sSpec & ".html"
sSavePath = ClearCharacters(sSavePath)
Open sSavePath For Output As 1
Print #1, sDocHTML
Close #1


' -----------------------------
' Error Handler
' -----------------------------
If Err <> 0 Then
Resume Next
End If

End Sub

Sub Destroy_IE()

On Error GoTo ReInit_IE
If Not ieBrowser Is Nothing Then Set ieBrowser = Nothing

Exit Sub


End Sub

Sub Init_IE()

On Error GoTo ReInit_IE

Set ieBrowser = GetObject(, "InternetExplorer.Application")
Exit Sub


Set ieBrowser = CreateObject("internetexplorer.application")
Application.Wait DateAdd("n", 1, Now) ' Wait for one/Two minutes to Start the Browser
End Sub

Function ClearCharacters(ByVal sDirtyString As String) As String

Dim arUnWantedCharacter(1 To 6) As String
Dim IsClear As Boolean
Dim i As Integer
Dim strCleanString As String
Dim j As Integer

arUnWantedCharacter(1) = "/"
arUnWantedCharacter(2) = "/"
arUnWantedCharacter(3) = "?"
arUnWantedCharacter(4) = "*"
arUnWantedCharacter(5) = "["
arUnWantedCharacter(6) = "]"

IsClear = True

strCleanString = vbNullString
For i = 1 To UBound(arUnWantedCharacter)
If InStr(1, sDirtyString, arUnWantedCharacter(i)) Then
IsClear = False
For j = 1 To Len(sDirtyString)
If Mid$(sDirtyString, j, 1) <> arUnWantedCharacter(i) Then
strCleanString = strCleanString & Mid$(sDirtyString, j, 1)
End If
Next j
sDirtyString = strCleanString
End If
Next i

If IsClear = True Then strCleanString = sDirtyString


ClearCharacters = strCleanString

End Function

Excel VBA save the search as an HTML File. The function uses innerhtml to get the searched webpage and saves it in the mentioned location

Save Webpage using VBA, VBA Save Webpage (without images) using Excel VBA

See also


  1. Since I'm not a VBA guru I only understand part of your post. What I want to do is write a formula in column B that opens Google and searches for the text in Column A and returns the result. Does your code do that and if so, how would I refer to it in a formula?

  2. Anonymous7:18 AM

    trying to run this. sSpec is not defined

  3. Anonymous6:50 AM

    Thanks. I am able to login. Now how can i navigate to another ink. please provied.


Share on Facebook
Related Posts Plugin for WordPress, Blogger...

Visual Basic for Applications (VBA) Forum (recent threads)

CodeKeep VBA Feed

Visual Studio Tools for Office Forum (recent threads)

Download Windows Live Toolbar and personalize your Web experience! Add custom buttons to get the information you care about most.

Office Business Applications (OBA) Team Blog

MSDN Code Gallery Published Resources For Tag VSTO Google Group