By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,985 Members | 1,777 Online
Bytes IT Community
Submit an Article
Got Smarts?
Share your bits of IT knowledge by writing an article on Bytes.

Application Automation

NeoPa
Expert Mod 15k+
P: 31,347
In VBA (I expect VB would be similar) controlling another Office Application (Automation) can be done using the following steps :
  1. Ensure the Reference (VBA Window / Tools / References) has been ticked for the application you want to Automate.
  2. Set up an application object variable to use (You can use With...End With if you prefer).
    Either :
    1. Use CreateObject(Class) to open a new object for multi-instance programs (For single Instance programs it will return the current instance if already open).
    2. If supported for your desired application use GetObject(Document, Class) to open a specific document.
  3. Set the application's .Visible property. This determines if your changes are visible to the operator.
  4. While the application's object is active, you can run code as if within the other application simply by referencing the application object (EG. accApp.Visible = True...Call accApp.CurrentDB.Close).
  5. If you're finished with one document but you still want to keep the application ready for your use then set the .Visible property to False and re-activate later.
  6. When you're finished completely either :
    1. If you want to close down the application simply close it (Call accApp.Quit).
    2. If you want to leave it open for the operator to use just make sure you leave .Visible set to True.
  7. Release any associated objects (Set to Nothing or End With).
Here are some useful Methods / Properties that can be used from within your code :
  • Quit - Each Application object has a Quit method.
  • Activate - This sets the focus to the Application.
  • Run - Enables you to run code from the Application (or Document).
  • Copy / Paste - This should work happily between different Applications
  • WindowState - Can be set to Maximized, Minimized or Normal.

NB.
When working with any Office Application's own VBA IDE (Independent Development Environment) there are generally a whole bunch of defaults you get used to. For instance when referring in Excel to a Range("A1") object it will understand such a reference to mean to the ActiveSheet object of Excel's Application. In any foreign environment these defaults are not set so, assuming you have an Excel.Application object called appXL, you would refer to it as appXL.ActiveSheet.Range("A1") instead. This is just one example but the point holds true for all Office Applications.

Most people, when starting to use Application Automation for the first time, find they fall over this problem quite a lot. Look out for it and do what you can to avoid it as much as possible.

Posted by fauxanadu

Basic Outlook Automation (including Send New e-Mail)
Expand|Select|Wrap|Line Numbers
  1. ' Requires A References to the Microsoft Outlook Object Library
  2. ' in VBE, select Tools->References, and ensure it is checked
  3.  
  4. Private Sub ExportToOutlook()
  5.     ' Variable Declaration
  6.     Dim objOutlook as Object
  7.     Dim objEMail as Object
  8.  
  9.     ' If Outlook is open, use GetObject, otherwise open Outlook
  10.     Set objOutlook = GetObject(, "Outlook.Application")
  11.     If objOutlook Is Nothing Then Set objOutlook = CreateObject("Outlook.Application")
  12.  
  13.     ' Creates a new e-mail
  14.     Set objEMail = objOutlook.CreateItem(0)
  15.     With objEMail
  16.  
  17.         ' Adds To Recipient
  18.         Set ToContact = .Recipients.Add("Me@Gmail.Com")
  19.  
  20.         ' Adds CC recipient
  21.         ToContact.Type = olCC
  22.         Set ToContact = .Recipients.Add("You@Gmail.com")
  23.  
  24.         ' Sets the Subject
  25.         .Subject = "Service Report 1234"
  26.  
  27.         ' Sets the Body
  28.         .Body = "Attached herein are the Reports"
  29.  
  30.         ' Adds attachment
  31.         .Attachments.Add "c:\Service Report 1234", olByValue, , "Service Report"
  32.  
  33.         ' Embeds attachment
  34.         .Attachments.Add "c:\JoeBob.gif", olEmbeddedItem, , "Joe Bob's Picture"
  35.  
  36.         ' Receipt upon delivery
  37.         .OriginatorDeliveryReportRequested = True
  38.  
  39.         ' Recipt upon read
  40.         .ReadReceiptRequested = True
  41.  
  42.         ' Displays the E-Mail
  43.         .Display
  44.  
  45.         ' Sends the E-Mail
  46.         .Send
  47.  
  48.         ' Saves a Draft of the E-Mail
  49.         .Save                                       
  50.     End With
  51.  
  52. End Sub
Basic Excel Automation
Expand|Select|Wrap|Line Numbers
  1. ' Requires a Reference to Microsoft Excel 8.0 Object Library or Higher
  2. ' In VBE, goto Tools->References... and select it from the list
  3. Private Const conAppNotRunning As Long = 429
  4.  
  5. Private Sub ExportToExcel()
  6.     ' Variable Declarations
  7.     Dim objExcel As Excel.Application
  8.  
  9.     ' If Excel is open, use GetObject, otherwise create a new Excel object
  10.     On Error Resume Next
  11.     Set objExcel = GetObject(, "Excel.Application")
  12.     If Err = conAppNotRunning Then Set objExcel = New Excel.Application
  13.  
  14.     With objExcel
  15.         ' Adds a new workbook to the Excel environment
  16.         .Workbooks.Add
  17.  
  18.         ' Excel VBA Code goes here
  19.  
  20.         ' Causes the Excel window to become visible
  21.         .Visible = True
  22.     End With
  23. End Sub
