Automating Office applications

Comatose 2 Tallied Votes 1K Views Share

One of the most important things to understand when automating office, is that it's heavily [search]OOP[/search], and therefore you are forced to work with objects. In order to create an object that is external to your application (for example an office application, whereas a button or textbox would be internal to your application), you have to tell VB that you want to make an object. This is where the createobject function comes in, allowing your program to create an "instance" of a class so that you can manipulate its object.

CreateObject can take two arguments, the second of which (ServerName) has to do with the computer that the object was created on in a network, and will cause more trouble than it's worth. The first parameter (Class), however, allows you to make an object of a given type. So, if you want to make an office application you could set oApp = createobject("Office.Application") . Naturally you can't leave the "office.application" just like it is. It has to know which application you want to mess with (be it Word, Excel, etc), so in order to create a Word object, you can do some fancy stuff like set oApp = createobject("Word.Application") , and your application will create a new Word application. You can refer to your copy (Instance) of Word with the Object Variable oApp.

What is that you say? You don't see a copy of Word anywhere? That's because you haven't done anything with the properties just yet. The only thing you have done is just as the function's name implies, and that's to have created an object. Also keep in mind that the Word object has sub-objects, such as the document object. This makes sense in the whole picture of things, since a Word application can have a document, but also keep in mind that the Word document object will have different properties and methods than the Word's application object.

Indeed, anyone who has played with VB for a day will know that all objects have properties. These are the defining characteristics of your object. You know that you can make a button, by clicking the button tool from the toolbox, and dragging it onto the form right? Now, what about changing the caption text on the face of the button? That's a property. A simple command like command1.caption = "hello world" will change the text from command1 to hello world, because you changed the .Caption property. It's no different with an Office object either. Just because you can't see the Office object, and you can see the command button, doesn't mean it's not there. You know that the command button has a .Visible property, which lets you hide or show your command button, so why can't you do that with Word?

dim oApp 
set oApp = createobject("Word.Application")
oApp.Visible = true

It really is that easy! You have created an instance of Word and set its .Visible property to true. Now you can see the Word object that you made, but you shouldn’t show the Word object to people who use your application unless that is what it's supposed to do of course.

Methods are a lot like properties, differing in that methods perform an action. Sometimes methods alter properties, and sometimes they don't. Load up VB and start a standard EXE (if you are using VBA just load up the VB Editor and get a new Form). Now that you have a form you can drop a button on it, and a textbox on it. In the form load call the SetFocus method of the textbox (in VBA Text1 will probably be replaced with TextBox1) so the code would look something like text1.setfocus . That's pretty easy, and when the form loads up the cursor will be blinking in the textbox ready for typing. The user does not have to click the textbox with the mouse, or move hands away from the keyboard. All you had to do was call a simple method in order to greatly improve the user experience of the program.

Which is something you won’t do unless you clean up after yourself, because the program will leave copies of objects hanging around in memory eating up space. This can lead to serious performance hits on a system, and make life miserable for the people you built the program for. Cleaning up is easy to do and doesn't require a lot of thought: simply set all the objects you created to nothing. So, you made a Word object that you refer to with oApp for example. A simple clean up procedure here (on a quit button, or on the forms unload event) is two lines of code oApp.Quit and set oApp = nothing .

Be warned, if you DO NOT do this then Word (or any other Office app you created) WILL keep running whether it is visible or not. To prove it just load task manager and look at the processes tab. When you create the object, you'll see word.exe loaded up in there, if you don't set it back to nothing in your code, it will stay there eating up system resources.

It is a good idea to familiarize yourself with objects, their methods and properties, and so here are some links to the MSDN library that will help you do just that, some even contain sample code:

Word:

Excel:

Now that you have got to grips with objects, methods and properties you can start to move on to automation in earnest. The first thing that you need to know and understand is how to generate a new Word document. This enables you to save any kind of data you have as a Word document, or even create your own interface word processor that runs it's back end as Word. In order to make a new document, you will need to create an instance of Word, then create an object to the new document by calling the add method of the documents collection. Some simple code will help with the explanation:

dim oWord 
dim WordDoc 
Set oWord = CreateObject("Word.Application")
oWord.Visible = true
Set oDoc = oWord.Documents.Add

Don’t forget about clean up (set oWord and oDoc to nothing) or you really will hate yourself later. As of right now, this little piece of code will load Word, and generate a new document. If you comment out the last line (or remove it) it will still load Word, but you'll just have the Word window with no document. You can add some text with the range sub-object of the document object, for example:

