Friday, August 21, 2009

How to add Format Conditions using Excel VBA

Conditional Formatting using Excel VBA

Here is a small snippet for FormatConditions.

Sub Format_Condition_Example()

Dim oFc As FormatCondition

Dim oRange As Range

Set oRange = Range("B2:B5")

Set oFc = oRange.FormatConditions.Add(xlCellValue, xlLess, "0.5")

oFc.Interior.ColorIndex = 3

Set oFc = oRange.FormatConditions.Add(xlCellValue, xlBetween, "0.5", "0.80")

oFc.Interior.ColorIndex = 6

Set oFc = oRange.FormatConditions.Add(xlCellValue, xlGreater, "0.80")

oFc.Interior.ColorIndex = 4

End Sub

Excel VBA - Format ConditionsExcel Formatting Conditions

See also:

How to Search a specific Colored Text (Range) using Excel VBA

Format ListColumns using VBA

How to Hide the PivotTable Fields List using VBA

Excel VBA - Hide Pivot Table Fields List

If you are developing some pivot table as part of the report and feel the Pivot Fields list at the right corner is bit distracting, you can turn it off as shown below:

Sub Hide_PivotTable_Fields()

ActiveWorkbook.ShowPivotTableFieldList = False 'Dont Show the Pivot Table List

End Sub

Pivot Table Fields List

Pivot Table without Fields List

Update Word Document with Excel Information using VBA

Excel Range to Word Template using VBA

Most often we maintain list of contacts in Excel workbook and it needs to be transferred to Word document (made from some template). Here is a simple snippet that can help:

The code is used to copy the content from Excel range shown below to a Word document:






516 418 1234


Girish Kutty

516 418 6752


Ravichand Koneru

777 213 213


Sub CopY_Data_To_Word()

Dim oWA As Word.Application

Dim oWD As Word.Document

Set oWA = New Word.Application

Set oWD = oWA.Documents.Add("C:\Users\comp\Documents\") ' Replace with your template here

For i1 = 2 To Cells.SpecialCells(xlCellTypeLastCell).Row

oWD.Bookmarks("Name").Range.Text = Cells(i1, 1)

oWD.Bookmarks("ContactNo").Range.Text = Cells(i1, 2)

oWD.Bookmarks("Address").Range.Text = Cells(i1, 3)

oWD.Bookmarks("Email").Range.Text = Cells(i1, 4)

'Code for saving the document

Next i1

' Releasing objects etc

End Sub

Bookmarks are added to the Word template and whenever a new document is created from the template, the document has those bookmarks.

The code above places the information from the Excel sheet to the specific Bookmark ranges

Excel to Word using VBA

Saturday, August 08, 2009

How to Login to Website Using VBA

Login to Google Account using VBA

Here is a simple code that will login to Google accounts with the provided user-name and password.

The program requires references to the following:

1 Microsoft Internet Controls
2. Microsoft HTML Object Library

Microsoft HTML Object LibraryMicrosoft HTML Object Library- VBA Reference

Microsoft Internet Controls - VBA Reference Microsoft Internet Controls - VBA Reference

The Internet control is used to browse the webpage and the HTML Objects are used to identify the username and password textboxes and submit the text using the control button.

Dim HTMLDoc As HTMLDocument
Dim oBrowser As InternetExplorer
Sub Login_2_Website()

Dim oHTML_Element As IHTMLElement
Dim sURL As String

On Error GoTo Err_Clear
sURL = ""
Set oBrowser = New InternetExplorer
oBrowser.Silent = True
oBrowser.timeout = 60
oBrowser.navigate sURL
oBrowser.Visible = True

' Wait till the Browser is loaded
Loop Until oBrowser.readyState = READYSTATE_COMPLETE

Set HTMLDoc = oBrowser.Document

HTMLDoc.all.Email.Value = ""
HTMLDoc.all.passwd.Value = "*****"

For Each oHTML_Element In HTMLDoc.getElementsByTagName("input")
If oHTML_Element.Type = "submit" Then oHTML_Element.Click: Exit For

' oBrowser.Refresh ' Refresh If Needed
If Err <> 0 Then
Debug.Assert Err = 0
Resume Next
End If

End Sub

See Also :
Related Posts Plugin for WordPress, Blogger...
Download Windows Live Toolbar and personalize your Web experience! Add custom buttons to get the information you care about most.