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:
Private xlApp As Excel.Application
Private xlBook As Excel.Workbook
Private xlSheet As Excel.Worksheet
Private csExcelStatus As String ‘for getting status
- In the class’ initializer, add the following code:
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.
- Now, to add a new Excel sheet, use the following code:
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.
- Now to add text to the cell:
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:
Private wrdApp As Word.Application
Private csWordStatus As String
- In the initializer, add this code:
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:
- 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
and clsWord
, 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
and Word
. - 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
The code is given below:
CoInitialize(NULL);
_clsExcel objXl;
objXl.CreateDispatch("M2MSOfficeCOM.clsExcel");
objXl.SetVisible(true);
CString str="Analysis";
BSTR bst=str.AllocSysString();
objXl.AddNewSheet(&bst);
short sRow=1,sCol=1; objXl.AddTextToCell( &sRow,&sCol,&bst);
long colorID=46;objXl.BackGroungColorToCell( &sRow,&sCol,&colorID);
str="A1";
bst=str.AllocSysString();
short sColWidth=20; objXl.SetColWidth( &bst,&sColWidth);
str="C:\\Analysis.xls";bst=str.AllocSysString();
objXl.Save(&bst);
objXl.DetachDispatch(); CoUninitialize();
- 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 as follows:
CoInitialize(NULL);
_clsWord objWrd;
objWrd.CreateDispatch("M2MSOfficeCOM.clsWord");
objWrd.SetVisible(true);
CString str="My chapter-1";
BSTR bst=str.AllocSysString();
objWrd.AddHeader( &bst);
long foreColor=8,shading=12;
objWrd.Heading(&bst,&foreColor,&shading);
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);
str="Ourcompany";
bst=str.AllocSysString();
objWrd.AddHeader(&bst);
objWrd.AddPageNumber();
str="c:\\ss"; bst=str.AllocSysString();
str=""; BSTR bstPass=str.AllocSysString();
objWrd.Save(&bst,&bstPass);
objWrd.DetachDispatch(); 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