B
BT
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
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