Naveen's Weblog

Bridge to future

Posts Tagged ‘Performance’

Create, Execute and delete Macro in excel using C#

Posted by codingsense on May 11, 2009

When we need any specific task to be done in excel through our program, we first do the same task manually in excel and record the macro to have a look how the function should be written in our code.

There are some functions those are hard to find in the Interop.Excel dll, for such things microsoft has given helpful functions which will help us to add the macro in the sheet and execute it. This will help us to reuse the same macro that we generate in excel with record macro.

And later on if the macro is still present in generated excel then while opening it prompts enable/disable macro, so to avoid it, we will see how the macro can be deleted through C#.

Here let me explain you with a sample.

Read Only Or Locked Cell In Excel

Lets imagine a case , we have to generate an excel of employees with their name and salary. We want no employee should alter the salary that is enterd in the sheet i.e we want it to locked or to make it read only.

For the above case we will try to acheive executing a macro.

First we will try to see how a cell can be locked in excel and what macro is generated by excel when we record a new macro.

1) Select a cell
2) Enter some data ( that is to be locked for modification)
3) select Data Menu -> Validation
4) In Allow select custom
5) In formula enter “” and click ok
6) Now check altering the data in that cell, it gives a message and does not let the user change the value.

Wow this is what we need. Fine, since we have recorded the macro we will open the visual basic editor and see what macro has been generated, ok here is what i got.

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 11/05/2009 by naveen
'
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:=""""""
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
End Sub

now this is for the selection what we have done in the excel, to make more better when executing from external application we will pass a range parameter to the function, so that we can lock the input range of cells, let us also change the macro name to LockRange and change the message that is displayed to the user.

The new macro would look like.

Sub LockRange(oRange As String)
'
' Macro1 Macro
' Macro recorded 11/05/2009 by naveen
'
    Range(oRange).Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:=""""""
        .ErrorTitle = "Locked Cell"
        .ErrorMessage = "This cell is locked for modification."
        .ShowError = True
    End With
End Sub

Now since we know what macro to use to make the requirement possible, we just need to implement the same in our C# code, Here comes the code.

using System;
using System.Collections.Generic;
using System.Text;
using System.Windows.Forms;
using System.Runtime.InteropServices;
using System.Reflection;
using Excel = Microsoft.Office.Interop.Excel;
using Microsoft.Vbe.Interop;

