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

Constants, fixed-length strings, arrays, user-defined types and Declare statements not allowed as Public members of object modules

Declaring the following in a Userform is one possible cause for the error:

Declare Function apiFindWindow Lib "User32" Alias "FindWindowA" _

(ByVal lpclassname As Any, ByVal lpCaption As Any) As Long

DLL procedures declared in standard modules are public by default and can
be called from anywhere in your application. DLL procedures declared in any
other type of module are private to that module, and you must identify them
as such by preceding the declaration with the Private keyword. Hence

Private Declare Function apiFindWindow Lib "User32" Alias "FindWindowA" _

(ByVal lpclassname As Any, ByVal lpCaption As Any) As Long

Should solve the problem

How to Windows Explorer using VBA

How to Open a Folder in Windows Explorer using VBA

ShellExecute() Windows API function can be called from a VBA macro to start another program under Microsoft Windows. Use ShellExecute() instead of Shell (a Visual Basic statement) or WinExec() (a Windows API function) to work around the following limitation of the latter commands.

With Shell and WinExec(), you cannot start an application by specifying a file name only. For example, the following Shell statement will fail:

Shell (“c:\temp”)

Declare the API function

Declare Function ShellExecute Lib "shell32.dll" Alias _

"ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation _

As String, ByVal lpFile As String, ByVal lpParameters _

As String, ByVal lpDirectory As String, ByVal nShowCmd _

As Long) As Long

The following code will open the specified folder in Windows Explorer

Sub Open_ExplorerWindow()

ShellExecute 0, "open", "c:\temp", 0, 0, 1

End Sub

Suppress "Opening this will run the following SQL command" message using Registry – Word Mail Merge

How to suppress "Opening this will run the following SQL command" message using Registry – Word Mail Merge

Here is a method to do the same

Word 2007

1. Start Registry Editor.

2. Locate and then click the following registry key:

HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Word\Options

3. On the Edit menu, point to New, and then click DWORD Value.

4. Under Name, type:

SQLSecurityCheck

5. Double-click SQLSecurityCheck.

6. In the Value data box, type:

00000000

7. Click OK.

Back to the top

Word 2003

1. Start Registry Editor.

2. Locate and then click the following registry key:

HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\Word\Options

3. Click Edit, point to New, and then click DWORD Value.

4. Under Name, type:

SQLSecurityCheck

5. Double-click SQLSecurityCheck.

6. In the Value data box, type:

00000000

7. Click OK.

Back to the top

Word 2002 Service Pack 3

To do this, follow these steps:

1. Start Registry Editor.

2. Locate and then click the following registry key:

HKEY_CURRENT_USER\Software\Microsoft\Office\10.0\Word\Options

3. Click Edit, point to New, and then click DWORD Value.

4. Under Name, type:

SQLSecurityCheck

5. Double-click SQLSecurityCheck.

6. In the Value data box, type:

00000000

7. Click OK.

Word Message

Type RegEdit in Run Window Select the appropriate key for your Word version

Create a new DWORD value

How to Show Field Codes in Word Document – using VBA

Word VBA – Show Field Codes

Here is a simple way to show the field codes in Word

Sub Show_Field_Codes()

Application.ActiveWindow.View.ShowFieldCodes = True

End Sub

How to Hide Field codes in a Word Document using VBA

Word VBA – Hide Field Codes

The following snippet will hide the field codes present in Word document.

Sub Hide_Field_Codes()

Application.ActiveWindow.View.ShowFieldCodes = False

End Sub

How to Insert Document Properties in Word Document

Update Document Properties in a Word document using Field codes

Field codes are the best tool for any Word users. They are rich source of predefined information that can be inserted in the document.

Word document contains many important properties, which can be accessed from the Properties dialog box.

To view the Properties dialog box, click the Microsoft Office Button , point to Prepare, and click Properties, click Document Properties, and then click Advanced Properties.

The following windows shows the properties

Word Document Properties

Word Advanced Properties

To insert a field select the QuickParts option from Insert tab and then DocProperty in the option
To select a property, click the property name in the Property box in the Field dialog box.

Word Quick Parts Dialog

To view the Field dialog box, on the Insert tab, in the Text group, click Quick Parts, and then click Field.


Insert DocProperty in Word Document

How to insert the DocumentProperties field using Word VBA

The same can be done through VBA as follows

Sub Insert_PropertyData_InDocument()

Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, Text:= _
"DOCPROPERTY CreateTime ", PreserveFormatting:=True

End Sub

Word DocProperty Field

How to delete Folder using VBA

VBA RmDir Method

Here is a simple method to delete the entire folder using VBA

Sub Delete_Folders_FS()

Dim sFolder As String

On Error GoTo Err_Msg

sFolder = "c:\temp\2Bdeleted\"

RmDir (sFolder)

Err_Msg:

If Err.Number <> 0 Then

MsgBox Err.Description

Err.Clear

End If

End Sub