dim oWord
dim WordDoc
dim rng
Set oWord = CreateObject("Word.Application")
oWord.Visible = true
Set oDoc = oWord.Documents.Add
set rng = oDoc.Range(Start:=0, End:=0)
rng.text = "Hello World"

OK, so you have created a Word object, then a new document, and then set some text starting from the beginning. But creating documents isn't much fun if you can't save them as well. You can save the active document by simply calling the save method of the document object: something like oDoc.Save . The problem with this is that you haven't specified a name previously, you just made a new document, added some text, and want to save it. You can use the "SaveAs" method of the document object, and save yourselves a lot of hassle. You can use SaveAs for all operations, but for simplicity just calling the Save method would work for already opened files. If you call the Save method on an untitled document it will launch the dialog box for you to type a name to save it as. Here is the code example so far, using saveas:

Dim oWord
Dim WordDoc
Dim rng
Set oWord = CreateObject("Word.Application")
oWord.Visible = True
Set WordDoc = oWord.documents.Add
Set rng = WordDoc.Range(Start:=0, End:=0)
rng.Text = "Hello World"
WordDoc.Saveas ("c:\try.doc")
oWord.Quit
Set oWord = Nothing
Set WordDoc = Nothing
Set rng = Nothing

The truth is that opening existing files is really easy, and doesn't require a whole lot of explanation: you just call the open method of the object. Something to point out, however, is that some people get mistaken about not having to call the add method of the documents collection to open an existing document. The add method is the same as clicking the "new" button in Word itself. If you try to open a file that does not exist, it WILL flip out on you. So, a little error checking to see if the file in question exists first is a really good idea, but here is some code that will open "c:\try.doc" in Word (if try.doc exists):

Dim oWord
Dim WordDoc
Set oWord = CreateObject("Word.Application")
Set WordDoc = oWord.documents.open("c:\try.doc")
oWord.Visible = True

If this code is successful, then WordDoc will be a reference to the opened document "try.doc", thus allowing you to make changes and modifications at will, such as with the range object for example. Ranges allow you to decide where to start inserting or replacing text. Something that can come in very handy, especially if you want to append information to the very bottom of the word document, is to know how many characters the entire document has, so you know where to start inserting from. Forms and other types of documents used in business atmospheres need things to be pretty precise, so inserting a customer name BEFORE the word "Dear" (Mr. Henkle Dear) might cause serious problems for you and your business.

Ok, counting letters is as easy as the documents character count cnt = WordDoc.Characters.Count . Let's see if you can insert some text to the end of a file but make sure c:\try.doc exists before running this:

Dim oWord
Dim WordDoc
Dim rng

Set oWord = CreateObject("Word.Application")
Set WordDoc = oWord.documents.open("c:\try.doc")
cnt = WordDoc.Characters.Count
cnt = cnt - 1
Set rng = WordDoc.Range(Start:=cnt, End:=cnt)

rng.Text = vbNewLine & "Added Text!"
WordDoc.SaveAs "c:\try.doc"

oWord.Quit
Set rng = Nothing
Set oWord = Nothing
Set WordDoc = Nothing

A final note, I only had 4 letters in my word document (test) and my cnt value was 5. The reason that this was happening, is because the Document's Character Count Method counts the End Of File (EOF) character as a string part of the document!!!! So, simply remove 1 from the total so you don't get some crazy errors on the range object. All you do in the above example is get the total number of characters in the document (subtract one for EOF) and add a new line (vbnewline) and the line "Added Text!" to the file. Then it saves the file with out new text in it. How cool is that?

pranavdv -2 Light Poster

This is really good one who wants to work with office applcation

One of the most important things to understand when automating office, is that it's heavily [search]OOP[/search], and therefore you are forced to work with objects. In order to create an object that is external to your application (for example an office application, whereas a button or textbox would be internal to your application), you have to tell VB that you want to make an object. This is where the createobject function comes in, allowing your program to create an "instance" of a class so that you can manipulate its object.

CreateObject can take two arguments, the second of which (ServerName) has to do with the computer that the object was created on in a network, and will cause more trouble than it's worth. The first parameter (Class), however, allows you to make an object of a given type. So, if you want to make an office application you could set oApp = createobject("Office.Application") . Naturally you can't leave the "office.application" just like it is. It has to know which application you want to mess with (be it Word, Excel, etc), so in order to create a Word object, you can do some fancy stuff like set oApp = createobject("Word.Application") , and your application will create a new Word application. You can refer to your copy (Instance) of Word with the Object Variable oApp.