namespace LockRangeInExcel
{
class Program    
{
static void Main(string[] args)
{
Excel.Application oXL = null;
Excel._Workbook oWB = null;
Excel._Worksheet oSheet = null;
Excel.Range oRng = null;
_VBComponent oModule = null; // VBA Module            
try            
{
//Start Microsoft.Office.Interop.Excel and get Application object.
                oXL = new Microsoft.Office.Interop.Excel.Application();
oXL.Visible = true;

//Get a new workbook.
                oWB = (Microsoft.Office.Interop.Excel._Workbook)(oXL.Workbooks.Add(Missing.Value));
oSheet = (Microsoft.Office.Interop.Excel._Worksheet)oWB.ActiveSheet;

//Add table headers going cell by cell.
                oSheet.Cells[1, 1] = "First Name";
oSheet.Cells[1, 2] = "Salary(Read Only)";

//Format A1:D1 as bold, vertical alignment = center.
                oSheet.get_Range("A1", "B1").Font.Bold = true;
oSheet.get_Range("A1", "B1").VerticalAlignment =
Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;

// Create an array to multiple values at once.                
string[,] saNames = new string[5, 2];

saNames[0, 0] = "Naveen";
saNames[0, 1] = "10000";
saNames[1, 0] = "Girish";
saNames[1, 1] = "20000";
saNames[2, 0] = "Nandish";
saNames[2, 1] = "15000";
saNames[3, 0] = "Rashmi";
saNames[3, 1] = "40000";

//Fill A2:B6 with an array of values (First and Salary).
                oSheet.get_Range("A2", "B6").Value2 = saNames;

//AutoFit columns A:D.
                oRng = oSheet.get_Range("A1", "B1");
oRng.EntireColumn.AutoFit();

//Create macro in excel
                CreateMacro(oWB, oModule);

// Run VBA macro "MakeCellReadOnly" on a range                
string oRange = "B2:B6";
LockRange(oWB, oRange);

//For single cell you can use the function as follows                
//oRange = "C7";                
//MakeCellReadOnly(oWB, oRange);                

//Delete the created Macro
                DeleteMacro(oWB, oModule);

//Make sure Microsoft.Office.Interop.Excel is visible and give the user control                
//of Microsoft Microsoft.Office.Interop.Excel's lifetime.
                oXL.Visible = true;
oXL.UserControl = true;
}
catch (Exception theException)
{
String errorMessage;
errorMessage = "Error: ";
errorMessage = String.Concat(errorMessage, theException.Message);
errorMessage = String.Concat(errorMessage, " Line: ");
errorMessage = String.Concat(errorMessage, theException.Source);

MessageBox.Show(errorMessage, "Error");
}

ReleaseComObj(oSheet);
ReleaseComObj(oWB);
ReleaseComObj(oXL);
}

private static void ReleaseComObj(object o)
{
try            
{
Marshal.ReleaseComObject(o);
}
catch            
{ }
finally            
{
o = null;
}
}

private static void DeleteMacro(Microsoft.Office.Interop.Excel._Workbook oWB, _VBComponent oModule )
{
Object oMissing = System.Reflection.Missing.Value;
oWB.Application.Run("DeleteThisModule"
, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing,
oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing,
oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing,
oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing);
}

public static void LockRange(Excel._Workbook oWB, string oRange)
{
Object oMissing = System.Reflection.Missing.Value;
//The following command executes the macro by passing the range to the macro
            oWB.Application.Run("LockRange"
, oRange, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing,
oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing,
oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing,
oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing);
}

public static void CreateMacro(Excel._Workbook oWB,_VBComponent oModule)
{
//Create a macro
            oModule = oWB.VBProject.VBComponents.Add(vbext_ComponentType.vbext_ct_StdModule);

//Create macro for locking the cell and delete the module            
string sCode =
"Sub LockRange(oRange As String)\r\n" +
"Range(oRange).Select\r\n" +
"With Selection.Validation \r\n" +
".Delete\r\n" +
".Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:= _\r\n" +
"xlBetween, Formula1:=\"\"\"\"\"\"\r\n" +
".ErrorTitle = \"Locked Cell\"\r\n" +
".ErrorMessage = \"This cell is locked for modification.\"\r\n" +
".ShowError = True\r\n" +
"End With\r\n" +
"End Sub\r\n" +
"Sub DeleteThisModule()\r\n" +
"Dim vbCom As Object\r\n" +
"Set vbCom = Application.VBE.ActiveVBProject.VBComponents\r\n" +
"vbCom.Remove VBComponent:= vbCom.Item(\"Module1\")\r\n" +
"End Sub\r\n";

//Add the macro in excel workbook that we have created
            oModule.CodeModule.AddFromString(sCode);

}
}
}

In VB.Net the same implementation can be done as

Module Module1
Sub Main()
Dim oXL As New Microsoft.Office.Interop.Excel.Application
Dim oWB As Microsoft.Office.Interop.Excel.Workbook
Dim OSheet As New Microsoft.Office.Interop.Excel.Worksheet
Dim oModule As Microsoft.Vbe.Interop.VBComponent

Try            

'Start Microsoft.Office.Interop.Excel and get Application object.
            oXL.Visible = True            
'Get a new workbook.
            oWB = oXL.Workbooks.Add()
OSheet = oWB.ActiveSheet

'Add table headers going cell by cell.
            OSheet.Cells(1, 1) = "FirstName"
            OSheet.Cells(1, 2) = "Salary(Read Only)"            

'Format A1:D1 as bold, vertical alignment = center.
            OSheet.Range("A1", "B1").Font.Bold = True
            OSheet.Range("A1", "B1").VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter

'Create an array to multiple values at once.            
Dim saNames(5, 2) As String
            saNames(0, 0) = "Naveen"
            saNames(0, 1) = "10000"
            saNames(1, 0) = "Girish"
            saNames(1, 1) = "20000"
            saNames(2, 0) = "Nandish"
            saNames(2, 1) = "15000"
            saNames(3, 0) = "Rashmi"
            saNames(3, 1) = "40000"            

'Fill A2:B6 with an array of values (First and Salary).
            OSheet.Range("A2", "B6").Value2 = saNames

'AutoFit columns A:D.
            OSheet.Range("A1", "B1").EntireColumn.AutoFit()

'Create macro in excel
            CreateMacro(oWB, oModule)

' Run VBA macro "MakeCellReadOnly" on a range            
Dim oRange As String = "B2:B6"
            LockRange(oWB, oRange)