Feb 10 '07 #1
Share this Article
Share on Google+
9 Comments


NeoPa
Expert Mod 15k+
P: 31,347
Be careful when developing code for controlling other applications. The temptation is always to develop the code in the actual application natively, then to port what has been developed across and paste into the controlling application within a standard shell of code to link across to it (EG. Inside a shell of code opening and closing an Excel workbook from Access). This is actually a pretty sound way to develop your code, but there is one pretty fundamental problem with this :

Many Office applications have their own default shortcuts that are active when, but only when, the code is run from the native application itself. For instance, some code that would work quite well from within Excel may include references to the Range() property. By default, this can be used alone to default to ActiveSheet.Range(). Outside of Excel though, this default does not work (is not available). A reference to Range() (on its own) within Access code controlling a workbook will fail due to the absence of a reference object.
Jun 10 '10 #2

NeoPa
Expert Mod 15k+
P: 31,347
I just came across a problem using Access Automation while working on a process to manage a number of MDB files in Access 2003.

It seems that using .OpenCurrentDatabase in a controlled version of Access will sometimes fail with a spurious error. I wanted to log the error details but it seems it's a little unpredictable. I ran it without the setting and this time it worked fine.

To get around this you can set .UserControl = True before the call and it will work fine (Application.UserControl Property).

PS. I subsequently noted the error which occurred again :
7866 - Microsoft Office Access can't open the database because it is missing, or opened exclusively by another user.
May 3 '13 #3

100+
P: 104
Many Office applications have their own default shortcuts that are active when, but only when, the code is run from the native application itself. (...)
This drove me crazy for the longest time. I never did figure out why Range() wasn't working.

Thanks for the article, this is really useful.
Mar 11 '14 #4

100+
P: 215
"Ensure the Reference (VBA Window / Tools / References) has been ticked for the application you want to Automate"

instead of reference to exactly application
can we use this method ? (for example: excel automaton)
Expand|Select|Wrap|Line Numbers
  1. Dim xlapp As Object
  2. Set xlapp = CreateObject("Excel.Application")
  3.  
we can create whatever object application that vba support without reference (VBA Window / Tools / References), isn't it ?
Nov 30 '15 #5

zmbd
Expert Mod 5K+
P: 5,387
Yes you can do something like that - late binding:\

Here's part of the code I use to late bind to Outlook:
Expand|Select|Wrap|Line Numbers
  1. '...
  2.     Dim zobjOutlook As Object    'Outlook.Application  (Note dimensioned as Object)
  3.     Dim zobjEmail As Object      'Outlook.MailItem     (Note dimensioned as Object)
  4.     Dim zobjNameSpace As Object  'Outlook.NameSpace    (Note dimensioned as Object)
  5.     Const zolMailItem As Long = 0    'For Late Binding
  6.     Const zolFolderInbox As Long = 6 'For Late Binding
  7.     Const zolFormatHTML As Long = 2  'For Late Binding
  8.     Dim zstrSubject As String
  9.     Dim zstrAddress As String
  10.  
  11.     On Error Resume Next
  12.     Set zobjOutlook = GetObject(, "Outlook.Application")
  13. '...
This is, however, a more advanced level of programing and IMHO Neopa was targeting a fairly large audience wherein many would not be comfortable using late-binding. :)

(and in fact, I tend to use the early binding method for development and then switch to late binding (as in the above) once I have things working - I love the intellisense :-D)
Nov 30 '15 #6

100+
P: 215
@zmbd: yeah, now I recall that we call those method early binding and late binding, thank you to remind me.
I don't have anything to say other than I'm lazy so I won't go to reference and tick on the right box when I can
Expand|Select|Wrap|Line Numbers
  1. Dim abc as object
  2. set abc = createobject("Whatever.application")
  3. 'then slap on keyboard with a tons of code
  4.  
:D
Nov 30 '15 #7

zmbd
Expert Mod 5K+
P: 5,387
lazy... lazier would be to use the intellisense to help write your code, this also helps to avoid syntax issues (along with the Option Explicit setting); thus, producing viable code is more efficient as there is less troubleshooting required and what is required is more quickly pinpointed by the debug/compile option. It's a simple matter to then add the createobject line, set explicates to object, and finally pull the constants out... the pros and cons would however, be a topic for another thread. ;-)
Nov 30 '15 #8

100+
P: 215
I still could not find the like button == want to give Neopa and you some Like :D
Dec 2 '15 #9

NeoPa
Expert Mod 15k+
P: 31,347
Intended Like accepted with gratitude :-)
Dec 3 '15 #10