Automating Microsoft Excel and Word Together

• Download source files - 15.6 KB
• Download demo project - 19.7 KB
• Download demo in VB - 1.5 KB

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:
VB

Private xlApp As Excel.Application
Private xlBook As Excel.Workbook
Private xlSheet As Excel.Worksheet
Private csExcelStatus As String  ‘for getting status

8. In the class’ initializer, add the following code:
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.

9. Now, to add a new Excel sheet, use the following code:
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.

10. Now to add text to the cell:
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:
VB

Private wrdApp As Word.Application
Private csWordStatus As String

4. In the initializer, add this code:
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