NeoPa 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 : - Ensure the Reference (VBA Window / Tools / References) has been ticked for the application you want to Automate.
- Set up an application object variable to use (You can use With...End With if you prefer).
Either :- 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).
- If supported for your desired application use GetObject(Document, Class) to open a specific document.
- Set the application's .Visible property. This determines if your changes are visible to the operator.
- 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).
- 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.
- When you're finished completely either :
- If you want to close down the application simply close it (Call accApp.Quit).
- If you want to leave it open for the operator to use just make sure you leave .Visible set to True.
- 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) - ' Requires A References to the Microsoft Outlook Object Library
-
' in VBE, select Tools->References, and ensure it is checked
-
-
Private Sub ExportToOutlook()
-
' Variable Declaration
-
Dim objOutlook as Object
-
Dim objEMail as Object
-
-
' If Outlook is open, use GetObject, otherwise open Outlook
-
Set objOutlook = GetObject(, "Outlook.Application")
-
If objOutlook Is Nothing Then Set objOutlook = CreateObject("Outlook.Application")
-
-
' Creates a new e-mail
-
Set objEMail = objOutlook.CreateItem(0)
-
With objEMail
-
-
' Adds To Recipient
-
Set ToContact = .Recipients.Add("Me@Gmail.Com")
-
-
' Adds CC recipient
-
ToContact.Type = olCC
-
Set ToContact = .Recipients.Add("You@Gmail.com")
-
-
' Sets the Subject
-
.Subject = "Service Report 1234"
-
-
' Sets the Body
-
.Body = "Attached herein are the Reports"
-
-
' Adds attachment
-
.Attachments.Add "c:\Service Report 1234", olByValue, , "Service Report"
-
-
' Embeds attachment
-
.Attachments.Add "c:\JoeBob.gif", olEmbeddedItem, , "Joe Bob's Picture"
-
-
' Receipt upon delivery
-
.OriginatorDeliveryReportRequested = True
-
-
' Recipt upon read
-
.ReadReceiptRequested = True
-
-
' Displays the E-Mail
-
.Display
-
-
' Sends the E-Mail
-
.Send
-
-
' Saves a Draft of the E-Mail
-
.Save
-
End With
-
-
End Sub
Basic Excel Automation - ' Requires a Reference to Microsoft Excel 8.0 Object Library or Higher
-
' In VBE, goto Tools->References... and select it from the list
-
Private Const conAppNotRunning As Long = 429
-
-
Private Sub ExportToExcel()
-
' Variable Declarations
-
Dim objExcel As Excel.Application
-
-
' If Excel is open, use GetObject, otherwise create a new Excel object
-
On Error Resume Next
-
Set objExcel = GetObject(, "Excel.Application")
-
If Err = conAppNotRunning Then Set objExcel = New Excel.Application
-
-
With objExcel
-
' Adds a new workbook to the Excel environment
-
.Workbooks.Add
-
-
' Excel VBA Code goes here
-
-
' Causes the Excel window to become visible
-
.Visible = True
-
End With
-
End Sub
9 39007 NeoPa 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.
NeoPa 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.
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.
"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) -
Dim xlapp As Object
-
Set xlapp = CreateObject("Excel.Application")
-
we can create whatever object application that vba support without reference (VBA Window / Tools / References), isn't it ?
zmbd 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: - '...
-
Dim zobjOutlook As Object 'Outlook.Application (Note dimensioned as Object)
-
Dim zobjEmail As Object 'Outlook.MailItem (Note dimensioned as Object)
-
Dim zobjNameSpace As Object 'Outlook.NameSpace (Note dimensioned as Object)
-
Const zolMailItem As Long = 0 'For Late Binding
-
Const zolFolderInbox As Long = 6 'For Late Binding
-
Const zolFormatHTML As Long = 2 'For Late Binding
-
Dim zstrSubject As String
-
Dim zstrAddress As String
-
-
On Error Resume Next
-
Set zobjOutlook = GetObject(, "Outlook.Application")
-
'...
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)
@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 -
Dim abc as object
-
set abc = createobject("Whatever.application")
-
'then slap on keyboard with a tons of code
-
:D
zmbd 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. ;-)
I still could not find the like button == want to give Neopa and you some Like :D
NeoPa 32,534
Expert Mod 16PB
Intended Like accepted with gratitude :-)
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...
| |