What is that you say? You don't see a copy of Word anywhere? That's because you haven't done anything with the properties just yet. The only thing you have done is just as the function's name implies, and that's to have created an object. Also keep in mind that the Word object has sub-objects, such as the document object. This makes sense in the whole picture of things, since a Word application can have a document, but also keep in mind that the Word document object will have different properties and methods than the Word's application object.

Indeed, anyone who has played with VB for a day will know that all objects have properties. These are the defining characteristics of your object. You know that you can make a button, by clicking the button tool from the toolbox, and dragging it onto the form right? Now, what about changing the caption text on the face of the button? That's a property. A simple command like command1.caption = "hello world" will change the text from command1 to hello world, because you changed the .Caption property. It's no different with an Office object either. Just because you can't see the Office object, and you can see the command button, doesn't mean it's not there. You know that the command button has a .Visible property, which lets you hide or show your command button, so why can't you do that with Word?

dim oApp 
set oApp = createobject("Word.Application")
oApp.Visible = true

It really is that easy! You have created an instance of Word and set its .Visible property to true. Now you can see the Word object that you made, but you shouldn’t show the Word object to people who use your application unless that is what it's supposed to do of course.

Methods are a lot like properties, differing in that methods perform an action. Sometimes methods alter properties, and sometimes they don't. Load up VB and start a standard EXE (if you are using VBA just load up the VB Editor and get a new Form). Now that you have a form you can drop a button on it, and a textbox on it. In the form load call the SetFocus method of the textbox (in VBA Text1 will probably be replaced with TextBox1) so the code would look something like text1.setfocus . That's pretty easy, and when the form loads up the cursor will be blinking in the textbox ready for typing. The user does not have to click the textbox with the mouse, or move hands away from the keyboard. All you had to do was call a simple method in order to greatly improve the user experience of the program.

Which is something you won’t do unless you clean up after yourself, because the program will leave copies of objects hanging around in memory eating up space. This can lead to serious performance hits on a system, and make life miserable for the people you built the program for. Cleaning up is easy to do and doesn't require a lot of thought: simply set all the objects you created to nothing. So, you made a Word object that you refer to with oApp for example. A simple clean up procedure here (on a quit button, or on the forms unload event) is two lines of code oApp.Quit and set oApp = nothing .

Be warned, if you DO NOT do this then Word (or any other Office app you created) WILL keep running whether it is visible or not. To prove it just load task manager and look at the processes tab. When you create the object, you'll see word.exe loaded up in there, if you don't set it back to nothing in your code, it will stay there eating up system resources.

It is a good idea to familiarize yourself with objects, their methods and properties, and so here are some links to the MSDN library that will help you do just that, some even contain sample code:

Word:

Excel:

Now that you have got to grips with objects, methods and properties you can start to move on to automation in earnest. The first thing that you need to know and understand is how to generate a new Word document. This enables you to save any kind of data you have as a Word document, or even create your own interface word processor that runs it's back end as Word. In order to make a new document, you will need to create an instance of Word, then create an object to the new document by calling the add method of the documents collection. Some simple code will help with the explanation:

dim oWord 
dim WordDoc 
Set oWord = CreateObject("Word.Application")
oWord.Visible = true
Set oDoc = oWord.Documents.Add

Don’t forget about clean up (set oWord and oDoc to nothing) or you really will hate yourself later. As of right now, this little piece of code will load Word, and generate a new document. If you comment out the last line (or remove it) it will still load Word, but you'll just have the Word window with no document. You can add some text with the range sub-object of the document object, for example:

dim oWord
dim WordDoc
dim rng
Set oWord = CreateObject("Word.Application")
oWord.Visible = true
Set oDoc = oWord.Documents.Add
set rng = oDoc.Range(Start:=0, End:=0)
rng.text = "Hello World"

OK, so you have created a Word object, then a new document, and then set some text starting from the beginning. But creating documents isn't much fun if you can't save them as well. You can save the active document by simply calling the save method of the document object: something like oDoc.Save . The problem with this is that you haven't specified a name previously, you just made a new document, added some text, and want to save it. You can use the "SaveAs" method of the document object, and save yourselves a lot of hassle. You can use SaveAs for all operations, but for simplicity just calling the Save method would work for already opened files. If you call the Save method on an untitled document it will launch the dialog box for you to type a name to save it as. Here is the code example so far, using saveas:

Dim oWord
Dim WordDoc
Dim rng
Set oWord = CreateObject("Word.Application")
oWord.Visible = True
Set WordDoc = oWord.documents.Add
Set rng = WordDoc.Range(Start:=0, End:=0)
rng.Text = "Hello World"
WordDoc.Saveas ("c:\try.doc")
oWord.Quit
Set oWord = Nothing
Set WordDoc = Nothing
Set rng = Nothing

