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 = trueIt 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:
- http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dv_wrcore/html/wrgrfwordobjects.asp
- http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dv_wrcore/html/wrgrfapplicationobject.asp
- http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dv_wrcore/html/wrgrfdocumentobject.asp
- http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dv_wrcore/html/wrtskhowtoopenexistingdocuments.asp
- http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dv_wrcore/html/wrtskhowtosavedocuments.asp
Excel:
- http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dv_wrcore/html/wrgrfexcelobjects.asp
- http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dv_wrcore/html/wroriautomatingexcelusingexcelobjectmodel.asp
- http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dv_wrcore/html/wrgrfexcelapplicationobjectproperties.asp
- http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dv_wrcore/html/wrgrfexcelapplicationobjectmethods.asp
- http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dv_wrcore/html/wrtskhowtoopenworkbooks.asp
- http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dv_wrcore/html/wrtskhowtosaveworkbooks.asp
- http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dv_wrcore/html/wrgrfExcelWorkbookObject.asp
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.AddDon’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 = NothingThe 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 = TrueIf 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 = NothingA 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?
