Friday, April 4, 2008

Some ADO Fundamentals

Hi,

We all are using ADO quite frequently, but we are not sure if we are using it efficietnly too or not. I have given some information below, which may help you out in managing your queries.
While getting a recordset from a connection object.
Set objRS = objConn.Execute(CommandText, [RecordsAffected],[Your Options])
You can speed up your records fetching process by mentioning the options in the recordset object. Following are the options which are available:

adCmdText If you have given the Command Text as a SQL string.
adCmdTable It is a sign that query would return the column names of the table.
adCmdTableDirectfor a table name, whose columns are all returned.
adCmdStoredProc for a stored procedure name.
adCmdFile for a saved recordset.
adCmdUnknown for an unknown command type.
adCmdUnspecified to indicate the command type is unspecified. ADO will work out the command type itself, but this will lead to poorer performance, so you should always explicitly set the command type.


You can also add the following ExecuteOptionEnum modifiers to Options:

adAsyncExecute, for asynchronous execution.
adAsyncFetch, for asynchronous fetching.
adAsyncFetchNonBlocking, for asynchronous fetching that does not block.
adExecuteNoRecords, for a non-row returning command.


Thanks,
Vikas Bhandari

Creating Custom Menus in Excel through VBA

Source :
http://www.ozgrid.com/VBA/custom-menus.htm



Sub AddMenus()
Dim cMenu1 As CommandBarControl
Dim cbMainMenuBar As CommandBar
Dim iHelpMenu As Integer
Dim cbcCutomMenu As CommandBarControl

'(1)Delete any existing one. We must use On Error Resume next _
in case it does not exist.
On Error Resume Next
Application.CommandBars("Worksheet Menu Bar").Controls("&New Menu").Delete
On Error GoTo 0

'(2)Set a CommandBar variable to Worksheet menu bar
Set cbMainMenuBar = _
Application.CommandBars("Worksheet Menu Bar")

'(3)Return the Index number of the Help menu. We can then use _
this to place a custom menu before.
iHelpMenu = _
cbMainMenuBar.Controls("Help").Index

'(4)Add a Control to the "Worksheet Menu Bar" before Help.
'Set a CommandBarControl variable to it
Set cbcCutomMenu = _
cbMainMenuBar.Controls.Add(Type:=msoControlPopup, _
Before:=iHelpMenu)

'(5)Give the control a caption
cbcCutomMenu.Caption = "&New Menu"

'(6)Working with our new Control, add a sub control and _
give it a Caption and tell it which macro to run (OnAction).
With cbcCutomMenu.Controls.Add(Type:=msoControlButton)
.Caption = "Menu 1"
.OnAction = "MyMacro1"
End With
'(6a)Add another sub control give it a Caption _
and tell it which macro to run (OnAction)
With cbcCutomMenu.Controls.Add(Type:=msoControlButton)
.Caption = "Menu 2"
.OnAction = "MyMacro2"
End With
'Repeat step "6a" for each menu item you want to add.

Creating Custom Menus in Power Point

A very good link to learn about Powerpoint.

http://www.bettersolutions.com/powerpoint/PBZ143/LE824411712.htm

Thanks,
Vikas

Thursday, April 3, 2008

Adding an item in Right Click Menu.


Option Explicit

Private Sub Workbook_Open()
    Call addButton
End Sub

Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
    Call addButton
End Sub

Sub addButton()
Dim cBut
On Error Resume Next
    Application.CommandBars("Cell").Controls("RightClickMenu").Delete
On Error GoTo 0
    Set cBut = Application.CommandBars("Cell").Controls.Add(Temporary:=True)
    With cBut
        .Caption = "RighClickMenu"
        .Style = msoButtonCaption
        .OnAction = "MacroName"
    End With
End Sub


Someone asked for help for creating a submenu in the right click menu. For example, An item like ColorRange in the right click pop up menu, and if you go over it, then it gives option to color the cells.

Following is the code for that:

Write the following code in the workbooks open method to add the item automatically once you open the workbook.



Sub addButton()
Dim cBut As CommandBarPopup
Dim pic As PictureFormat

On Error Resume Next
Application.CommandBars("Cell").Controls("RightClickMenu").Delete
On Error GoTo 0
Set cBut = Application.CommandBars("Cell").Controls.Add(Type:=msoControlPopup, temporary:=True)

With cBut
.Caption = "Color Menu"
End With

Dim red As CommandBarButton
Dim blue As CommandBarButton
Dim green As CommandBarButton
Dim black As CommandBarButton