The truth is that opening existing files is really easy, and doesn't require a whole lot of explanation: you just call the open method of the object. Something to point out, however, is that some people get mistaken about not having to call the add method of the documents collection to open an existing document. The add method is the same as clicking the "new" button in Word itself. If you try to open a file that does not exist, it WILL flip out on you. So, a little error checking to see if the file in question exists first is a really good idea, but here is some code that will open "c:\try.doc" in Word (if try.doc exists):

Dim oWord
Dim WordDoc
Set oWord = CreateObject("Word.Application")
Set WordDoc = oWord.documents.open("c:\try.doc")
oWord.Visible = True

If this code is successful, then WordDoc will be a reference to the opened document "try.doc", thus allowing you to make changes and modifications at will, such as with the range object for example. Ranges allow you to decide where to start inserting or replacing text. Something that can come in very handy, especially if you want to append information to the very bottom of the word document, is to know how many characters the entire document has, so you know where to start inserting from. Forms and other types of documents used in business atmospheres need things to be pretty precise, so inserting a customer name BEFORE the word "Dear" (Mr. Henkle Dear) might cause serious problems for you and your business.

Ok, counting letters is as easy as the documents character count cnt = WordDoc.Characters.Count . Let's see if you can insert some text to the end of a file but make sure c:\try.doc exists before running this:

Dim oWord
Dim WordDoc
Dim rng
 
Set oWord = CreateObject("Word.Application")
Set WordDoc = oWord.documents.open("c:\try.doc")
cnt = WordDoc.Characters.Count
cnt = cnt - 1
Set rng = WordDoc.Range(Start:=cnt, End:=cnt)
 
rng.Text = vbNewLine & "Added Text!"
WordDoc.SaveAs "c:\try.doc"
 
oWord.Quit
Set rng = Nothing
Set oWord = Nothing
Set WordDoc = Nothing

A final note, I only had 4 letters in my word document (test) and my cnt value was 5. The reason that this was happening, is because the Document's Character Count Method counts the End Of File (EOF) character as a string part of the document!!!! So, simply remove 1 from the total so you don't get some crazy errors on the range object. All you do in the above example is get the total number of characters in the document (subtract one for EOF) and add a new line (vbnewline) and the line "Added Text!" to the file. Then it saves the file with out new text in it. How cool is that?

elaek 0 Newbie Poster

I think it's a very good example to show how we can work with others applications.

elaek 0 Newbie Poster

for me, i need how to add or delete a line from a report (with VB code).
The report is created, and i need to add some lines, sometimes, i need to delete sometimes. It depend on the user choice.
Can any one help me.
Thanks.

Tyrone.Wilson 0 Light Poster

Awesome tut. I had no idea how to do all of this stuff. I found a bit of a gem with this one. One thing I did notice though was that all of the microsoft links didn't work (content not found)

Thanks for the lesson.

sunilkhilari 0 Newbie Poster

GOOD

pranavdv -2 Light Poster

This is really a good one. This really helps how to manager other office applications with the objects.

Member 784676 0 Newbie Poster

i am using vfp9 application with xp and office 2010 in that i am unable to open dotx file through vfp9 while i open the dotx file it open in readonly mode please help me please go through my code for the reference


ext = 'dotx '
lpass = 'm003'
odoc = 'e:\esec release 30-06-2010\data\templates\Temp No.3 - ASDASDSAD'+"."+Alltrim(ext)
loword=Createobject('Word.Application')
loword.Visible = .T.
loword.WindowState= 1
loword.documents.Open( '&odoc' ,,.T.,.F.,'&lpass',,,'&lpass') &&&current


thanks
dhanesh r mehta

Smithers 0 Newbie Poster
DJ-DOO 0 Newbie Poster

Hi..

I'm a third year software design student and I'm on my work placement, I'm hoping someone can help me.

I've written a macros to parse a word document to extract tabular data and export it to excel based on unique table headings. The problem I now come up against is I need to extract paragraphs also, it's not as easy as setting range etc.. All paragraphs are under chapter headings..the headings are all in the 'h2' style. The method (which i hope I can follow through on) is to search through all text in 'h2' style for the required heading by searching for a unique string of for example "6.17 Impacted Interfaces"

My question is can you search only text in a specific heading style for a unique string? And if so...how? My plan is, find the heading, then process what comes next after heading.

Any help on the would be very gratefully received...

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.