'For single cell you can use the function as follows            
'oRange = "C7";            
'MakeCellReadOnly(oWB, oRange);            

'Delete the created Macro
            DeleteMacro(oWB)

'Make sure Microsoft.Office.Interop.Excel is visible and give the user control            
'of Microsoft Microsoft.Office.Interop.Excel's lifetime.
            oXL.Visible = True
            oXL.UserControl = True            

Catch        


End Try        

End Sub    

Private Sub DeleteMacro(ByVal oWB As Microsoft.Office.Interop.Excel.Workbook)
oWB.Application.Run("DeleteThisModule")
End Sub    

Private Sub LockRange(ByVal oWb As Microsoft.Office.Interop.Excel.Workbook, ByVal oRange As String)
'The following command executes the macro by passing the range to the macro
        oWb.Application.Run("LockRange", oRange)
End Sub    

Public Sub CreateMacro(ByVal oWB As Microsoft.Office.Interop.Excel.Workbook, ByVal oModule 
As Microsoft.Vbe.Interop.VBComponent)
'Create a macro
        oModule = oWB.VBProject.VBComponents.Add(Microsoft.Vbe.Interop.vbext_ComponentType.vbext_ct_StdModule)
'Create macro for locking the cell and delete the module        
Dim sCode As String = "Sub LockRange(oRange As String)" + Environment.NewLine + _
"Range(oRange).Select" + Environment.NewLine + _
"With Selection.Validation " + Environment.NewLine + _
".Delete" + Environment.NewLine + _
".Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:=" + _
"xlBetween, Formula1:=""""""""""" + Environment.NewLine + _
".ErrorTitle = ""Locked Cell""" + Environment.NewLine + _
".ErrorMessage = ""This cell is locked for modification.""" + Environment.NewLine + _
".ShowError = True" + Environment.NewLine + _
"End With" + Environment.NewLine + _
"End Sub" + Environment.NewLine + _
"Sub DeleteThisModule()" + Environment.NewLine + _
"Dim vbCom As Object" + Environment.NewLine + _
"Set vbCom = Application.VBE.ActiveVBProject.VBComponents" + Environment.NewLine + _
"vbCom.Remove VBComponent:= vbCom.Item(""Module1"")" + Environment.NewLine + _
"End Sub"        

'Add the macro in excel workbook that we have created
        oModule.CodeModule.AddFromString(sCode)

End Sub
End Module

To execute the above code, copy paste the whole code in your application. You need to add 2 dll’s to make the compilation successful they are,

  • Microsoft.Office.Interop.Excel
  • Microsoft.Vbe.Interop

And make sure you have given access to external application to access Excel. For more details visit

Posted in C# | Tagged: , , | 4 Comments »

Search metadata

Posted by codingsense on December 23, 2008


Many times we may want to see the information of our MSSQL objects. Here are some list of searches i have come across and found useful to share with you all.

  • SYSOBJECTS:

    The below query gives the list of keys associated with a table

    
    select name, xtype from sysobjects where parent_obj = object_id(N'[dbo].TableName')

    You can further filter it out by adding where clause to get only Primary Key( Xtype = ‘PK’) and foreign key (Xtype = ‘F’) and so on

    XType List :
    C: Check constraint
    D: Default constraint
    F: Foreign Key constraint
    L: Log
    P: Stored procedure
    PK: Primary Key constraint
    RF: Replication Filter stored procedure
    S: System table
    TR: Trigger
    U: User table
    UQ: Unique constraint
    V: View
    X: Extended stored procedure

  • INFORMATION_SCHEMA:

    To List the Tables in the database with thier properites use

    
    select * from INFORMATION_SCHEMA.TABLES

    To see the list of stored procedures in the database with thier properites use

    
    SELECT * FROM INFORMATION_SCHEMA.ROUTINES

    Information schema to find foreign keys associated with a table
    More Information_Schema

  • SYSINDEXES:

    Many people use count(*) to get the number of rows in a table, for this operation the MSSQL server recounts the rows thus leading to slower process. The table properties will have its rowcount at any point of time. The best way is to access from sysindexes as follows

    
    select rows from SYSINDEXES WHERE ID = OBJECT_ID('TableName') and indid < 2

Happy Learning :)

Posted in MSSQL | Tagged: , , | Leave a Comment »

Guidelines, Tips and Tricks MSSQL

Posted by codingsense on December 23, 2008

