Wednesday, September 26, 2007

Reducing Size of Microsoft Access Database (Compact Database)

Compact MS Access Database using VBA (ADO Code)

If the DB Size is huge the compact DB utility [Compact & Repair Database (Tools-->Database utilities-->Compact and Repair Database) should reduce the DB Size.

Here is the code for doing the same using VB/VBA (ADO)

Public Sub CompactDB()
'Microsoft Jet and Replication objects
Dim objJE As New JRO.JetEngine, strSource As String, strTarget As String
Busy True
strSource = " "
strTarget = " "
objJE.CompactDatabase "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strSource & ";", "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strTarget & ";Jet OLEDB:Engine Type=4;"
Busy False
'Engine type:
'Access 97 = 4
'Access 2000 = 5
End Sub

Saturday, September 15, 2007

Excel VBA - Change Font Color for Part of Text

Formatting Text (Cell Contents) Partially using Excel VBA

Sometimes, you need to differentiate some parts of text in an Excel cell. This can be done using formatting those characters.

Here is the way that can be done using VBA using the Characters property

Sub Color_Part_of_Cell()
'Print ActiveCell.Characters.Count
With ActiveCell.Characters(2).Font
.Color = RGB(36, 182, 36)
End With

With ActiveCell.Characters(2, 2).Font
.Color = RGB(36, 182, 36)
End With

With ActiveCell.Characters(, 2).Font
.Color = RGB(36, 182, 36)
End With

End Sub

The output of the above is


expression.Characters(Start, Length)

expression Required. An expression that returns an object in the Applies To list.

Start Optional Variant. The first character to be returned. If this argument is either 1 or omitted, this property returns a range of characters starting with the first character.

Length Optional Variant. The number of characters to be returned. If this argument is omitted, this property returns the remainder of the string (everything after the Start character)

The same characters object can be used to make a character Bold / Italic

Orientation of Cell Through Excel VBA

Almost most of the Excel VBA programmers would have the requirement to change the orientation of the cell.

Here is an example

Sub Orientations()

ActiveCell.Orientation = xlHorizontal
ActiveCell.Orientation = xlVertical
ActiveCell.Orientation = xlUpward
ActiveCell.Orientation = xlDownward
ActiveCell.Orientation = 45
ActiveCell.Orientation = -45

End Sub

The orientation of the cell in Excel would be as follows:

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.