Microsoft Office Tutorials and References
In Depth Information
E.2.1 An Alternative Approach
Dim XlApp As Excel.Application
which the Automation client will understand, because it can now check the server's object library.
Note that we need to qualify the object name, since other object models have an Application
object as well.
Next, we want to start the Excel Automation server, create an Excel Application object, and get a
reference to that object. This is done in the following line:
Set XLApp = New Excel.Application
At this point, we have complete access to Excel's object model. It is important to note, however,
that the previous line starts the Excel Automation server, but does not start Excel's graphical user
interface, so Excel will be running invisibly. To make Excel visible, we just set its Visible
property to True :
XLApp.Visible = True
We can now program as though we were within the Excel VBA IDE. For instance, the following
code creates a new workbook, adds a worksheet to it, puts a value in cell A1, and then saves the
workbook:
Sub MakeWorkbook()
Dim XlApp As Excel.Application
Dim wb As Excel.Workbook
Dim ws As Excel.Worksheet
Set XlApp = New Excel.Application
XlApp.Visible = True
Set wb = XlApp.Workbooks.Add
Set ws = wb.Worksheets.Add
ws.Name = "Sales"
ws.Range("A1").Value = 123
wb.SaveAs "d:\temp\SalesBook"
End Sub
Note that the Excel server will not terminate by itself, even if the XLApp variable is destroyed. If
we have made Excel visible, then we can close it programmatically, as well as from the user
interface in the usual way (choosing Exit from the File menu, for instance). But if the Excel server
is invisible, it must be closed using the Quit method:
XlApp.Quit
(If we fail to terminate the Excel server, it will remain running invisibly, taking up system
resources, until the PC is restarted.)
E.2.1 An Alternative Approach
The approach described for programming Excel from within another application is the preferred
approach, since it is the most efficient. However, there is an alternative approach that you may
encounter, so let us discuss it briefly. As before, we assume that a reference has been set to the
Excel object library.
E.2.1.1 The CreateObject function
Search JabSto ::




Custom Search