472,783 Members | 974 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes and contribute your articles to a community of 472,783 developers and data experts.

Application Automation

32,534 Expert Mod 16PB
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.

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
  4. Private Sub ExportToOutlook()
  5.     ' Variable Declaration
  6.     Dim objOutlook as Object
  7.     Dim objEMail as Object
  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")
  13.     ' Creates a new e-mail
  14.     Set objEMail = objOutlook.CreateItem(0)
  15.     With objEMail
  17.         ' Adds To Recipient
  18.         Set ToContact = .Recipients.Add("Me@Gmail.Com")
  20.         ' Adds CC recipient
  21.         ToContact.Type = olCC
  22.         Set ToContact = .Recipients.Add("You@Gmail.com")
  24.         ' Sets the Subject
  25.         .Subject = "Service Report 1234"
  27.         ' Sets the Body
  28.         .Body = "Attached herein are the Reports"
  30.         ' Adds attachment
  31.         .Attachments.Add "c:\Service Report 1234", olByValue, , "Service Report"
  33.         ' Embeds attachment
  34.         .Attachments.Add "c:\JoeBob.gif", olEmbeddedItem, , "Joe Bob's Picture"
  36.         ' Receipt upon delivery
  37.         .OriginatorDeliveryReportRequested = True
  39.         ' Recipt upon read
  40.         .ReadReceiptRequested = True
  42.         ' Displays the E-Mail
  43.         .Display
  45.         ' Sends the E-Mail
  46.         .Send
  48.         ' Saves a Draft of the E-Mail
  49.         .Save                                       
  50.     End With
  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
  5. Private Sub ExportToExcel()
  6.     ' Variable Declarations
  7.     Dim objExcel As Excel.Application
  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
  14.     With objExcel
  15.         ' Adds a new workbook to the Excel environment
  16.         .Workbooks.Add
  18.         ' Excel VBA Code goes here
  20.         ' Causes the Excel window to become visible
  21.         .Visible = True
  22.     End With
  23. End Sub
Feb 10 '07 #1
9 39007
32,534 Expert Mod 16PB
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
32,534 Expert Mod 16PB
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
106 64KB
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
215 128KB
"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")
we can create whatever object application that vba support without reference (VBA Window / Tools / References), isn't it ?
Nov 30 '15 #5
5,501 Expert Mod 4TB
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
  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
215 128KB
@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
Nov 30 '15 #7
5,501 Expert Mod 4TB
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
215 128KB
I still could not find the like button == want to give Neopa and you some Like :D
Dec 2 '15 #9
32,534 Expert Mod 16PB
Intended Like accepted with gratitude :-)
Dec 3 '15 #10

Sign in to post your reply or Sign up for a free account.

Similar topics

by: Shuttermutt | last post by:
Good day, all! I'm wondering if it's possible to write two applications so that they can each compile to their own EXE, but one application can manipulate the objects on the other application. For...
by: Leo | last post by:
hi there i have just started with mac development and i use applescript at the moment for iTunes and iPhoto automation. but i'd like to do it from python. does anybody know weather that's...
by: qwweeeit | last post by:
Hi all, Elliot Temple on the 1 June wrote: > How do I make Python press a button on a webpage? I looked at > urllib, but I only see how to open a URL with that. I searched > google but no...
by: Neil Ginsberg | last post by:
I have a strange situation with my Access 2000 database. I have code in the database which has worked fine for years, and now all of a sudden doesn't work fine on one or two of my client's...
by: Jimmer | last post by:
I've got what should be an easy automation problem, but the solution simply isn't coming to me. I've got several public variables set up for automation as follows: Public gappExcel As...
by: Lee Seung Hoo | last post by:
hi~ :) I need all information of "Automation" or "Automation Object" what is that ? why is it useful ? How can I use that by C# or .Net Framework ?
by: Daniel | last post by:
Hello, i have a problem with the word automation from c#. First, i want to mention, that i don't have any dependencies from word in my c#-project, i want to use the system.reflection model to...
by: a.theil | last post by:
Please help! I need a simple excel automation, just 2 write some files into excel. I do: Dim oXL As Excel.Application Dim oWB As Excel.Workbook Dim oSheet As Excel.Worksheet Dim oRng As...
by: NeoPa | last post by:
Actually, the question's a little more involved than that. Let me try to explain as succinctly as I can. I want to know how to execute code that is held within the project I'm trying to open...
by: Rina0 | last post by:
Cybersecurity engineering is a specialized field that focuses on the design, development, and implementation of systems, processes, and technologies that protect against cyber threats and...
by: linyimin | last post by:
Spring Startup Analyzer generates an interactive Spring application startup report that lets you understand what contributes to the application startup time and helps to optimize it. Support for...
by: erikbower65 | last post by:
Here's a concise step-by-step guide for manually installing IntelliJ IDEA: 1. Download: Visit the official JetBrains website and download the IntelliJ IDEA Community or Ultimate edition based on...
by: kcodez | last post by:
As a H5 game development enthusiast, I recently wrote a very interesting little game - Toy Claw ((http://claw.kjeek.com/))。Here I will summarize and share the development experience here, and hope it...
by: Taofi | last post by:
I try to insert a new record but the error message says the number of query names and destination fields are not the same This are my field names ID, Budgeted, Actual, Status and Differences ...
by: Rina0 | last post by:
I am looking for a Python code to find the longest common subsequence of two strings. I found this blog post that describes the length of longest common subsequence problem and provides a solution in...
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
by: Mushico | last post by:
How to calculate date of retirement from date of birth
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.