Integration Access with other component of the office
Programming in Access does not cost(stand) to forget that he is part of package Microsoft Office so one of the plus MS Access as facility of the development - интегрированность with such powerful exhibits as Word, Excel, PowerPoint, Outlook. Certainly, gain access to him possible not only with the help of Access, but as from versions MS Office 2000, all of these have one general programming language VBA. And in this serieses article shall consider the examples of the use to such integrations, or as her(its) else name automations.
The Automation (earlier known as OLE-automation OLE Automation) this one of the the most important facilities to technologies ActiveX, allowing software to control the object from the other exhibits. And this main means, by means of which possible integrate the functional possibilities of different exhibits.Exhibits, supporting automation, are divided on two categories: clients to automations and servers to automations. Moreover some exhibits can be only client or only server to automations, but there is and such (and to he pertain Microsoft Access), which can emerge and in that and in the other quality.
Clients and servers to automations
At integrations two exhibits one gives their own objects for use, but other uses the objects of the first exhibit.
- Exhibit, objects which available to other exhibits, is identified server to automations (sometimes his(its) else name component)
- Exhibit, which uses the objects of the other exhibit, is identified the client (or controller) to automations. Objects, which available to other exhibits, name object to automations
Through objects of the automations application server makes free other program to that its function, which can be him useful. For instance, text editor can open other program an access to check-spelling, service relationship access to creation and sending the messages. This allows the developer to accelerate the process of the development of their own applications, due to use ready function server.
For programme control object to automations from any application Microsoft Office necessary:
- Install the reference to library object exhibits-server to automations.
- Create the object of the necessary class.
- Realize all necessary actions on object, обращаясь to his(its) characteristic and method.
- Close the object.
But possible use the objects of the server to automations and not installing reference to concrete library object, however in this case program will work slowly. This is connected with that that after installing the reference to library, at announcement object variable, possible indicate her(its) concrete type but if reference is absent then for this variable to come to indicate the generalised type Object.
Microsoft Access as client to automations
To create the object for use in operation of the automations, it is necessary first to create the copy of his(its) class and assign the reference to it object variable. However create possible not any object from object model of exhibit-server, but only global objects. Such are a global object for all object models of exhibits family Microsoft Office is an object Application, which is found on top of the hierarchies object.
To hear, what characteristic of the models object are global, possible use the browser an object in editor VBA Object Browser (is started for instance so: open any module and press F2).
Afterwards choose in revealling list Project/Dbrary necessary library, but then choose the element <globals> in list Classes.
Exists several ways of the making the copy of the class of the object.
- By means of keyword New
This keyword describes the variable type Object and simultaneously installs the reference to new copy of the class of the object.
Dim app As New Excel.Application
When use the keyword New for making the new copy of the class Application is started corresponding to exhibit (for instance, Word or Excel). If this exhibit already uncared-for, that to not to start second copy, better use for making the new copy of the class function GetObject ()
To use the keyword New for making the copy of the class of the object to automations, necessary beforehand to add the reference of the library object exhibits of the server in dialogue window of the editor VBA References (the References).
The Example of the use:
Sub PowerPointOpenFile_Click()
On Error GoTo Err_
Dim strAppName As String
Dim app As New PowerPoint.Application
strAppName = CurrentProject.Path & "\Презентация1.ppt"
With app
.Visible = True
.Presentations.Open strAppName
End With
Set app = Nothing
Exit_:
Exit Sub
Err_:
MsgBox Err.Description
Resume Exit_
End Sub
By means of functions CreateObject()
The Function CreateObject () creates the reference to new copy of the class of the automatic object. Importance returned this function, must be assigned object variable by means of operator Set. For instance, named by operator is opened exhibit Word, and reference to he is assigned object variable docobj. Herewith even though Word was already open, will be uncared-for one more his(its) copy:
Set app = CreateObject ("Excel.Application")
Object variable in this instance can be declared as Object, and then reference to library object Microsoft Word to install no need. But can be declared so:
Dim app As Excel.Application
In this case must be is without fall installed reference to library object Microsoft Word **.0 Object Library (** shows that for miscellaneous version office importance miscellaneous: 10.0, 11.0, 12.0 for accordingly offices HR, 2003, 2007). If on computer is installed several versions Microsoft Office, that possible indicate the number to versions of exhibit, which will be used at automations, for instance:
Set app = CreateObject("Excel.Application.11")
The Example of the use to functions:
Sub ExcelOpenFile()
On Error GoTo Err_
Dim strAppName As String
Dim app As Excel.Application
strAppName = CurrentProject.Path & "\Книга1.xls"
Set app = CreateObject("Excel.Application")
With app
.Visible = True
.Workbooks.Open strAppName
End With
Set app = Nothing
Exit_:
Exit Sub
Err_:
MsgBox Err.Description
Resume Exit_
End Sub
- By means of functions GetObject()
This way is used to get the reference to already existing copy of the class, t. e. for access to existing document, keeping in file, or for access to object Application already uncared-for application-server. Function Getobject () has following syntax:
Set <object Variable> = Getobject([<path>][,<class>])
Without fall must be specified at least one of the argument to functions!
We shall Consider three variants of the writing to functions:
- Set app = GetObject(, "Excel.Application")
is it here lowered first parameter then function works like two aforesaid simply for making the copy of the class of the object.
- Set app = GetObject("C:\Baze\ Книга1.xls", "Excel.Application")
is it here specified both parameters way to file and name of application. In this case file Kniga.xls is started
- Set app = GetObject("C:\Baze\ Книга1.xls")
here second argument can be omitted if in roll Windows exists the ligament of the extension of the file with object of application. For instance, an object type Excel have in the event of Microsoft Excel files xls by default.Workbook. This means that unless indicate the argument <class>, that will is created reference to object Workbook, presented specified in the first argument by file.
The Example of the use to functions:
Sub WordOpenFile()
On Error GoTo Err_
Dim strAppName As String
Dim app As Word.Application
strAppName = CurrentProject.Path & "\Doc1.doc"
Set app = GetObject("", "Word.Application")
With app
.Visible = True
.Documents.Open strAppName
End With
Set app = Nothing
Exit_:
Exit Sub
Err_:
MsgBox Err.Description
Resume Exit_
End SubTo easier was create the code for work with object Word or Excel, recall about such remarkable facility of these applications, as record макросов. By means of record макросов possible to execute all necessary actions, using interface of the user, then view the generated code VBA and встроить this code in its procedure VBA, only mildly having ed him(it).
For instance: we open the file excel, choose the Service Makros Begin record. In appeared dialogue window assign the name макросу and жмем OK . Hereinafter we do their own deal format the cells, change given all that is pleased. The Sequence action is written in special module in the manner of commands VBA. ZHmem to stop record. Then Service Makros Editor Visual Basic and in group Modules find the new module. We Open and look, as all that you did looks on VBA.
After all necessary operations with object of application-server are executed, these objects follows to close to free occupied by them memory. The Majority object support for this methods close or Quit. Besides, follows to free object variable, having assigned by her importance Nothing.
In applicable to article example are shown variants of the opening document Word, Excel, Power Point. For a change for each document is used one of three ways of the opening. But, of course, any one of the document possible to open any one of ways.
PS: Example is made in Access 2002. At opening him(it) in Access 2000 it is necessary will be through Tools - References переопределиить references to Word, Excel and Power Point since 10.0 on 9.0
