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 = "http://www.google.com/search?q=" ' 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
' ---------------------------------
Init_IE
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
DoEvents
dtCurrentTime = Now
' ---------------------------------
' Exit Process if it is taking long time
' ---------------------------------
If DateDiff("s", dtStartTime, dtCurrentTime) > iMaxWaitTime Then sReturn = "TimeOut": Exit Sub
Loop

' 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"
Else
If InStr(1, sDocText, sData) <> 0 Then
sReturn = "Found"
Else
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

Destroy_IE

' -----------------------------
' Error Handler
' -----------------------------
Err_Clearer:
If Err <> 0 Then
Err.Clear
Resume Next
End If

End Sub

Sub Destroy_IE()

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

Exit Sub

ReInit_IE:

End Sub


Sub Init_IE()

On Error GoTo ReInit_IE

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

ReInit_IE:

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

Finally:


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 http://vbadud.blogspot.com/2009/08/how-to-login-to-website-using-vba.html

Excel VBA - 1004 -- The file could not be accessed

Solution for 1004 -- The file could not be accessed while saving the File

1004 Microsoft Office Excel cannot access the file 'C:\temp'. There are several possible reasons:

• The file name or path does not exist.
• The file is being used by another program.
• The workbook you are trying to save has the same name as a currently open workbook.

1004 -- The file could not be accessed. Try one of the following:

• Make sure the specified folder exists.
• Make sure the folder that contains the file is not read-only.
• Make sure the file name does not contain any of the following characters: < > ? [ ] : or *
• Make sure the file/path name doesn't contain more than 218 characters.

This error occurs because of unwanted characters in the File. The following function would help in removing those characters:



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

Finally:

ClearCharacters = strCleanString

End Function





See also :

Excel VBA - 1004 -- The file could not be accessed

Save and Reopen all Workbooks (Excel VBA)

Save copy of the workbook

SaveAs Dialog - Controlled Save

Save RTF document as word

Using Excel as Database using VBA (Excel ADO)

VBA ADO Code for using Excel as Database


Though many database systems have come , still there is a need to use Excel as Backend database. The reasons might be many -- you get Excel sheets as a Report and do not want to import that into Access or SQL Server

Here is a simple code that will allow you to do exactly that

Sub Excel_ADO()

Dim cN As ADODB.Connection '* Connection String
Dim RS As ADODB.Recordset '* Record Set
Dim sQuery As String '* Query String
Dim i1 As Long
Dim lMaxRow As Long '* Last Row in the Sheet
Dim iRevCol As Integer '*
Dim i3 As Integer

On Error GoTo ADO_ERROR

Set cN = New ADODB.Connection
cN.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Orginal.xls;Extended Properties=Excel 8.0;Persist Security Info=False"
cN.ConnectionTimeout = 40
cN.Open

Set RS = New ADODB.Recordset

lMaxRow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
iRevCol = 2

For i1 = 2 To lMaxRow

Application.StatusBar = i1
sQuery = "Select * From [Sheet1$]"

RS.ActiveConnection = cN
RS.Source = sQuery
RS.Open

If RS.EOF = True And RS.BOF = True Then
GoTo TakeNextRecord
End If

RS.MoveFirst
Do Until RS.EOF = True
sName = Trim$(RS("Name").Value)
sAge = Trim$(RS("Age").Value)
' Do some operations
RS.MoveNext
Loop


TakeNextRecord:
If RS.State <> adStateClosed Then
RS.Close
End If
Next i1

If Not RS Is Nothing Then Set RS = Nothing
If Not cN Is Nothing Then Set cN = Nothing

ADO_ERROR:
If Err <> 0 Then
Debug.Assert Err = 0
MsgBox Err.Description
Resume Next
End If

End Sub

All the code remains the same as Access ADO code except the change in connection string. cN.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Orginal.xls;Extended Properties=Excel 8.0;Persist Security Info=False"

See also:

Opening Comma Separate File (CSV) through ADO

Query Table with Excel as Data Source

Create Database with ADO / ADO Create Database

ADO connection string for Excel

Create Additional Pivot Table using Excel VBA (from Existing PivotCache)

Macro to Create a Pivot Table from Existing Pivot Cache

