How to Kill the Word Process using VBA

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

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

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

How to Install Analysis ToolPak in Excel 2007

1. 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

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

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)

There are many reasons for this error:

1. Parameter name not spelt correctly
2. Case is not correct in parameter (firstname instead of FirstName )
3. 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

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

Next i1

End If

End Sub

VBA code to get system free space

How to get the free space available using VBA

The FreeDiskSpace property can be used to retrieve the free space information from Word VBA

Sub FreeDiskSpace_Current_Drive()

Dim sFreeSpace As String

sFreeSpace = System.FreeDiskSpace

sFreeSpace = Format(sFreeSpace, "0,000")

MsgBox "Free Space Available is " & sFreeSpace

End Sub

How to disable user from using short-cut keys using VBA

How to disable shortcut keys in VBA

You can disable the keybinding using the following code

Sub VBA_Sys()

' Disable Ctrl + S

FindKey(BuildKeyCode(wdKeyControl, wdKeyS)).Disable

' Rebind Ctrl + S to FileSave

FindKey(BuildKeyCode(wdKeyControl, wdKeyS)).Rebind wdKeyCategoryCommand, "FileSave"

End Sub

The above code disables Ctrl +S FileSave command. Use the rebind method to restore the bind.

Word VBA to get System Resolution

How to get System Resolution using VBA

Sub VBA_System_Resolution()

MsgBox System.HorizontalResolution & " X " & System.VerticalResolution

End Sub

Programmatically Open and Repair workbook using VBA

How to Repair Excel Workbook using VBA

The following code uses VBA to open and repair the workbook (the option available using Excel Open Dialog)

Sub OpenAndRepairWorkbook()

Dim oWB As Workbook

On Error GoTo Err_Open

Set oWB = Workbooks.Open(Filename:="C:\ShasurData\ExcelVBA\VBE Tools 2007.xlam", CorruptLoad:=XlCorruptLoad.xlRepairFile)

Exit Sub

Err_Open:

MsgBox Err.Number & " - " & Err.Description

Err.Clear

End Sub

How to use non-contiguous range of cells in Excel VBA

Here is a way to update a range that is not contiguous using VBA

Sub NonContiguous_Range_Example()

Dim oRng As Range

Set oRng = Range("A1, B5, C9")

oRng.Value = "45"

oRng.Interior.ColorIndex = 34

End Sub

The output will be as shown below:

How to insert data to a database with Access AutoNumber Field using VBA

SQL Command to Insert Data to a Table with AutoNumber Field

Access creates its own Primary Key, which is an AutoNumber field – no trouble if you are inserting record directly. On the other hand, if you insert through SQL Query we need to be bit careful. We cannot insert data to AutoNumber field to preserve its sanctity and an insert with only values will throw the “Number of query values and destination fields are not the same. “ error. To avoid this use the Insert with Field Name – Value Combination

Insert Statement without Field Name

oCm.CommandText = "Insert Into SampleTable Values ('" & sName & "','" & sLocation & "')"

Insert Statement with Field Names and Values

oCm.CommandText = "Insert Into SampleTable (UserName, Location) Values ('" & sName & "','" & sLocation & "')"

Here is the design of our Sample Table




“Number of query values and destination fields are not the same. “ error.

How to Insert Data to an Access 2007 Database Table using Excel VBA

VBA code for inserting data to Access 2007 database

Inserting data to Access database can be performed from VBA using ADO commands. The following code uses ActiveX Data Objects (ADO) to insert data to an Access 2007 database (accdb)

The code needs a reference to Microsoft ActiveX Data Objects library

Microsoft ActiveX Data Objects library Reference


This example uses a sample table with three fields (ID – autogenerated one , Name, and Location)

Sub Simple_SQL_Insert_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 = "Narayanaguda"

Set oCm = New ADODB.Command

oCm.ActiveConnection = Cn

oCm.CommandText = "Insert Into SampleTable (UserName, Location) Values ('" & sName & "','" & sLocation & "')"

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

The above code uses ADO’s command object to execute the insert query.

Access Table with Updated Values

Method 'MacroOptions' of object '_Application' failed

The error occurs due to various reasons

1. The Function corresponding to Macro name is not in a code module (it might be in Sheet/Workbook)

Solution : Copy the Macro to a code module. If a code module is not available, create one by Insert à Module

2. The macro name is not fully qualified. (WorkbookName!MacroName)

Solution: The following code snippet gives an example for prefixing the Macro name with the workbook name

Sub Add_UDF_To_Category()

Application.MacroOptions Macro:="PERSONAL.XLSB!Get_Net_Working_Days", Category:=2, Description:="Returns Net Working Days for 2009"

End Sub

Method 'MacroOptions' of object '_Application' failed

How to add a user defined function to a Category using Excel VBA Macro

How to Categorize the User Defined Function using VBA Macro

The following code adds the Get_Net_Working_Days UDF to the Date & Time Category.

Sub Add_UDF_To_Category()

Application.MacroOptions Macro:="PERSONAL.XLSB!Get_Net_Working_Days", Category:=2, Description:="Returns Net Working Days for 2009" '

End Sub

The above snippet uses the MacroOptions function that sets/resets properties that are available in Macro Options dialog

Insert Function Dialog – Before Macro

Insert Function Dialog – After Macro

How to select a Named Range in a Workbook using VBA

The following snippet will select the specified named range

Function Goto_A_Name(ByVal sName As String) As Boolean

'

On Error GoTo Err_Going

Application.GoTo Reference:=sName

Goto_A_Name = True

Exit Function

Err_Going:

End Function

Restrict Multiple Instance of Visual Basic Application

How to Prevent Visual Basic Exe being executed multiple times

At times we set/reset registry settings / environment variables as part of the program logic. If another instance of the application uses this concurrently it would become a mess. The best is to prevent the application from being loaded for the second time. The easy way to do is to use App.PrevInstance method as shown below.

If App.PrevInstance = True Then

MsgBox "An instance of this tool is running in this machine! Requested instance will terminated", vbExclamation

Exit Sub

End If

Only comments may appear after End Sub, End Function, or End Property

Only comments may appear after End Sub, End Function, or End Property

One possibility of this error might be because of the Declare statement, which might NOT be at the beginning of the module / class

Place the declare statement at the beginning of the module