Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles / Languages / VB

Automating Microsoft Excel and Word Together

3.00/5 (5 votes)
2 Dec 2005CPOL2 min read 2   1.6K  
Automating Word and Excel together into one DLL in Visual Basic so that it can be used in VC++ with ease.

Introduction

In many cases, it is required to use MS Excel or Word in our VC++ applications. Automating them in VC is easy, but when it is done as a DLL in VB, it is simply a few lines of code, and the result lies in its simplicity. Here, I have designed a DLL in MS Visual Basic which automates both MS Excel and MS Word. Upon importing the DLL to our VC workspace, we can use both these capabilities.

Automating Excel

  1. Open Visual Basic.
  2. Select a new ActiveX DLL project.
  3. You will get a class by default.
  4. Name the project (say, M2MSOfficeCOM).
  5. Name your class. We are first automating Excel, so I have given the name “clsExcel”.
  6. From the References menu, add a reference to the Microsoft Excel 10 object library.
  7. Add these declarations:
  8. VB
    Private xlApp As Excel.Application
    Private xlBook As Excel.Workbook
    Private xlSheet As Excel.Worksheet
    Private csExcelStatus As String  ‘for getting status
  9. In the class’ initializer, add the following code:
  10. VB
    Private Sub Class_Initialize()
        csExcelStatus = "Inuitializing COM"
        On Error GoTo ErrorHandler
            Set xlApp = CreateObject("Excel.Application")
            csExcelStatus = "Successfully Initialized COM"
            Exit Sub
    ErrorHandler:
    csExcelStatus = "Failed to Initialize COM"
    End Sub

    Here, you have created an object of Excel.

  11. Now, to add a new Excel sheet, use the following code:
  12. VB
    Public Function AddNewSheet(SheetName As String)
        Set xlBook = xlApp.Workbooks.Add(xlWBATWorksheet)
        With xlApp
            .ActiveSheet.Name = SheetName
        End With
        
        Set xlSheet = xlApp.ActiveSheet
    End Function

    Here, a new sheet is created which is added to the book, and is named with the passed SheetName parameter.

  13. Now to add text to the cell:
  14. VB
    Public Function AddTextToCell(RowID As Integer, ColID As Integer, Text As String)
            xlSheet.Cells(RowID, ColID).Value = Text
    End Function

    Similarly, check the rest of the code in the attached files.

Automating Word

Now, the steps for automating Word.

  1. Add another class file and name it “clsWord”.
  2. Add a reference to the “Microsoft Word 10.0 object library”.
  3. Declare these:
  4. VB
    Private wrdApp As Word.Application
    Private csWordStatus As String
  5. In the initializer, add this code:
  6. VB
    Private Sub Class_Initialize()
    On Error GoTo fin
        Set wrdApp = CreateObject("Word.Application")
        wrdApp.Documents.Add
        csWordStatus = "Initializtion Successfull"
    Exit Sub
    
    fin:
      csWordStatus = "Initializtion Failed"
    End Sub
    
    
    
    <li>The rest of the operations are very simple.
    
    Adding text is done as:
    
    
    <pre lang="vbnet">Public Function AddText(Text As String)
    On Error GoTo fin
        wrdApp.Documents.Application.Selection.TypeText Text
        Exit Function
    fin:
        csWordStatus = "Text Entry Failed"
    End Function

The capabilities of this class includes:

  1. Adding a heading
  2. Adding ordinary text
  3. Adding a header
  4. Adding a footer
  5. Shading the heading
  6. Adding a page number

Calling this DLL from VC++

  1. Open a VC++ dialog based application. From the View Class wizard, click “Add class from a type library”. Browse and select the compiled DLL. The two classes, namely clsExcel and clsWord, will be displayed. Select both, and click OK.
  2. Now, add the header file of the two new classes added to the project in your dialog class.
  3. Place two buttons and name them Excel and Word.
  4. Now code the Excel button as follows. The demo involves:
    1. Creating an Excel sheet
    2. Setting its visibility to true
    3. Adding a sheet and naming it
    4. Adding a value to Cell-1
    5. Changing the back ground color of Cell-1
    6. Changing the column width of Column-1
    7. Saving the file

    The code is given below:

    C++
    //Initialize Com Object
    CoInitialize(NULL);
    
    //Create Object for Excel
    _clsExcel objXl;
    objXl.CreateDispatch("M2MSOfficeCOM.clsExcel");
    
    //Make Excel Application visible
    objXl.SetVisible(true);
    
    
    CString str="Analysis";
    BSTR bst=str.AllocSysString(); 
    objXl.AddNewSheet(&bst);
    //Add a new Sheet name it "Analysis"
    
    short sRow=1,sCol=1;    //In Cell 1 add string "Analysis"
    objXl.AddTextToCell( &sRow,&sCol,&bst);
    
    long colorID=46;//Chnge bgcolor cell-1
    objXl.BackGroungColorToCell( &sRow,&sCol,&colorID);
    
    str="A1";
    bst=str.AllocSysString(); 
    short sColWidth=20;      //Change column width of Cell -1
    objXl.SetColWidth( &bst,&sColWidth);
    
    str="C:\\Analysis.xls";//save the file name it "Analysis.xls"
    bst=str.AllocSysString(); 
    objXl.Save(&bst); 
    
    objXl.DetachDispatch(); //clean up
    CoUninitialize();
  5. Now, code the Word button as follows. The demo involves:
    1. Creating a document
    2. Making it visible
    3. Adding a header to it
    4. Adding a heading to it with shade
    5. Adding ordinary text
    6. Adding footer
    7. Adding page number

    The code is as follows:

    C++
    //Initialize Com Object
    CoInitialize(NULL); 
    _clsWord objWrd;
    objWrd.CreateDispatch("M2MSOfficeCOM.clsWord");
    
    //Make word Application visible
    objWrd.SetVisible(true); 
    
    CString str="My chapter-1";
    BSTR bst=str.AllocSysString(); 
    objWrd.AddHeader( &bst);       //Add header to document
    
    long foreColor=8,shading=12;
    objWrd.Heading(&bst,&foreColor,&shading);
    //Add heading with forecolor and bgcolor
    
    //Add a line of text
    str="This can be done in a number of ways especially" + 
        " when yoo are tired of sending many mails";
    bst=str.AllocSysString(); 
    objWrd.AddText(&bst); 
    
    //Add page footer
    str="Ourcompany";
    bst=str.AllocSysString(); 
    objWrd.AddHeader(&bst); 
    
    //add Page number.
    objWrd.AddPageNumber();
    
    //sace the file
    str="c:\\ss";       //file name
    bst=str.AllocSysString();
    
    str="";   //Password if any
    BSTR bstPass=str.AllocSysString();
    
    //save
    objWrd.Save(&bst,&bstPass);
    
    objWrd.DetachDispatch(); //clean up
    CoUninitialize();

Hope this will be useful…The same DLL can also be used with other Visual Basic applications. Just add a reference to the DLL.

Downloads

The downloads include:

  1. The DLL project in VB
  2. A test project in VB
  3. A test project in VC

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)