Word Macro Programming

[marycp]

I have a word template and want to make it automatically set a field called articleID to the next number that it gets out of a SQL database. I have the code to automatically populate the field with a number but when I try to setup the object connection I get an error at this line:

Set objConn = Server.CreateObject("ADODB.Connection")

The error I get is "Run-time error '424': Object Required"

Can Macros access SQL dbs? I can't store the article ID in the registry because it would only be unqiue to one person instead of a group of people.

Any thoughts?
TIA
Mary

[doctorgonzo]

Try adding this line to the top of the procedure, if you don't have it already:

Dim objConn as New ADODB.Connection

The problem might be with the Server object. Are you initializing it correctly? It would probably be easier to create the connection on the client side instead of the server side.

[marycp]

Originally posted by doctorgonzo Try adding this line to the top of the procedure, if you don't have it already: Dim objConn as New ADODB.Connection The problem might be with the Server object. Are you initializing it correctly? It would probably be easier to create the connection on the client side instead of the server side.

That didn't work. I am not at all familiar with VB and got this code from an ASP page. Maybe I should create the connection by the client since the client is running it. How do I create a client object?

[doctorgonzo]

If you got the code from an ASP page, it probably won't work.

The Connection object needs a ConnectionString to be able to connect to the SQL Server. For example, the ConnectionString for the database I use is this:

"Provider=SQLOLEDB.1;Data Source=SQLSERV1;Initial Catalog=MyDatabase;User ID=MyUser;Password=MyPassword"

You can easily modify this string for your implementaion by changing the name of the server (Data Source), database (Initial Catalog), username (User ID), and password. Then, open a connection by setting the ConnectionString property and using the Open method, like this:

Set objConn = New ADODB.Connection
objConn.ConnectionString = "Provider=SQLOLEDB.1;Data Source=SQLSERV1;Initial Catalog=MyDatabase;User ID=MyUser;Password=MyPassword"
objConn.Open

You don't have to create a connection to work with a recordset. You can pass the ConnectionString when using a Recordset's Open command instead of explicitly creating a connection.

[marycp]

Ok this is all helping. But when I put this in my code it said "Compile Error: User defined type not defined"

Do I need to include any libraries?

Sorry for all the questions. It's not my first language.

Thx

[doctorgonzo]

Yes, make sure you include the Microsoft ActiveX Data Objects library. Version shouldn't really matter.

[marycp]

How do you do that? I couldn't find it in the object browser of the microsoft visual basic editor that comes with M$ Word.

[doctorgonzo]

In the VBA editor, go to Tools and then References. There should be at least one version of the ActiveX Data Objects library available in the list. If not, do a search for files on your PC that start with "msado"

[marycp]

Thanks so much. This works beautiful

[doctorgonzo]

No problem. If you have any other questions, just post back.

[marycp]

Originally posted by doctorgonzo No problem. If you have any other questions, just post back.

OK, this is really confusing. I wrote the Macro and it works for me on my PC and on my laptop. I log in as the same user on both machines. However when anyone else tries to run it they get these two errors one right after the other:

1. Could not open macro storage

2. Compile error: Can't find project or library

I did some research and #1 is usually a sign of a corruption but other people have it and I find that weird. She removed normal.dot but it still doesn't work. I even had her set macro security to low and it doesn't work.

Any ideas?

[doctorgonzo]

The "Can't find project or library" may mean that type libraries are missing and can't be found. Are the other users trying to run this from their own PCs, or are they simply logging into your PC as different users?

[marycp]

They are running them from their own PCs with the same version of word.

[doctorgonzo]

If possible, go to their PC and go to the VBA macro editor. Go to Tools...References to make sure that all the references are present. Type libraries that are on your PC may not be on theirs; this is especially common with computers that have different versions of Windows. I am guessing that the ADO (ActiveX Data Objects) library is the one that is missing.

[marycp]

I'll try that.. She is bringing her laptop in tomorrow for me to look at. Thanks again.

[marycp]

Ok, the compile error seems to be located on the following line:

Set fField = ActiveDocument.FormFields("ArticleID")

Now fField is not declared above and if I declare it as type "Field" it gives a compile error at this line:

fField.Result = CStr(iDefault)

Is fField part of a default library that most users don't have?

I got the script from M$ web site but of course they don't explain it for the novice vb programmer.

Do you have any idea?

Thanks

[doctorgonzo]

Try declaring fField as type FormField, not a plain Field.

[marycp]

Thanks. I got rid of it by not using fField but simply using ActiveDocument.FormFields("ArticleID") its a little longer to type, but worked.

Now I just have to figure out why everyone but me gets a "Runtime error 5981 Could not open macro storage"....

[doctorgonzo]

What version of Office are you using?

[marycp]

You won't believe this, but I re-created from scratch the entire template and now it works. The template must have been corrupted somehow.

Thanks for all your help!