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:

Name

ContactNo

Address

Email

Christina

516 418 1234

Cincinatti

Christina@vbadud.com

Girish Kutty

516 418 6752

Cincinatti

gkutty@vbadud.com

Ravichand Koneru

777 213 213

Boston

rkoneru@vbadud.com

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\Doc2.dot") ' 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 = "https://www.google.com/accounts/Login"
Set oBrowser = New InternetExplorer
oBrowser.Silent = True
oBrowser.timeout = 60
oBrowser.navigate sURL
oBrowser.Visible = True

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

Set HTMLDoc = oBrowser.Document

HTMLDoc.all.Email.Value = "
sample@vbadud.com"
HTMLDoc.all.passwd.Value = "*****"

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


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

End Sub






See Also : http://vbadud.blogspot.com/2008/05/google-search-using-vba.html
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.