The addition of the Visual Basic for Applications (VBA) language into Visual Basic 4.0 opens up a new frontier for Visual Basic applications. This frontier allows you to incorporate the full functionality of applications such as Microsoft Excel and Word
directly into Visual Basic applications. This incorporation is accomplished through OLE automation. This chapter explores the interaction between Visual Basic 4.0 and Microsoft Office and outlines methods for incorporating this powerful functionality into
applications developed in Visual Basic 4.0.
The OLE container control provides a method of displaying and manipulating Microsoft Office (and other graphical) objects directly from your Visual Basic application. A container as it is applied to Visual Basic is an item (usually a control) that
contains another item. For example, if I place a frame control on a form and then place a text box inside of it, the frame is a container to the text box. Using this same principle, you can add a Microsoft Excel spreadsheet or a Microsoft Word document
directly to a Visual Basic form using the OLE Container control. The spreadsheet or document is contained within the OLE Container control.
To display another application's object in your application, that object must be visible to Visual Basic. There are two types of objects available for use by Visual Basic applications: custom controls and references. Custom controls mirror the controls
available with Visual Basic 3. The Visual Basic 4.0 version of the custom control has the extension OCX (instead of VBX). In Visual Basic 3, the File | Add File menu option was selected to insert a custom control into an application. In Visual Basic 4.0,
this method has been simplified: select Tools | Custom Controls from the main menu. A dialog box appears, providing a list of all custom controls and insertable items available for use by Visual Basic applications (see Figure 24.1). An example of an
insertable item is a Microsoft Excel chart. Although the chart is not an OCX object, it is a tangible object that can be added to an application. Once an OCX or OLE object is included in a Visual Basic application, its properties and methods can be
accessed directly from code. To include a control or an OLE object, simply select its checkbox in the dialog box. Once you have selected the items you want to include in your application, choose OK. The items you select are added to the Visual Basic
toolbar.
Figure 24.1. The Visual Basic Custom Controls dialog box displays the available OCX controls and OLE objects.
Note
You can use one of two methods when you want to include an object in a Visual Basic application using the OLE control: linking or embedding.
Linking allows you to directly edit an object's data from within Visual Basic. When you link, the object in the OLE container is linked directly to the data it is accessing. Any actions taken on the data from the OLE control act on the original data.
For example, use linking if you want to allow your users to edit a Microsoft Word document directly from Visual Basic and directly apply any changes made to the document in Visual Basic to the Word document file. To achieve this, follow these steps:
Figure 24.2. The Insert Object dialog box after the Create From File option is selected. Select the Link checkbox to link the OLE control to its data. Leave this checkbox deselected to store a copy of the
data in the control.
These steps have established a link with the Word document's data using the OLE control. Imagine that the document is updated by another user after you displayed it with your OLE control. To view the changes made to the document, use the OLE control's
update method. Do the following:
The data displayed in the OLE control is updated to reflect the changes you made to the document.
In this case, the OLE control is directly connected to the original data. Clicking the command button updates the data displayed in the control. You can also change the document directly from the Visual Basic application by following these steps:
The updated data is displayed in the OLE control. In this example, you are acting directly on the original data. Any changes made by the application are made to this data; any changes made to this data by other applications can be viewed by the
application by using the update method (that is, by clicking the command button).
The second method through which the OLE control displays data is embedding. When you embed an object, the control maintains a copy of the data within itself. This is a private copy of the data and is not inherently available to other applications. If
changes are made to the data, the original data is unaffected. Here are the steps for embedding a Word document into an application:
The primary difference between the steps for linking and those for embedding is the selection of the Link checkbox. Because the preceding steps did not select the Link checkbox, you are provided with a copy of the data. When this copy is updated, the
original data remains unchanged.
If you close the application without saving the data, the data within the OLE control is lost. To save your copy of the data, use the SaveToFile method. The following code saves the contents of the OLE control to a file:
Private Sub cmdSaveToFileOLE_Click()
iFileNum = FreeFile
Open "C:\PROJECTS\PROJECT1\REPORT.TXT" For Binary Lock Write As x
OLE1.SaveToFile iFileNum
Close iFileNum
End Sub
The preceding code saves the contents of the control to a file. To get the contents from the file and place them into the control, use the ReadFromFile method. The following sample code accomplishes this:
Private Sub cmdReadFromFileOLE_Click()
iFileNum = FreeFile
Open "C:\PROJECTS\PROJECT1\REPORT.TXT" For Binary Lock Write As x
OLE1.ReadFromFile iFileNum
Close iFileNum
End Sub
It is important to note here that the file must be opened for Binary when you both read and write the file. Because this is the case, the file should be edited only from within Visual Basic. Additionally, only data placed in the file using the
SaveToFile method can be retrieved using the ReadFromFile method.
The data from multiple objects can be saved to the same file. It is important to understand, however, that the data must be removed from the file in the same order in which it was added. If you do not remove the data in the proper order, the data you
display (if the data displays at all) will be incorrect. This is logical because the data is stored in a file. Once an object's data is written to the file, the file pointer is at a certain position. The data for additional objects is added beginning with
the first byte after the end of the last object's data. The ReadFromFile method does not allow you to specify where in the file you begin reading data.
If you have used DDE in the past, you may be wondering what differences exist between DDE and OLE and when each is appropriate. OLE is a far better standard than DDE and is the preferred method for use whenever possible. DDE is a method of exchanging
only textual information. When you request data from an application using DDE, you receive the data and the data only. On the other hand, OLE offers the use of any objects an application exposes. For example, if you request data from a Word document using
DDE, you receive the data and nothing more. If you use OLE, you receive the data and, in addition, you are afforded the use of Word's spelling checker. Using OLE Automation, you can borrow the functionality of the spelling checker from Word. Borrowing the
spelling checker eliminates the need to incorporate a spell-check utility in your Visual Basic application.
The key is to understand that the end user must also be able to access any objects you incorporate into an application. Using the Word example, the user is required to have a copy of Microsoft Word installed on his or her system for your application to
successfully access the spelling checker (or any other object Microsoft Word exposes).
In OLE Automation, an application exposes its objects to other applications for use. For example, Microsoft Excel exposes a spreadsheet object. An instance of the spreadsheet object can be created by other applications and the properties and methods of
the spreadsheet can then be accessed. Each object exposed in OLE Automation has a hierarchy. This hierarchy is the path through which an object and its properties and methods are accessed. This is not an unfamiliar concept. In Visual Basic, a picture box
is accessed through its form. To access the picture box, you must call the form, as shown here:
Form1.Picture1.BackColor = RGB(255,255,255)
This code changes the backcolor property of the picture control contained on Form1. OLE Automation operates in the same manner. An Excel workbook has in its hierarchy a spreadsheet object. This spreadsheet object has cells. A range of cells can be
selected on a spreadsheet object by using the Range property. To access a range of cells on a spreadsheet that is a part of a workbook, use the following code:
objSpreadSheet1.Range(objSpreadSheet1.Cells(1, 2), objSpreadSheet1.Cells(1, 5))
To access an object's properties and methods, an instance of the object must first be created. In the previous example, objSpreadSheet1 is an instance of a spreadsheet object that Microsoft Excel exposes. In the next section of this chapter, you create
an Excel application piece by piece directly from Visual Basic. Each step adds an object that Excel exposes and then manipulates its properties and methods.
If you want to create a spreadsheet, you must follow the hierarchy of Excel. To create the spreadsheet, you must first create an instance of Excel and then an instance of an Excel workbook. Each of these elements of Excel are objects Excel exposes. The
following example creates an instance of Excel and adds a workbook to the Excel instance:
'Declare the Excel object variable
Dim objApp As Object
'Declare a workbook object
Dim objWorkBook As Object
'Set the application object to an instance of Excel
Set objApp = CreateObject("Excel.Application")
'Set the workbook object to a new workbook using the add method.
Set objWorkBook = objApp.WorkBooks.Add
This instance of Excel is an OLE object that Excel exposes. You can access any of the properties and methods of the Excel object using OLE Automation. In this case, we created a workbook using the workbook's Add method. Notice that each of the objects
is assigned a variable (objApp and objWorkBook). These variables are then used to address the created object.
The Excel instance and the workbook are now objects in memory. These objects are not yet visible. Once you create a workbook, you are provided with several spreadsheets by default. Imagine that you want to manipulate the first spreadsheet in the
workbook. Before you can manipulate it, you have to declare an object to represent it. Referring to the object hierarchy, add the following code:
'Declare a spreadsheet object Dim objSpreadSheet As Object 'Set the spreadsheet object to the first (active) sheet of the excel workbook. Set objSpreadSheet1 = objApp.ActiveWorkbook.ActiveSheet
This code creates a new spreadsheet object and gives it the focus. It also assigns the application workbook's active sheet the variable objSpreadSheet1. This eliminates the need to continually refer to an ever increasing hierarchy of items directly.
You can use the following code to assign a value to the Font.Size property:
objApp.ActiveWorkBook.ActiveSheet.Range.Font.Size = 14
However, if you assign the spreadsheet range a variable name (objSpreadSheetRange) with the statement Dim objSpreadSheetRange = objApp.ActiveWorkBook.ActiveSheet.Range, you can accomplish what the preceding line of code did by using this shorter line:
objSpreadSheetRange.Font.Size = 14
This next example selects a range of cells through Visual Basic code:
Set objSpreadSheet1Range = objSpreadSheet1.Range(objSpreadSheet1.Cells(1, 1), objSpreadSheet1.Cells(36, 16))
Now change the color of the selected cells (the preceding statement stored the range of cells in the objSpreadSheet1Range variable):
objSpreadSheet1Range.Interior.Color = RGB(125, 125, 125)
This statement used the color property to change the color of the cells (seems logical). However, it is important to note that in the hierarchy of Excel, the spreadsheet range does not have a color property. The spreadsheet range does have an Interior
object, however, and this object has a color property. The following line results in runtime error 1000:
objSpreadSheet1Range.Color = RGB(125, 125, 125)
The error occurred because the range does not have a color property; the statement failed to access the color property through the Interior object. This understanding of objects and their hierarchy is one of the most important concepts in OLE
Automation.
The proper statement to set this property is:
objSpreadSheet1Range.Interior.Color = RGB(125, 125, 125)
While you are manipulating cells in a spreadsheet, change the font characteristics. The following example assumes that you have selected some cells. The example sets the font size of the cell and applies a bold look:
objSpreadSheet1Range.Font.Size = 12 objSpreadSheet1Range.Font.Bold = True
In this example, the font size (Font.Size) and boldness (Font.Bold) of the selected range of cells that belong to the spreadsheet (objSpreadSheet1Range), that belongs to the workbook (objWorkBook), that belongs to the application (objApp) is changed.
How are you doing?
Note
Imagine that you have created an instance of Excel (an Excel object) and that you have also created a workbook. By default, this workbook contains several spreadsheets; you have set the focus to the first spreadsheet. Now imagine that you want to add
some command buttons to the spreadsheet. The following code adds several command buttons to the spreadsheet. It sets the positions of the buttons, sets the font to bold, and sets the size of the text on the button to 8 point:
For X = 33 To 153 Step 30
objSpreadSheet1.Buttons.Add 153, X, 80, 24
objSpreadSheet1.Buttons.Font.Bold = False
objSpreadSheet1.Buttons.Font.Size = 8
Next X
The reason that the For loop runs from 33 to 153 is that these are positions used to set the command buttons to the desired positions. Now that you have added command buttons to the spreadsheet, add the desired text:
objSpreadSheet1.Buttons(1).TEXT = "SelectA1" objSpreadSheet1.Buttons(2).TEXT = "SetCells" objSpreadSheet1.Buttons(3).TEXT = "ClearCells" objSpreadSheet1.Buttons(4).TEXT = "SelectCells" objSpreadSheet1.Buttons(5).TEXT = "SetActiveCell"
To provide functionality to the command buttons, create a module of functions to attach to the buttons. Create the module in your Visual Basic application. The saved module is added to the Excel application with the following code:
Set objModule1 = objWorkBook.Modules.Add(, objSpreadSheet1, 1) objModule1.Activate objModule1.InsertFile filename:="C:\PROJECTS\MODULE1.TXT"
The module is a text file created in Excel and is simply a text file that contains functions. It is also possible to create the functions in a Visual Basic module and export that module to the Excel application as a text file. The following section
lists the contents of the module.
The functions in the module added to the Excel application demonstrate some of the auto-mation capabilities of VBA. These functions can just as easily be implemented directly from Visual Basic.
The first function selects the cell at location A:1
Sub SelectA1()
'This function selects cell A1
Range(Cells(1, 1), Cells(1, 1)).Select
End Sub
The next function selects 10 rows down and 10 columns to the right from the currently selected cell:
Sub SelectCells10X10()
'Selects 10 rows and 10 columns adjacent to the active (currently selected)
'cell.
iRow = 10 'set number of rows to move to 10
iCol = 10 'set number of columns to move to 10
Selection.Resize(iRow, iCol).Select 'select the cells
End Sub
The following function is similar to the preceding one but it prompts the user for the number of cells to be selected in both directions:
Sub SetActiveCell()
'Changes the position of the active cell by the number of rows and columns
'selected.
Dim iRow, iCol
iRow = InputBox("Enter the number of rows to move", "Enter Rows", 1)
iCol = InputBox("Enter the number of columns to move", "Enter Columns", 1)
iRow = Abs(iRow) 'use the InputBox function to set the
'number of rows
iCol = Abs(iCol) 'use the InputBox function to set the
'number of rows
Selection.Offset(iRow, iCol).Select 'select the new cell
End Sub
The next function places a value in cells A1 through A10. These values are formatted using the properties bold, italic, and so on. Note that the With...End With statement is used here. This statement allows you to refer to an object without continually
retyping its name:
Sub SetCellValueAndFormat()
'Sets the value in the first 20 cells of column A to the current value of x and
'makes the 'font bold,italic and underlined. .Offset(x, 0).Select selects the
'next cell in the column.
Range(Cells(1, 1), Cells(1, 1)).Select
For x = 1 To 20 'begin Loop
With ActiveCell 'working on the active cell
.Value = x - 2 'set the value in the cell to the current value
'of x
.Font.Size = 10 'set the font size to 14
.Font.Bold = True 'set the cell font to bold
.Font.Italic = True 'italicize the cell text
.Offset(1, 0).Select 'Select the next cell down (A1, A2, A3, etc.)
End With 'close the With statement
Next x 'Loop to the next x value
End Sub
The next function clears the values placed in the cells with the SetCellValueAndFormat function and also resets the formatting:
Sub ClearCellValueandFormat()
'Clears the values and formatting set in the function SetCellValueandFormat()
Range(Cells(1, 1), Cells(1, 1)).Select
For x = 1 To 20 'begin Loop
With ActiveCell 'working on the active cell
.Value = "" 'set the value in the cell to NULL
.Font.Bold = False 'turn off the bold
.Font.Italic = False 'turn off the italics
.Offset(1, 0).Select 'Select the next cell down (A1, A2,etc.)
'.Offset(rows,columns).Select
End With 'close the With statement
Next x 'Loop to the next x value
End Sub
Once you add the module to your application-to-be, attach the functions to the command buttons on the spreadsheet. This is accomplished with the OnAction property:
objSpreadSheet1.Buttons(1).OnAction = "SelectA1" objSpreadSheet1.Buttons(2).OnAction = "SetCellValueAndFormat" objSpreadSheet1.Buttons(3).OnAction = "ClearCellValueAndFormat" objSpreadSheet1.Buttons(4).OnAction = "SelectCells10X10" objSpreadSheet1.Buttons(5).OnAction = "SetActiveCell"
So far, you have created and formatted a spreadsheet. The following instructions add some additional data to the spreadsheet that is used later to create a chart.
First, label the spreadsheet data:
'Set the range of cells that we will be working with (one cell). Set objSpreadSheet1Range = objSpreadSheet1.Range(objSpreadSheet1.Cells(3, 2), _ objSpreadSheet1.Cells(3, 2)) 'Place the Chart Data label in the selected cell. objSpreadSheet1Range.Characters.Caption = "Chart Data:"
Next, place the data for the chart on the spreadsheet:
For X = 4 To 13
'Set the range of cells. The range is 1 cell and changes each time through
'the loop.
Set objSpreadSheet1Range = objSpreadSheet1.Range(objSpreadSheet1.Cells(X,
_2), _objSpreadSheet1.Cells(X, 2))
'This is a bogus value (X * 5) to be placed in the current cell.
sValue = X * 5
'Place the value in the cell.
objSpreadSheet1Range.Characters.Caption = sValue
Next X
Before wrapping up the spreadsheet portion of the application, add a Microsoft Word document to your spreadsheet. To do this, you must first select the size of the document. This is done by selecting a range of cells for the document's location:
Set objSpreadSheet1Range = objSpreadSheet1.Range(objSpreadSheet1.Cells(4, 2), _ objSpreadSheet1.Cells(13, 2))
Next, add the document to the spreadsheet. The following line of code adds the document and displays the contents of a Microsoft Word file:
objSpreadSheet1.OLEObjects.Add filename:="C:\PROJECTS\OLE.DOC"
You can manipulate the size of the Word document in the following manner:
objSpreadSheet1.OLEObjects(1).Height = 200 objSpreadSheet1.OLEObjects(1).Width = 250 objSpreadSheet1.OLEObjects(1).TOP = 30 objSpreadSheet1.OLEObjects(1).Left = 300
This completes the spreadsheet portion of the application. To summarize, you have created an Excel object and added a workbook to that object. The workbook automatically provided several spreadsheets; you set the focus to the first spreadsheet and
formatted it (with colors, fonts, and so on). Then you added command buttons and attached the functions in a module to the command buttons. You then created some data for the yet-to-be-created chart to access. Finally, you added a Microsoft Word document
object to the spreadsheet and displayed a Word document file in the object.
The next step is to create a chart for the application. The following line of code adds the chart:
Set Chart1 = objWorkBook.Charts.Add(, objSpreadSheet1, 1)
Next, you have to point the chart to the data on the spreadsheet:
Chart1.ChartWizard Source:=objSpreadSheet1Range
The Excel Chart object provides the ChartWizard object to completely format the chart. The wizard's properties and methods provide the same level of manipulation you can achieve manually.
And there you have it: a completed application. The only thing left to do is to set the application to Visible so that it can be seen. These lines of code make the application and the chart visible:
objApp.Visible = True Chart1.Visible = True
When the application appears, you want the user to see the spreadsheet; add this line to display the first spreadsheet and give it the focus:
objSpreadSheet1.Activate
Now that you are finished creating the application, it is very important that you destroy the object variables you have created. If you are familiar with GDI functions, you know that if you create a brush and do not destroy it, you have a serious memory
leak. It is the same with objects: If you do not destroy object variables, you will cause your users great distress. The following lines destroy the object variables created so far:
Set objApp = Nothing Set objWorkBook = Nothing Set objSpreadSheet1 = Nothing Set objSpreadSheet1Range = Nothing Set Chart1 = Nothing
Also remember to consider the creation of objects when debugging OLE Automation code. In this example, the Excel application is not rendered visible until nearly the last line. If you continually step through your code creating objects, but break before
you delete them, you will quickly run out of memory. Additionally, when you exit Windows, you are asked whether or not you want to save the changes made to the newly created application. If this occurs, answer No.
Listing 24.1 shows the complete application. It is important to note that this application starts with nothing. It creates an instance of Excel, adds objects and code to it, and creates a fully functional Excel application directly from Visual Basic.
Function CreateWorkBook()
Dim objApp As Object
Dim objWorkBook As Object
Dim objSpreadSheet As Object
Screen.MousePointer = 11
Set objApp = CreateObject("Excel.Application")
Set objWorkBook = objApp.WorkBooks.Add
Set objSpreadSheet1 = objApp.ActiveWorkbook.ActiveSheet
Set objSpreadSheet1Range = objSpreadSheet1.Range(objSpreadSheet1.Cells(1, 1), _
_objSpreadSheet1.Cells(36, 16))
objSpreadSheet1Range.Interior.Color = RGB(0, 255, 255)
Set objSpreadSheet1Range = objSpreadSheet1.Range(objSpreadSheet1.Cells(3, 4), _
_objSpreadSheet1.Cells(14, 5)) 'row/column
objSpreadSheet1Range.Interior.Color = RGB(125, 125, 125)
Set objSpreadSheet1Range = objSpreadSheet1.Range(objSpreadSheet1.Cells(1, 2), _
_objSpreadSheet1.Cells(1, 2))
objSpreadSheet1Range.Font.Size = 12
objSpreadSheet1Range.Font.Bold = True
objSpreadSheet1Range.Characters.Caption = _
"Visual Basic 4 Unleashed - Chapter 2 - Microsoft Office Application developed from
_within Visual Basic"
For X = 33 To 153 Step 30
objSpreadSheet1.Buttons.Add 153, X, 80, 24
objSpreadSheet1.Buttons.Font.Bold = False
objSpreadSheet1.Buttons.Font.Size = 8
Next X
objSpreadSheet1.Buttons(1).TEXT = "SelectA1"
objSpreadSheet1.Buttons(1).OnAction = "SelectA1"
objSpreadSheet1.Buttons(2).TEXT = "SetCells"
objSpreadSheet1.Buttons(2).OnAction = "SetCellValueAndFormat"
objSpreadSheet1.Buttons(3).TEXT = "ClearCells"
objSpreadSheet1.Buttons(3).OnAction = "ClearCellValueAndFormat"
objSpreadSheet1.Buttons(4).TEXT = "SelectCells"
objSpreadSheet1.Buttons(4).OnAction = "SelectCells10X10"
objSpreadSheet1.Buttons(5).TEXT = "SetActiveCell"
objSpreadSheet1.Buttons(5).OnAction = "SetActiveCell"
Set objModule1 = objWorkBook.Modules.Add(, objSpreadSheet1, 1)
objModule1.Activate
objModule1.InsertFile filename:="c:\temp\temp3\module1.txt"
(The file name is the same as the Visual Basic module created earlier)
'Chart
Set objSpreadSheet1Range = objSpreadSheet1.Range(objSpreadSheet1.Cells(3, 2), _
_objSpreadSheet1.Cells(3, 2))
objSpreadSheet1Range.Characters.Caption = "Chart Data:"
For X = 4 To 13
Set objSpreadSheet1Range = objSpreadSheet1.Range(objSpreadSheet1.Cells
_(X, 2), _objSpreadSheet1.Cells(X, 2))
sValue = X * 5
objSpreadSheet1Range.Characters.Caption = sValue
Next X
Set objSpreadSheet1Range = objSpreadSheet1.Range(objSpreadSheet1.Cells(4, 2), _
_objSpreadSheet1.Cells(13, 2))
objSpreadSheet1.OLEObjects.Add filename:="c:\winword\moving.doc"
objSpreadSheet1.OLEObjects(1).Height = 200
objSpreadSheet1.OLEObjects(1).Width = 250
objSpreadSheet1.OLEObjects(1).TOP = 30
objSpreadSheet1.OLEObjects(1).Left = 300
objSpreadSheet1.OLEObjects.Add classtype:="MSGraph"
Set Chart1 = objWorkBook.Charts.Add(, objSpreadSheet1, 1)
Chart1.ChartWizard Source:=objSpreadSheet1Range
objApp.Visible = True
Chart1.Visible = True
objSpreadSheet1.Activate
Set objApp = Nothing
Set objWorkBook = Nothing
Set objSpreadSheet1 = Nothing
Set objSpreadSheet1Range = Nothing
Set Chart1 = Nothing
Screen.MousePointer = 0
End Function
When you run this application, a fully functional Microsoft Excel application is created. This application is included on the CD-ROM that accompanies this book. Once the application is created, the user can manually exit the application and save
changes. Saving the changes creates a permanent copy of the application. If you want to close and destroy the application using Visual Basic code, do the following:
Sub CloseExcel()
iRet% = MsgBox("Click OK to close spreadsheet or Cancel to leave spreadsheet open", _1, "Close Spreadsheet")
If iRet% = 2 Then Exit Sub
objSpreadSheet.Application = Nothing
Set objSpreadSheet = Nothing
End Sub
When you create a spreadsheet from Visual Basic, a copy of Excel may already be open. To let Windows know that you are destroying an Excel instance created by Visual Basic, use the CloseExcel() function (see the preceding code). Do not use
objSpreadSheet.Application.Quit. Because Windows is unsure whether a copy of Excel was already open, this second approach merely flashes the title bar. Only when the application is given the focus does the Save Changes dialog box appear.
Once the application is created, you can access it without re-creating it. In the same way, any previously created spreadsheet can be accessed with Visual Basic. The following example opens a preexisting spreadsheet and adds a chart:
Sub GetReportData()
Dim X As Integer
'The GetObject function retrieves an OLE automation object from a file.
Set objSpreadSheet = GetObject("c:\msoffice\excel\conrad.xls")
'Set up values for the chart. This is done by prompting the user for values.
For X = 1 To 5
Do
iValue = InputBox("Enter a value", "Create Chart", 0)
Loop Until CStr(Val(iValue)) = iValue
objSpreadSheet.Cells(1, X).VALUE = iValue
Next X%
'Show the application
objSpreadSheet.Application.Visible = True
objSpreadSheet.Parent.Windows(1).Visible = True
End Sub
Notice that because this code does not create any objects (it merely retrieves an existing object using the GetObject function), you do not have to destroy any objects on completion of the program.
In the first part of this chapter, you created an Excel application totally independent of Visual Basic. In this section, you incorporate the functionality of Microsoft Word into Visual Basic. In the next section, you use OLE Automation to borrow
objects that Microsoft Word exposes, eliminating the need to create functionality in your application that is already available in Word.
The best method to use to create the code that objects from Microsoft Word can understand (WordBasic) is to record the commands you need in a Word macro. To record a macro in Word, select Tools | Macro from the main menu. The Macro dialog box displays.
Enter a name for the macro in the Macro Name text box and click Record. The Record Macro dialog box appears (see Figure 24.3). Click OK in this dialog box to begin recording the macro. A small window appears with a Stop and a Pause button (place the
mouse over one of the buttons and a ToolTip appears). Once you have finished recording the macro, click the Stop button.
Figure 24.3. The Microsoft Word Macro dialog box. Use the macro recorder to create code you can copy to Visual Basic.
Select Tools | Macro from the main menu again. Click the name of the macro you just created in the list box and click Edit. A new Word document appears, containing the WordBasic code for the macro. With small modifications, this code can be incorporated
into the Visual Basic application.
The following fragment of code was created by the macro recorder when I saved an open file with a new name:
FileSaveAs .Name = "NEW.DOC", .Format = 0, .LockAnnot = 0, .Password = "",_ .AddToMru = 1, .WritePassword = "", .RecommendReadOnly = 0, .EmbedFonts = 0, _ .NativePictureFormat = 0, .FormsData = 0
This fragment was created when I selected File | Page Setup and manipulated some of the page properties:
FilePageSetup .Tab = "3", .PaperSize = "1", .TopMargin = "1" + Chr$(34), _ .BottomMargin = "1" + Chr$(34), .LeftMargin = "1.25" + Chr$(34), .RightMargin = "1.25" + Chr$(34)
Selecting Help | Search for Help On creates this WordBasic code:
HelpSearch
With some manipulation, you can import this code into Visual Basic and manipulate objects Word exposes without creating code from the ground up. It does not take very long to learn the basic functions of Word. The following section creates an instance
of the Microsoft Word application and uses many of the objects, properties, and methods Word exposes.
In the form_load procedure, you must set a filename variable. This is the file to be created with this application:
Private Sub Form_Load()
Dim sFileName as String
sFileName = "C:\PROJECT\APP.DOC"
End Sub
First, create a variable to hold the text box string:
Dim sText As String
The next step is to create an instance of Microsoft Word. This object is created using the CreateObject() function:
Dim objWord As Object
Set objWord = CreateObject("word.basic")
Now set up an error handler. The reason for this error handler is that Microsoft Word sets the err variable to something other than 0 when it completes certain operations. For example, the spelling checker object returns error code 51 when the spell
check completes successfully. Without the error handler, Visual Basic pops up an error dialog box.
On Error GoTo ErrorHandler
Here is the error handler:
ErrorHandler:
Select Case Err
Case 51 'The spelling checker completed successfully.
Resume Next
Case 5003 'A disabled command was selected
MsgBox "That Command is not available at this time (disabled)"
Case Else
MsgBox Error$
End Select
The next step is to get the text the user entered in the text box and place it in a variable:
sText = Text1.TEXT
Now clear the Clipboard and place the contents of the sText variable into the Clipboard. This text will be added to the newly created document. The 1 in the Clipboard.SetText statement indicates that the format of what is being placed on the Clipboard
is text:
'Clear the clipboard Clipboard.Clear 'Copy the text in Text1 to the clipboard. Clipboard.SetText sText, 1
Next, check the Clipboard to ensure that the text is there. If there is no text, the user may not have entered anything in the text box. In this case, exit the subprocedure.
'There is text on the clipboard If Clipboard.GetFormat(vbCFText) Then
You access the Word object using the With... End With statement.
With objWord
First, create a new Word document:
.FileNew
The next step is to paste the contents of the Clipboard into the newly created document:
.EditPaste
Now that you have added text to the document, spell check it. When the spell check is complete, assuming all words are spelled correctly, the err variable is 51.
.ToolsSpelling
Now that the spelling check is complete, select the text and copy it to the Clipboard. The text will be retrieved later by the Visual Basic application that you are creating and placed back into the text box:
.EditSelectAll
.EditCopy
Now save the newly created document as a file and close the Word document:
.FileSaveAs sFileName
.FileClose 2
End With
The final step of this subprocedure is to destroy the Word object and set the text box back to blank:
Set objWord = Nothing
Text1.TEXT = ""
Then retrieve the spell-checked text and place it back into the text box:
Text1.TEXT = Clipboard.GetText(vbCFText)
In summary, you created an instance of a Microsoft Word object, added a new document, and pasted the contents of the Clipboard to the document. Then you spell-checked the text, saved the document (creating a new document file), and placed the
spell-checked text back on the Clipboard. Finally, you pasted the Clipboard contents back into our Visual Basic application's text box. Listing 24.2 shows the finished product.
Private Sub CreateWordDocument()
'sFilename is the file that we are going to create.
Dim sFileName As String
sFileName = "D:\test.doc"
'sText is what we will place in the file (the contents of the text box)
Dim sText As String
'Create an instance of Word
Dim objWord As Object
Set objWord = CreateObject("word.basic")
'Prevents errors when spelling checker completes OK.
On Error GoTo ErrorHandler
sText = Text1.TEXT
'Clear the clipboard
Clipboard.Clear
'Copy the text in Text1 to the clipboard
Clipboard.SetText sText, 1
'Check the clipboard to ensure that the text is there
If Clipboard.GetFormat(vbCFText) Then
'Using the word object
With objWord
'Select File | New from the menu to create a new document
.FileNew
'Paste the contents of the clipboard into the document
.EditPaste
'Spell check the document. If the spell check is successful, error 51 'occurs.
.ToolsSpelling
'Select the text
.EditSelectAll
'Copy it to the clipboard
.EditCopy
'Save the word document (this creates a file)
.FileSaveAs sFileName
'Close the word document
.FileClose 2
End With
Else
'No text was in the text box
MsgBox "There is nothing to save."
End If
'Destroy the Word object
Set objWord = Nothing
'Reset the text box to blank
Text1.TEXT = ""
Exit Sub
ErrorHandler:
Select Case Err
Case 51
Resume Next
Case 5003
MsgBox "That Command is not available at this time (disabled)"
Case Else
MsgBox Error$
End Select
End Sub
Private Sub GetTextFromClipboard()
Text1.TEXT = Clipboard.GetText(vbCFText)
End Sub
This chapter outlined some of the ways applications developed in Visual Basic 4.0 can interact with Microsoft Office. A fully functional Excel application was created using only code from Visual Basic. This application contained a spreadsheet with
buttons attached to macros. A chart was added to the application as was a module of functions that performed basic operations on the spreadsheet. Finally, a Microsoft Word document that displayed the contents of a document was embedded into the
application.
Another application was created using OLE Automation and Microsoft Word. This Visual Basic application used the objects Word exposes to create a Word application and document, send text to the document, spell check the document, and return it to Visual
Basic. The Microsoft Word document was then saved as a file.
This chapter provided the base on which you can develop creative, interactive applications that incorporate the OLE Automation objects Microsoft Office exposes.