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