VBA Tips & Tricks

How to export Word Range as RTF using VBA

2009-09-25T19:31:00.000-07:00

Save Selected Word Range as RTF using Word VBA

The following snippet saves the selected Range as RTF using VBA (Word 2007)

Sub Export_Selection_As_RTF()

Selection.Range.ExportFragment "C:\Users\comp\AppData\Local\Temp\Sample.rtf", wdFormatRTF

End Sub

How to add Format Conditions using Excel VBA

2009-08-21T21:42:00.000-07:00

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 Formatting Conditions

How to Hide the PivotTable Fields List using VBA

2009-08-21T21:39:00.000-07:00

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

2009-08-21T19:26:00.001-07:00

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

How to Login to Website Using VBA

2009-08-08T19:05:00.000-07:00

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 Library- 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

How to change the Source of Pivot Table using VBA

2009-07-27T18:58:00.000-07:00

How to configure Pivot Table source data externally through VBA

A Pivot Table is linked to a particular source data. If for some reasons, you need that to be configured by users the following code will give some hint:

Sub Change_Pivot_TableDataSource()

Dim oPT As PivotTable

Dim oPC As PivotCache

Dim ORange As Range

Set oPT = ActiveSheet.PivotTables(1)

Set oPC = oPT.PivotCache

Set ORange = Application.InputBox(Prompt:="Select the New DataRange", Type:=8)

oPC.SourceData = "Sheet1!" & Application.ConvertFormula(ORange.Address, xlA1, xlR1C1)

oPT.RefreshTable

If Not oPT Is Nothing Then Set oPT = Nothing

If Not oPC Is Nothing Then Set oPC = Nothing

End Sub

The code gets the new data range through Input Box and modifies the SourceData of the Pivot Table. Change the Sheet name accordingly before you use the code.

How to Check the Source Type of Pivot Table using VBA

2009-07-27T18:57:00.000-07:00

Excel VBA Check Pivot Source

The following snippet could help in getting the source type of the Pivot Table

Sub CheckSourceConnection()

Dim pvtCache As PivotCache

Set pvtCache = Application.ActiveWorkbook.PivotCaches.Item(1)

On Error GoTo No_Connection

If pvtCache.SourceType = xlDatabase Then

MsgBox "The data source connection is: " & _

pvtCache.SourceData, vbInformation, "Pivot Table Source"

ElseIf pvtCache.SourceType = xlExternal Then

MsgBox "The data source connection is: " & _

pvtCache.SourceDataFile, vbInformation, "Pivot Table Source"

End If

Exit Sub

No_Connection:

MsgBox "Pivot Table source cannot be determined.", vbInformation, "Pivot Table Source"

End Sub

How to Add Popup Menu Item in Excel/Word using VBA

2009-07-27T18:54:00.000-07:00

Create Popup Menu (Right Click menu) using VBA

Here is a simple snippet that will add a menu item to the popup menu and assign a macro to it

Public Const APP_SHORTNAME = "VBADUD_POPUP"

Sub Add_To_Popup_Menu()

Dim ctlNewMenu As CommandBarControl

Dim ctlNewGroup As CommandBarControl

Dim ctlNewItem As CommandBarControl

On Error GoTo Err_Trap

On Error Resume Next

Application.CommandBars("Cell").Controls(APP_SHORTNAME).Delete

On Error GoTo 0

Set ctlNewMenu = Application.CommandBars("Cell").Controls.Add(Type:=msoControlPopup)

ctlNewMenu.Caption = APP_SHORTNAME

'--- Button - Load Raw Data ------------

Set ctlNewItem = ctlNewMenu.Controls.Add(Type:=msoControlButton)

ctlNewItem.Caption = "Process Data"

ctlNewItem.OnAction = "ProcessData"

Err_Trap:

If Err <> 0 Then

Err.Clear

Resume Next

End If

End Sub

The above will create a new Group and add the “Process Data” control to it.

How to Create PDF from Word Document using VBA

2009-07-22T19:12:00.000-07:00

Convert Word to PDF using VBA

Word 2007 has a new method - Document.ExportAsFixedFormat, which saves the document as PDF or XPS format

The following code will save the current document as PDF in the same path

Sub Convert_2_PDF()

ActiveDocument.ExportAsFixedFormat OutputFileName:= _

ActiveDocument.Path & "\" & ActiveDocument.Name & ".pdf", ExportFormat:= _

wdExportFormatPDF, OpenAfterExport:=False, OptimizeFor:= _