Set red = cBut.Controls.Add(temporary:=True)
Set blue = cBut.Controls.Add(temporary:=True)
Set green = cBut.Controls.Add(temporary:=True)
Set black = cBut.Controls.Add(temporary:=True)

red.Caption = "Red"
blue.Caption = "Blue"
green.Caption = "Green"
black.Caption = "Black"
red.Picture = stdole.LoadPicture("c:\red.bmp")
green.Picture = stdole.LoadPicture("c:\green.bmp")
black.Picture = stdole.LoadPicture("c:\black.bmp")
blue.Picture = stdole.LoadPicture("c:\blue.bmp")
red.OnAction = "RedColor"
blue.OnAction = "blueColor"
green.OnAction = "greenColor"
black.OnAction = "blackColor"
End Sub



Private Sub Workbook_Open()
Call addButton
End Sub




Once you are done with the above code, then create a new module and paste the following code in the newly created module.




Enum Color
red
blue
green
black
End Enum


Sub RedColor()
ColorCell (Color.red)
End Sub
Sub BlueColor()
ColorCell (Color.blue)
End Sub
Sub GreenColor()
ColorCell (Color.green)
End Sub
Sub BlackColor()
ColorCell (Color.black)
End Sub

Sub ColorCell(clr As Color)

Dim rng As Range
Dim sht As Worksheet
Dim typNme As String

typNme = TypeName(Application.Selection)

Select Case (typNme)
Case "Range"
Set rng = Application.Selection

Select Case (clr)
Case Color.red
rng.Interior.Color = ColorConstants.vbRed
Case Color.black
rng.Interior.Color = ColorConstants.vbBlack
Case Color.blue
rng.Interior.Color = ColorConstants.vbBlue
Case Color.green
rng.Interior.Color = ColorConstants.vbGreen
End Select
End Select



End Sub

Wednesday, April 2, 2008

Creating Excel Menu from Dot net 2003



using System;
using Microsoft.Office.Interop.Excel;
using Microsoft.Office.Core;

namespace ConsoleApplication3
{

class Class1
{
///
/// Summary description for Class1.
///

private Application excel= new ApplicationClass();
private Workbook book= null;
private Worksheet sheet = null;
private CommandBar MainMenuBar = null;
private CommandBarControl MenuBarItem = null;
//private CommandBarButton MenuItem = null;
public Class1()
{
book = excel.Workbooks.Open("C:\\log.xls",0, true, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "",true, false, 0, true, false, false);
InitMenuBarItems("My Main Menu");
excel.Visible=true;
sheet = (Worksheet)book.Sheets.get_Item(1);
Console.WriteLine("The file name is {0}",sheet.Name);
Console.ReadLine();
}

private void InitMenuBarItems(string Caption)
{
try
{
MainMenuBar =
excel.CommandBars["Worksheet Menu Bar"];
MenuBarItem = MainMenuBar.Controls.Add(
MsoControlType.msoControlPopup,
Type.Missing, Type.Missing, Type.Missing, true);
MenuBarItem.Caption = Caption;

}
catch
{

}
}

[STAThread]
static void Main(string[] args)
{
Class1 cls = new Class1();

}
}
}

Create and register ADDin to Use in Excel

namespace NAddIn
{
[ClassInterface(ClassInterfaceType.AutoDual)]
public class Functions
{
public Functions()
{
}

public double Add2(double v1, double v2)
{
return v1 + v2;
}

[ComRegisterFunctionAttribute]
public static void RegisterFunction(Type t)
{
Microsoft.Win32.Registry.ClassesRoot.CreateSubKey(
"CLSID\\{" + t.GUID.ToString().ToUpper() +
"}\\Programmable");
}

[ComUnregisterFunctionAttribute]
public static void UnregisterFunction(Type t)
{
Microsoft.Win32.Registry.ClassesRoot.DeleteSubKey(
"CLSID\\{" + t.GUID.ToString().ToUpper() +
"}\\Programmable");
}
}
}

Build the NAddIn project to create bin\debug\NAddIn.dll.
Test the Add-In in Excel:
Open a new workbook in Excel.
Select Tools, Add-Ins, Automation.
NAddIn.Functions should be listed - select it. OK.
In a cell, type =Add2(3,4)
The cell should display 7.
To register the .dll after moving it, run regasm with the /codebase flag (typically as c:\WINDOWS\Microsoft.NET\Framework\v1.1.4322\RegAsm /codebase NAddIn.dll). You will get a warning about the assembly being unsigned - you can ignore this (or sign the assembly as documented).