Automating Microsoft Excel and Word Together





3.00/5 (5 votes)
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
- Open Visual Basic.
- Select a new ActiveX DLL project.
- You will get a class by default.
- Name the project (say, M2MSOfficeCOM).
- Name your class. We are first automating Excel, so I have given the name “
clsExcel
”. - From the References menu, add a reference to the Microsoft Excel 10 object library.
- Add these declarations:
- In the class’ initializer, add the following code:
- Now, to add a new Excel sheet, use the following code:
- Now to add text to the cell:
Private xlApp As Excel.Application
Private xlBook As Excel.Workbook
Private xlSheet As Excel.Worksheet
Private csExcelStatus As String ‘for getting status
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.
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.
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.
- Add another class file and name it “
clsWord
”. - Add a reference to the “Microsoft Word 10.0 object library”.
- Declare these:
- In the initializer, add this code:
- The rest of the operations are very simple.
Adding text is done as:
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
Private wrdApp As Word.Application
Private csWordStatus As String
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
The capabilities of this class includes:
- Adding a heading
- Adding ordinary text
- Adding a header
- Adding a footer
- Shading the heading
- Adding a page number
Calling this DLL from VC++
- 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
andclsWord
, will be displayed. Select both, and click OK. - Now, add the header file of the two new classes added to the project in your dialog class.
- Place two buttons and name them
Excel
andWord
. - Now code the Excel button as follows. The demo involves:
- Creating an Excel sheet
- Setting its visibility to true
- Adding a sheet and naming it
- Adding a value to Cell-1
- Changing the back ground color of Cell-1
- Changing the column width of Column-1
- Saving the file
- Now, code the Word button as follows. The demo involves:
- Creating a document
- Making it visible
- Adding a header to it
- Adding a heading to it with shade
- Adding ordinary text
- Adding footer
- Adding page number
The code is given below:
//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();
The code is as follows:
//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:
- The DLL project in VB
- A test project in VB
- A test project in VC