Here are some of the Tips and Tricks to imporve the performance of MSSQL.

  • Minimize the use of nulls.Because they incur more complexity in queries and updates. ISNULL and COALESCE functions are helpful in dealing with NULL values.
  • Use TRUNCATE TABLE statement instead of DELETE clause if you want to delete all rows from a table
  • Do not use reserved words for naming database objects, as that can lead to some unpredictable situations
  • Avoid using the new bigint data type unless you really need its additional storage capacity. The bigint data type uses 8 bytes of memory verses 4 bytes for the int data type
  • Don’t use “sp_“ as your prefix for stored procedures – it is a reserved prefix in MS SQL server!
  • The Union All statement is much faster than Union, because Union All statement does not look for duplicate rows, and Union statement does look for duplicate rows, whether or not they exist.
  • Always put the Declare statements at the starting of the code in the stored procedure. This will make the query optimizer to reuse query plans.
  • Do not call functions repeatedly in stored procedures, triggers, functions and batches, instead call the function once and store the result in a variable, for later use.
  • To avoid trips from application to SQL server, we should retrieve multiple resultset from single Stored procedure instead of using output param.
  • If stored procedure always returns single row result set, then consider returning the result set using OUTPUT parameters instead of SELECT statement, as ADO handles OUTPUT parameters faster than result set returned by SELECT statements.
  • Avoid (*), Try to restrict the queries result set by returning only the particular columns from the table, not all table’s columns.
  • Use SET NOCOUNT ON statement in your stored procedures to reduce network traffic.
  • Call stored procedure using its fully qualified name.The complete name of an object consists of four identifiers: the server name, database name, owner name, and object name. An object name that specifies all four parts is known as a fully qualified name. Using fully qualified names eliminates any confusion about which stored procedure you want to run and can boost performance because SQL Server has a better chance to reuse the stored procedures execution plans if they were executed using fully qualified names.
  • Do not query/manipulate the data directly in your front end application, instead create stored procedures, and let your applications to access stored procedure
  • Except or Not Exist clause can be used in place of Left Join or Not In for better performance.

If any more additional tips or guidelines then please post a comment.

Happy Learning :)

Posted in MSSQL | Tagged: , , | Leave a Comment »

Shrink Database and Files in MSSQL

Posted by codingsense on December 22, 2008

To increase your MSSQL database performance use Shrink database and files.

Each MDF file will have its LDF (LOG) file with it. Any transaction made to the database will lead to increase of size in MDF as well as LDF files. Over a period of time you will notice that eventhough your database has less data, the file size of both MDF and LDF will be large which leads to slower processing of queries and lead to other maintainance problems.

So MSSQL has given an option to remove unused spaces.

To shrink the database and files through Management studio you can follow the below steps

  • Open Object explorer
  • Right click on the database that needs to be shrinked
  • Tasks
  • Shirnk Database
  • Shrink Files

The following are the commands to shrink Database and Files through T-SQL.

Shrink Database :

dbcc ShrinkDatabase(DatabaseName)

The database name is the name that is seen in the object explorer of Management studio.

Shrink Files :

dbcc ShrinkFile(Logical Name)

The logical name of the database can be seen using the following query
Select name from sysfiles

Here 2 rows will be listed first one will represent the logical name of MDF file and other is for LDF file. Execute the DBCC query twice with both the names to shrink both MDF and LDF files, like

dbcc ShrinkFile(DB)

dbcc ShrinkFile(DB_LOG)

In my case i was working on a Email Project and the database would increase rapidly, so i make a stored procedure and gave user the option to shrink the database. In the button click event i called the stored procedure which was

Create PROCEDURE [dbo].[ShrinkEmail]
AS
BEGIN
 DBCC ShrinkDatabase(Emails)
 DBCC ShrinkFile(Emails)
 DBCC ShrinkFile(Emails_LOG)
END

This reduced my LOG file from size 7GB to 504KB and MDF file changed from 2.8 GB to 2.3 GB. :)

Caution: There are many discussions going over the net whether the MSSQL performance increases or decrease by shrinking the databases and files. So please do refer the following links and take appropriate decision.

http://technet.microsoft.com/en-us/library/ms189493.aspx
http://www.straightpathsql.com/archives/2009/01/dont-touch-that-shrink-button/

Happy Learning :)

Posted in MSSQL | Tagged: , | 8 Comments »

 
Follow

Get every new post delivered to your Inbox.