Many times you will have a Pivot Table created from a pivot cache and you have a requirment to create another pivot table from the same data. In such cases, instead of creating a new cache, you can use the existing pivot cache to create another pivot table. This will save a good amount of memory too.

Sub Create_Pivot_Table_From_Existing_Cache()

Dim oPC As PivotCache

Dim oPT As PivotTable

Dim oWS As Worksheet

oWS = ActiveSheet

If oWS.PivotTables.Count <>Then Exit Sub

oPC = oWS.PivotTables(1).PivotCache

oPT = oPC.CreatePivotTable(oWS.[J1], "Pivot From Existing Cache", True)

oPT.AddFields(oPT.PivotFields("Item").Name)

oPT.AddDataField(oPT.PivotFields("Customer"), "Quantity", xlCount)

End Sub

Here we are checking if any Pivot Table exist in that particular sheet; if it exists we are using the same cache of the pivot table to create another pivot table

See also:

Create Additional Pivot Table using Excel VBA (from Existing PivotCache)

Create Pivot Table using Excel VBA

Create Pivot Table from VBA using Wizard

Create Pivot Table using Excel VBA

Macro to Create a Pivot Table from New Pivot Cache

Sub Create_Pivot_Table_From_Cache()

Dim oPC As PivotCache

Dim oPT As PivotTable

Dim oWS As Worksheet

oWS = ActiveSheet

oPC = ActiveWorkbook.PivotCaches.Create(xlDatabase, oWS.UsedRange)

oPT = oPC.CreatePivotTable(oWS.[D20], "Pivot From Cache", True)

oPT.AddFields(oPT.PivotFields("Item").Name, oPT.PivotFields("Customer").Name)

oPT.AddDataField(oPT.PivotFields("Qty"), "Quantity", xlSum)

End Sub

PivotCache represents the collection of memory caches from the PivotTable reports in a workbook. Each memory cache is represented by a PivotCache object. The above example creates a pivotcache from existing data and then using the cache a pivot table is created

See also:

Create Additional Pivot Table using Excel VBA (from Existing PivotCache)

Create Pivot Table using Excel VBA

Create Pivot Table from VBA using Wizard

Create Pivot Table from VBA using Wizard

Use PivotTable Wizard Programmatically using Excel VBA

You can use PivotTableWizard method of Worksheet object to create a new PivotTable report. This method doesn’t display the PivotTable Wizard. This method isn’t available for OLE DB data sources. Use the Add method to add a PivotTable cache, and then create a PivotTable report based on the cache

Sub Create_Pivot_Table_Using_Wizard()

Dim oPT As PivotTable

Dim oWS As Worksheet

On Error GoTo Err_PT

If ActiveSheet.Type = xlWorksheet Then

oWS = ActiveSheet

oPT = oWS.PivotTableWizard(xlDatabase, oWS.UsedRange, oWS.Range("A20"), "PivotFromWizard")

oPT.AddFields("Item", , "Qty")

oPT.AddDataField(oPT.PivotFields("Qty"), "Quanity", xlSum)

oPT.TableRange1.Select()

End If

Exit Sub

Err_PT:

MsgBox(Err.Description)

Err.Clear()

End Sub

See also:

Create Additional Pivot Table using Excel VBA (from Existing PivotCache)

Create Pivot Table using Excel VBA

Create Pivot Table from VBA using Wizard

Creating a Command Button on Sheet using Excel VBA

Adding an OLE Object (Command Button) to a Worksheet using Excel VBA

Sub Create_Command_Button_2007()

'

' Creates a Command button and Positions it

' Written by Shasur for http://vbadud.blogspot.com

Dim oOLE As OLEObject

' Add a Command Button

oOLE = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", Left:=220, Top:=40, Height:=30, Width:=120)

oOLE.Interior.Color = vbRed

' Move and Size with cells

oOLE.Placement = XlPlacement.xlMoveAndSize

oOLE.Object.Caption = "Click Me..."

End Sub

Each OLEObject object represents an ActiveX control or a linked or embedded OLE object.

An ActiveX control on a sheet has two names: the name of the shape that contains the control, which you can see in the Name box when you view the sheet, and the code name for the control, which you can see in the cell to the right of (Name) in the Properties window. When you first add a control to a sheet, the shape name and code name match. However, if you change either the shape name or code name, the other is not automatically changed to match.