I have an Access database (Office XP) with a button attached to a form. When the button is pressed, I use VBA to create a recordset and new word application and to open a word document stored on the server (the document is empty).
My procedure loops through the recordset and stores certain information in various variables. These variables are passed as arguments to another sub procedure (PrintToWord()), which writes a letter to the appropriate customer inserting the variables into the appropriate places in the letter.
My program has three faults, which I think are to do with the way I create and close the word application.
1) If I run my application and then press my button a second time after it has finished writing the letters, the program complains that it cant find an object. When the debug window opens, it highlights the first line of code in my PrintToWord() procedure (Selection.ParagraphFormat.Alignment = wdAlignParagraphRight)
I can get around this by using DoCmd.Quit in the last line of the calling procedure to close access down when its finished. I can then open Access again and run the application.
Although this isn’t really a problem, because once the letters are written, there is no point in writing them again. Its just from a learning point of view, that I want to know why this happens and how to correct the problem.
2) The second problem is a bit more serious. If the user happens to have a word document open, even if it’s just minimised in the background, once the button is pressed, that open document will have all of the letters written to it instead of the one that the procedure is supposed to open.
I know I can tell the users to close down word before using it, but I would rather do it properly.
3) The most serious problem is if winword.exe is running in the background when Word is closed e.g. if you do a Ctrl + Alt + Del and bring up Task Manager and look at the processes tab.
Winword.exe seems to hang about mostly when Word is used through Outlook for writing email although it often seems to run in the background when both Word and Outlook is closed.
The program throws up an error box with a title of “Run Time Error 91” and a message of “object variable or with block variable not set”. On pressing debug, it highlights the first line of code in the PrintToWord() procedure as shown in problem 1 above.
This then leaves a lock on the actual word document ,which sits on our servers shared mapped drive. You can delete the lock/temp file that gets created when a word document opens, but the only way to release it is to go into computer management on the server and delete the file lock.
If word is closed and I close down winword.exe in task manager, my program works perfectly.
Here is some skeleton code from my program, showing the creation and killing of my word application (which is invisible so the user never sees it) and the call of the procedure, which writes the letters.
Any help with this matter will be very much appreciated.
Ian
Private Sub cmdCreateMailmerge_Click()
Dim MyWordApp As New Word.Application
Dim MyDoc As Word.Document
Set MyDoc = MyWordApp.Documents.Open("H:\Customer Services\Recall\Recall.doc"
MyDoc.Activate
.
.
Do Until RS.EOF
.
.
Call PrintToWord(AccountNumber, Orders, BatchNumber, Product, MyDoc, ExtraText, False, DelAddress)
.
.
Loop
.
.
MyDoc.Close SaveChanges:=wdSaveChanges, OriginalFormat:=wdWordDocument, RouteDocument:=False
MyWordApp.Quit
Set MyDoc = Nothing
Set MyWordApp = Nothing
RS.Close
DoCmd.Quit
End Sub
My procedure loops through the recordset and stores certain information in various variables. These variables are passed as arguments to another sub procedure (PrintToWord()), which writes a letter to the appropriate customer inserting the variables into the appropriate places in the letter.
My program has three faults, which I think are to do with the way I create and close the word application.
1) If I run my application and then press my button a second time after it has finished writing the letters, the program complains that it cant find an object. When the debug window opens, it highlights the first line of code in my PrintToWord() procedure (Selection.ParagraphFormat.Alignment = wdAlignParagraphRight)
I can get around this by using DoCmd.Quit in the last line of the calling procedure to close access down when its finished. I can then open Access again and run the application.
Although this isn’t really a problem, because once the letters are written, there is no point in writing them again. Its just from a learning point of view, that I want to know why this happens and how to correct the problem.
2) The second problem is a bit more serious. If the user happens to have a word document open, even if it’s just minimised in the background, once the button is pressed, that open document will have all of the letters written to it instead of the one that the procedure is supposed to open.
I know I can tell the users to close down word before using it, but I would rather do it properly.
3) The most serious problem is if winword.exe is running in the background when Word is closed e.g. if you do a Ctrl + Alt + Del and bring up Task Manager and look at the processes tab.
Winword.exe seems to hang about mostly when Word is used through Outlook for writing email although it often seems to run in the background when both Word and Outlook is closed.
The program throws up an error box with a title of “Run Time Error 91” and a message of “object variable or with block variable not set”. On pressing debug, it highlights the first line of code in the PrintToWord() procedure as shown in problem 1 above.
This then leaves a lock on the actual word document ,which sits on our servers shared mapped drive. You can delete the lock/temp file that gets created when a word document opens, but the only way to release it is to go into computer management on the server and delete the file lock.
If word is closed and I close down winword.exe in task manager, my program works perfectly.
Here is some skeleton code from my program, showing the creation and killing of my word application (which is invisible so the user never sees it) and the call of the procedure, which writes the letters.
Any help with this matter will be very much appreciated.
Ian
Private Sub cmdCreateMailmerge_Click()
Dim MyWordApp As New Word.Application
Dim MyDoc As Word.Document
Set MyDoc = MyWordApp.Documents.Open("H:\Customer Services\Recall\Recall.doc"
MyDoc.Activate
.
.
Do Until RS.EOF
.
.
Call PrintToWord(AccountNumber, Orders, BatchNumber, Product, MyDoc, ExtraText, False, DelAddress)
.
.
Loop
.
.
MyDoc.Close SaveChanges:=wdSaveChanges, OriginalFormat:=wdWordDocument, RouteDocument:=False
MyWordApp.Quit
Set MyDoc = Nothing
Set MyWordApp = Nothing
RS.Close
DoCmd.Quit
End Sub