wdExportOptimizeForPrint, Range:=wdExportAllDocument, _

Item:=wdExportDocumentContent, IncludeDocProps:=True, KeepIRM:=True, _

CreateBookmarks:=wdExportCreateNoBookmarks, DocStructureTags:=True, _

BitmapMissingFonts:=True, UseISO19005_1:=False

End Sub

Welcome Office 2010

2009-07-18T23:46:00.000-07:00

At last the Office 2010 is out - here is what Microsoft has to say about its new baby:

"Microsoft® Office 2010 gives you rich and powerful new ways to deliver your best work - whether you’re at work, home, or school - on a computer, Web browser, or Smartphone. Grab your audience’s attention and inspire them with your ideas using enhanced tools, customizable templates, and photo editing capabilities. Work with multiple people from different locations at the exact same time using new co-authoring capabilities. By offering more ways to access your files from virtually anywhere, Office 2010 puts you in control. "

For more info -->
http://www.microsoft.com/office/2010/

Feel free to post your views on Office 2010

Office 2010 Downloads are available on request (https://microsoft.crgevents.com/Office2010TheMovie/Content/Default.aspx?p=Home&)

VBA Additional Contols not enabled in Tools menu

2009-07-16T18:42:00.000-07:00

Probably a silly one, but it took some good time of one of my student. So if the 'Additional Controls' in Excel VBA/Word VBA window is not enabled

Enable the Toolbox --> View -->Toolbox and it becomes enabled

How to Create New Menu using Excel VBA

2009-07-14T19:54:00.000-07:00

How to automatically add menu to Excel using VBa

Here is a code to add a new menu and a button whenever a sheet is opened.

Option Explicit
Private Const APPNAME As String = "Sample Menu"
Private Sub Workbook_Open()

Dim ctlNewMenu As CommandBarControl
Dim ctlNewGroup As CommandBarControl
Dim ctlNewItem As CommandBarControl
Dim vID
On Error GoTo Err_DUD

On Error Resume Next
Application.CommandBars("Worksheet Menu Bar").Controls(APPNAME).Delete
On Error GoTo 0
Set ctlNewMenu = Application.CommandBars("Worksheet Menu Bar").Controls.Add(Type:=msoControlPopup)
ctlNewMenu.Caption = APPNAME
'--- Add Control Buttons ---------------
Set ctlNewItem = ctlNewMenu.Controls.Add(Type:=msoControlButton)
ctlNewItem.Caption = "Sample Button"
ctlNewItem.OnAction = "SayHello"
ctlNewItem.TooltipText = "Sample Button by VBADUD"
Err_DUD:
If Err <> 0 Then
Err.Clear
Resume Next
End If
End Sub

How to have userform on when working with Word / Excel

2009-06-07T18:11:00.000-07:00

How to use Excel and Word Application when userform is displayed

Many times you need to show the userform and work with the application (Excel / Word etc). By default you cannot switch to the spreadsheet or document if the userform is shown.

Setting the form's ShowModal property to false enables user to use the Application when the userform is displayed

How to Reply to Mail using Outlook VBA

2009-05-24T19:21:00.000-07:00

Copy formatted word document to Outlook mail using VBA

Here is a simple snippet that replies to the mail using VBA. The contents of the document has earlier been saved as HTML and the HTML is copied to the mail Item

Public Sub ReplyWithHTML()
Dim oMail As Outlook.MailItem
Dim oFSO
Dim oFS
If Application.ActiveExplorer.Selection.Count Then
If TypeOf Application.ActiveExplorer.Selection(1) Is Outlook.MailItem Then
Set oMail = Application.ActiveExplorer.Selection(1).Reply

Set oFSO = CreateObject("Scripting.FileSystemObject")

Set oFS = oFSO.OpenTextFile("C:\ForBlogger\formedSample.html")

stext = oFS.readall
oMail.BodyFormat = olFormatHTML
oMail.HTMLBody = stext & vbCr & oMail.HTMLBody
oMail.Display
End If
End If
End Sub

Save Powerpoint Slides as Images using VBA

2009-05-20T17:56:00.000-07:00

VBA Code to Convert Ppwerpoint Slide to Image (JPEG)

Here is a simple code that will Export all slides in a powerpoint presentation to Jpeg files

Sub Save_PowerPoint_Slide_as_Images()

Dim sImagePath As String
Dim sImageName As String
Dim oSlide As Slide '* Slide Object
Dim lScaleWidth As Long '* Scale Width
Dim lScaleHeight As Long '* Scale Height
On Error GoTo Err_ImageSave

sImagePath = "C:\ForBlogger\"
For Each oSlide In ActivePresentation.Slides
sImageName = oSlide.Name & ".jpg"
oSlide.Export sImagePath & sImageName, "JPG"
Next oSlide

Err_ImageSave:
If Err <> 0 Then
MsgBox Err.Description
End If
End Sub

Disable Close button using Excel VBA

2009-05-08T18:34:00.000-07:00

How to disable close button using VBA

If you want to prevent the user to close the Application, you block the menu items, keypress etc. This snippet will help you in disabling the close button of the application.

Sub DisableExcelMenu()
' Remove Exel Menu Items
Dim hMenu As Long

hMenu = GetSystemMenu(Application.hwnd, 0)
Call DeleteMenu(hMenu, SC_CLOSE, MF_BYCOMMAND)
End Sub

The snippet uses WinAPI functions. Include the following functions to your module:

Public Declare Function GetSystemMenu Lib "user32" (ByVal hwnd As Long, ByVal bRevert As Long) As Long

Public Declare Function DeleteMenu Lib "user32" (ByVal hMenu As Long, ByVal nPosition As Long, ByVal wFlags As Long) As Long

Declare Function EnableMenuItem Lib "user32" ( _
ByVal hMenu As Long, _
ByVal uIDEnableItem As Long, _
ByVal uEnable As Long) As Long
'Used to find the Outlook icon in the system tray. If present then Outlook is running
Private Declare Function FindWindow _
Lib "user32.dll" _
Alias "FindWindowA" _
(ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long

Public Const MF_BYCOMMAND = &H0&
Public Const MF_BYPOSITION = &H400&
Public Const SC_ARRANGE = &HF110
Public Const SC_CLOSE = &HF060
Public Const SC_HOTKEY = &HF150
Public Const SC_HSCROLL = &HF080
Public Const SC_KEYMENU = &HF100
Public Const SC_MAXIMIZE = &HF030
Public Const SC_MINIMIZE = &HF020
Public Const SC_MOVE = &HF010
Public Const SC_NEXTWINDOW = &HF040
Public Const SC_PREVWINDOW = &HF050
Public Const SC_RESTORE = &HF120
Public Const SC_SIZE = &HF000
Public Const SC_VSCROLL = &HF070
Public Const SC_TASKLIST = &HF130
Public Const HWND_TOPMOST = -1
Public Const HWND_NOTOPMOST = -2
Public Const HWND_TOP = 0
Public Const SWP_NOSIZE = &H1
Public Const SWP_NOMOVE = &H2
Public Const GWL_STYLE = (-16)

To enable the close button use

Dim hMenu As Long

hMenu = GetSystemMenu(Application.hwnd, 0)
Call EnableMenuItem(hMenu, SC_CLOSE, MF_BYCOMMAND)

Disabled Close Button Excel

Kill Residual Excel Process using VBA

2009-05-06T19:43:00.000-07:00

Here is a simple VBA code to "Kill" the Excel process using VBA

Sub Kill_Excel()

Dim sKillExcel As String

sKillExcel = "TASKKILL /F /IM Excel.exe"
Shell sKillExcel, vbHide

End Sub

How to Kill the Word Process using VBA

2009-04-27T20:09:00.000-07:00

Developers who are working on Word VBA would have experienced the Word Crash problem more often.

Here is a crude way to kill the Word process.
Sub Kill_Word()

Dim sKillWord As String

sKillWord = "TASKKILL /F /IM Winword.exe"

Shell sKillWord, vbHide

End Sub

Process Window

Use this option if you are sure that the process is unused one

Reset Auto Filter and unhide Rows and Columns using Excel VBA

2009-04-27T20:05:00.000-07:00

Autofilters and hidden cells are a nemesis when you perform some operations. The following simple macro will unhide all rows/columns and turn-off the autofilter

Sub RemoveFiltersAndHiddenRows()

Dim oWS As Worksheet

For Each oWS In ActiveWorkbook.Sheets

oWS.AutoFilterMode = False
oWS.UsedRange.Rows.Hidden = False
oWS.UsedRange.Columns.Hidden = False Next

End Sub

How to Save a Chart as Image using Excel VBA

2009-03-15T06:09:00.000-07:00

Here is the way to save the active chart in Excel 2007 to a JPG file. It is better to size the chart appropriately before exporting it as an image.

Sub Save_ChartAsImage()

Dim oCht As Chart

Set oCht = ActiveChart

On erRROR GoTo Err_Chart

oCht.Export Filename:="C:\PopularICON.jpg", Filtername:="JPG"

Err_Chart:

If Err <> 0 Then

Debug.Print Err.Description

Err.Clear

End If

End Sub

The code uses Export method to save the chart in graphics format

How to Install Analysis ToolPak in Excel 2007

2009-03-15T06:05:00.000-07:00

How to Install Analysis ToolPak in Excel 2007

Click the Microsoft Office Button , click Excel Options, and then click the Add-ins category.

In the Manage list, select Excel Add-ins, and then click Go.

In the Add-ins available list, select the Analysis ToolPak box, and then click OK.

If necessary, follow the instructions in the Setup program.

The analysis pack will be loaded and displayed on the menu

How to Connect SQL Express 2005 from VBA

2009-03-14T09:19:00.000-07:00

Excel VBA retrieve data from SQL Server 2005

Here is a way to connect to SQL Express 2005 from Excel VBA

Sub Connect2SQLXpress()

Dim oCon As ADODB.Connection

Dim oRS As ADODB.Recordset

Set oCon = New ADODB.Connection

oCon.ConnectionString = "Driver={SQL Native Client};Server=.\SQLEXPRESS;Database=DB1; Trusted_Connection=yes;"

oCon.Open

Set oRS = New ADODB.Recordset

oRS.ActiveConnection = oCon

oRS.Source = "Select * From Table1"

oRS.Open

Range("A1").CopyFromRecordset oRS

oRS.Close

oCon.Close

If Not oRS Is Nothing Then Set oRS = Nothing

If Not oCon Is Nothing Then Set oCon = Nothing

End Sub

The code uses ActiveX Data Objects (ADO). You need to add a reference to it as shown below

How to update an Access Table using VBA

2009-03-07T06:18:00.000-08:00

How to update an Access Table using ADO

The following code snippet would be helpful to update an Access 2007 database table using VBA. The code uses ADO and requires a reference to ActiveX Data Objects Library

The sample uses a simple table which contains a name and a location field.

The code uses the SQL update query to update the database. The query is executed by the ADO’s command execute method

Sub Simple_SQL_Update_Data()

Dim Cn As ADODB.Connection '* Connection String

Dim oCm As ADODB.Command '* Command Object

Dim sName As String

Dim sLocation As String

Dim iRecAffected As Integer

On Error GoTo ADO_ERROR

Set Cn = New ADODB.Connection

Cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\comp\Documents\SampleDB.accdb;Persist Security Info=False"

Cn.ConnectionTimeout = 40

Cn.Open

sName = "Krishna Vepakomma"

sLocation = "Cincinnati, OH"

Set oCm = New ADODB.Command

oCm.ActiveConnection = Cn

oCm.CommandText = "Update SampleTable Set Location ='" & sLocation & "' where UserName='" & sName & "'"

oCm.Execute iRecAffected

If iRecAffected = 0 Then

MsgBox "No records inserted"

End If

If Cn.State <> adStateClosed Then

Cn.Close

End If

Application.StatusBar = False

If Not oCm Is Nothing Then Set oCm = Nothing

If Not Cn Is Nothing Then Set Cn = Nothing

ADO_ERROR:

If Err <> 0 Then

Debug.Assert Err = 0

MsgBox Err.Description

Err.Clear

Resume Next

End If

End Sub

Table Before Update Command

Table After Update Command

No value given for one or more required parameters. (ADO Error)

2009-03-07T06:12:00.003-08:00

There are many reasons for this error:

Parameter name not spelt correctly
Case is not correct in parameter (firstname instead of FirstName )
Passing incorrect type, for example, numeric instead of string - pass the string within quote

How to extract all synonyms of a given word using VBA

2009-02-22T04:06:00.001-08:00

The following code snippet gives a hint on how to extract synonym list using Word VBA

Sub Retrieve_Word_Info()

Dim arSynonyms

Dim oSynInfo As SynonymInfo

Dim arSynList

Dim sWord As String

sWord = "call"

Set oSynInfo = Application.SynonymInfo(sWord)

If oSynInfo.Found = True Then

For i1 = 1 To oSynInfo.MeaningCount

arSynList = oSynInfo.SynonymList(i1)

For i2 = 1 To UBound(arSynList)

MsgBox oSynInfo.MeaningList(i1) & " := " & arSynList(i2)

Next i1

End If

End Sub