Click here to Skip to main content
15,667,231 members
Articles / Programming Languages / Visual Basic
Posted 2 Dec 2005


30 bookmarked

Automating Microsoft Excel and Word Together

Rate me:
Please Sign up or sign in to vote.
3.00/5 (5 votes)
2 Dec 2005CPOL2 min read
Automating Word and Excel together into one DLL in Visual Basic so that it can be used in VC++ with ease.


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
    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")
        csWordStatus = "Initializtion Successfull"
    Exit Sub
      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
        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:

    //Initialize Com Object
    //Create Object for Excel
    _clsExcel objXl;
    //Make Excel Application visible
    CString str="Analysis";
    BSTR bst=str.AllocSysString(); 
    //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);
    short sColWidth=20;      //Change column width of Cell -1
    objXl.SetColWidth( &bst,&sColWidth);
    str="C:\\Analysis.xls";//save the file name it "Analysis.xls"
    objXl.DetachDispatch(); //clean up
  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:

    //Initialize Com Object
    _clsWord objWrd;
    //Make word Application visible
    CString str="My chapter-1";
    BSTR bst=str.AllocSysString(); 
    objWrd.AddHeader( &bst);       //Add header to document
    long foreColor=8,shading=12;
    //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";
    //Add page footer
    //add Page number.
    //sace the file
    str="c:\\ss";       //file name
    str="";   //Password if any
    BSTR bstPass=str.AllocSysString();
    objWrd.DetachDispatch(); //clean up

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


The downloads include:

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


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

Written By
Software Developer
India India
Software Engineer,
Technopark, Kerala.

Rx 135 Owner
Yamaha Fan.

Comments and Discussions

GeneralChange in Dll Pin
MsmVc21-Aug-09 0:57
MsmVc21-Aug-09 0:57 
GeneralError message Pin
MsmVc19-Aug-09 23:52
MsmVc19-Aug-09 23:52 
GeneralRe: Error message Pin
MsmVc21-Aug-09 0:55
MsmVc21-Aug-09 0:55 
QuestionOne question about array Pin
urwangshd22-Nov-07 8:26
urwangshd22-Nov-07 8:26 
GeneralExcellent! Pin
primey1-Oct-06 16:04
primey1-Oct-06 16:04 
GeneralNot Working Pin
Kaleelindia30-Aug-06 1:58
Kaleelindia30-Aug-06 1:58 
Generalnothing happened!! Pin
kuelite11-Aug-06 17:24
kuelite11-Aug-06 17:24 
GeneralRe: nothing happened!! Pin
Smith_TVPM13-Aug-06 6:16
Smith_TVPM13-Aug-06 6:16 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.