Archives

Archives / 2008 / July
  • Number of Rows Affected Rowcount and Top keyword

    Hi,

    Many a times in a query we need to limit down the number of records returned in the query. This can be done in two ways using the TOP keyword or the row count keyword.

    Select top 10 from Table

    This will return the top 10 records from the query. We can also make this dynamic by making the Value in Top keyword to come from a variable.

    DECLARE @top INT
    SET @top = 10
    SELECT TOP(@top) * FROM Table

    If using a stored procedure we can also pass this variable as a parameter in the procedure. This way we can easily control the number of record affected.

    Another way to do the same stuff is to use the rowcount variable.

    DECLARE @top INT
    SET @top = 10
    SET ROWCOUNT @top
    SELECT * FROM Table
    set rowcount 0

    Remember converting the rowcount back to 0 is very important; otherwise all other query will follow the same rowcount as set earlier.

    The drawback with rowcount is that in the future version this will not work with the delete and update statement in the future version of the SQL server, so its best to use the top keyword only.

    Vikram

  • Embedding another word file to a word document programmatically

    Hi

    Many a times we want to embed some document in the word file, with the help of Insert-> File-> Select File menu item. This will copy the content of the file to the word document. This can also be done programmatically. 

    String LocationOfFileToMerge = "C:\\Myfile.doc";

    range.InsertFile(LocationOfFileToMerge,ref oMissing, ref oFalse, ref oFalse, ref oFalse);

    We can also embed the document in the word file instead of copying the text of the document. In this case you can embed any kind of document.


    Object NameForLabel = "File Name";

     

    //Icon To Be Shown For Label
    Object IconFileName = "C:\\IconFile.ico";

     

    String LocationOfFileToMerge = "C:\\Myfile.doc";

    Object ClassType = "Word.Document.8";

    Object True = true;

    Object False = false;

    Object Missing = System.Reflection.Missing.Value;

     

    range.InlineShapes.AddOLEObject(

        ref ClassType,ref LocationOfFileToMerge,ref False, ref True, ref IconFileName,

        ref Missing,ref NameForLabel, ref Missing);

    Vikram

  • Programmatically making basic text formatting option in Word 2007 Automation

    Hi,

    when you start working with word programmatically, many a times you will have to add some text in the word document with formatting. Word automation gives you easy way top format the text that you are writing in the Word document programmatically.

    To make the same impact as would an enter button in the word document we can use the following code.

    oWord.Selection.TypeParagraph();

    Basically this will enter a new paragraph in the word document. But you are in a bullet mode than this will take you to next line and also add the next bullet in the sequence.

    Some of the common word formatting functionality can easily be performed with the help of word automation.

    oWord.Selection.Font.Bold = 1;

    oWord.Selection.Font.Color = Word.WdColor.wdColorAqua;

    oWord.Selection.Font.Italic = 1;

    oWord.Selection.Font.Underline = Word.WdUnderline.wdUnderlineDashHeavy;


    This will change the formatting in the selected text in the active word document in the application. You can also find many more formatting options in the font class. Remember once you apply the formatting and till you remove the formatting, the formatting will continue till we remove the formatting. To remove the formatting we need to use the following code.

    oWord.Selection.ClearFormatting();

    To add text in the word document we can use the following code.

    oWord.ActiveWindow.Selection.TypeText("www.vikramLakhotia.com");


    Vikram

  • Programmatically replace word with the help of Word Automation

    Hi,

    In My last two posts I talked about

    ·         How to open and close a word document programmatically.

    ·         How to save a word document in different programmatically.

    In this post I will talk about how we can replace words in the word document. Many a times we will find ourselves in situation where by we have a word document with a predefined template where some of the words need to be replaced by user entered values. This can be easily performed with the help of word automation.

    To replace words in the word document we first need to open the word document programmatically. The following code snippet function shows how we can replace the word in the document. If we want to replace the word inside a selection then we need to pass the range of the selection or we need to pass the full word document in the

      public static void ReplaceRange(Microsoft.Office.Interop.Word.Range range, object findText,

                                                     object replaceText)

            {

                object item = Microsoft.Office.Interop.Word.WdGoToItem.wdGoToPage;

                object whichItem = Microsoft.Office.Interop.Word.WdGoToDirection.wdGoToFirst;

                object replaceAll = Microsoft.Office.Interop.Word.WdReplace.wdReplaceAll;

                object forward = true;

                object matchAllWord = true;

                object missing = System.Reflection.Missing.Value;

     

                range.Document.GoTo(ref item, ref whichItem, ref missing, ref missing);

     

                range.Find.Execute(ref findText, ref missing, ref matchAllWord,

                   ref missing, ref missing, ref missing, ref forward,

                   ref missing, ref missing, ref replaceText, ref replaceAll,

                   ref missing, ref missing, ref missing, ref missing);

            }

     

    Here we first go to the first page in the document and then execute the find with a replace text. We also pass the optional parameter to replace all keyword in the system.  To call this function we need to pass the word document(or a range selection of word document) along with object of the find text and replace text and the function will replace all the words in the given word document.

    Vikram

  • Word Automation saving word document in the different Format

    Hi,

    In my last post I talked about how to open and close a word document programmatically. In this post I will talk about how we can save the word document.

    While using a word document there can be three kind of save functionality that can be required.

    1.       When we open an existing word document and make some changes to the word document and save the word document.

    2.       When we open a new document and want to save the document in a specified location

    3.       When we want to save the word document in another format (Like XML, Compatible to word 97 etc..)

    For the first criteria we can simply call the save method of the word document. This function does not take any parameter and saves the document at the same location where it was opened. Here is the code snippet.

    oWordDoc.Save();

    For the second option we need to use the SaveAs function of the word document object.  The function takes many parameter, but the most important and required parameter in the first parameter which takes the path where the document will be saved.

    object fileName = “FullPath_for_saving_the_word_document”;

    oWordDoc.SaveAs(ref fileName, ref missing, ref missing, ref 

                 missing, ref missing, ref missing, ref missing, ref missing, ref

                    missing, ref missing, ref missing, ref missing, ref missing, ref

                    missing, ref missing, ref missing);

    For the third option we need to pass one more parameter (second parameter) to the saveAs function telling the format in which the document should be saved.  The format has to be of type object but made form one of the values in the enumeration Microsoft.Office.Interop.Word.WdSaveFormat

    object fileName = “FullPath_for_saving_the_word_document”;
    object Format = Microsoft.Office.Interop.Word.WdSaveFormat.wdFormatDocument;
    or
    //object Format = Microsoft.Office.Interop.Word.WdSaveFormat. wdFormatXML;

    oWordDoc.SaveAs(ref fileName, ref Format, ref missing, ref 

                 missing, ref missing, ref missing, ref missing, ref missing, ref

                    missing, ref missing, ref missing, ref missing, ref missing, ref

                    missing, ref missing, ref missing);

    Vikram

  • Automating Microsoft Word 2007 with C Sharp

    Hi,

    Some time in our project we need to work with word (winword) document programmatically. Word provides a good way to automate nearly all the functionality that can be used through interface.  The process of programmatically working with word document is commonly termed as word Automation.

    I will be using a series of blogs to discuss some of the functionality that can be achieved through word automation. In this Blog I will discuss how we can start programmatically open a new or and existing word document and close it.

    To work with word 2007 automation we need to have the word 2007 installed in the machine where the application will run. When we install the word 2007, it also installs with it the com component required to automate word 2007.

    To start working on the word automation we first need to reference the dll in the project. For this you need to click on the add reference in the project and go to the Com tab. There find the com component called Microsoft Office 12.0 Object Library. The namespace used for the word automation is Microsoft.Office.Interop.Word.

    To create or open an existing word document we first need to open the word application. Then open or create the word document and last but not the least close the document.

    Here is a code on how to do it. Remember to work with word programmatically; word takes a lot of parameter which might not be required in normal working. In most of the cases we will pass missing value to these parameters. Also all the value passed to word document needs to be passed as reference and not as value. Here is a code snippet of how to open a new word document.

    //Create an object for missing values. This will be passed when ever we don’t want to pass value

    Object missing = System.Reflection.Missing.Value();

    //Objects for true and false to be used in the word document for passing true or false.

    Object true = true;

    Object false = false;

     

            

    //Creating objects of word and document

    Microsoft.Office.Interop.Word.Application oWord = new Microsoft.Office.Interop.Word.Application();

    Microsoft.Office.Interop.Word.Document oWordDoc = new Microsoft.Office.Interop.Word.Document();

           

    //Adding a new document to the application

    oWordDoc = oWord.Documents.Add(ref missing, ref missing, ref missing, ref missing);

     

    // To open an existing word document we need to pass the path of the existing document with some // other parameters. Here is the code snippet opening an existing word document

     

    object fileName = “Path_of_the_file_to_Open”;

    // You can keep it true if you want to open the file in readonly mode

    object readOnly = false;

    // we can keep it false if you want to open the file but not make it invisible

    object isVisible = true;

    oWordDoc = oWord.Documents.Open(ref fileName, ref missing, ref 

                      readOnly, ref missing, ref missing, ref missing, ref 

                      missing, ref missing, ref missing, ref missing, ref

                      missing, ref isVisible, ref missing, ref missing, ref

                      missing, ref missing);

     

    To close the word document properly. We first need to close the word document and then the word application. If we do not close the word application then the memory will never be released for the word application.

    //Closing the file

    oWordDoc.Close(ref oFalse, ref missing, ref missing);

     

    //Quitting the word application to release the memory.

    oWord.Quit(ref missing, ref missing, ref missing);

    Vikram