VBA problems with printing and editing a document

[sunjinsak]

Hi,

Firstly I’d like to apologise in advance for what is sure to be a long post but this is a rather odd problem that requires a bit of explaining so here goes…

I have a Word document (created in Word 2003 SP3) which is actually a stock letter that we have to use in work. For the purpose of example the file is called lettername.doc. Now, the stock letter must have certain information presented in a certain format in a certain position on the document. To help out (or at least attempt to) I modified the document using VBA so that a user form pops up when it’s opened and all the required info is input into that. A command button is then pressed which calls a sub I wrote to check the required info is present and in the correct format and then inserts it in the underlying document in the correct position. There is also a command button on the letter itself that, when clicked, prints the letter then closes it without saving changes (to preserve the underlying “template”).

So far so good – all seemed to be working fine, but then I got a request to add a “feature” to recall the last letter printed so that if someone had collected their print out and realised they’d made a mistake (put the wrong address on, misspelt something etc) they could just call the letter up again and make the amendment rather than having to start from scratch and fill out the whole user form again.

To do this (and I’m sure there’s a much better way but I’m not much of a coder) I added a few lines to my sub that saves the letter to the root of the user’s Z: drive as lettername_last.doc (turning alerts off first so as to “silently” overwrite any existing document) after inserting the info from the user form into the “template”. The user can then use the “Print” command button as normal to print the letter and exit without saving any changes.

Now, when a user next opens lettername.doc they can choose (via the user form) to ‘Recall last letter printed’ which exits lettername.doc without saving and opens up lettername_last.doc from their Z: drive. Again, so far so good – they can print the last letter using the “Print” command button and it all seems to work nicely.



Here’s where a rather unexpected problem occurs. If the user chooses not to recall the last letter printed and continues to create a new one they finish filling in the user form and press the “okay” button to insert the details, when they press the “Print” command button the document (which is only 2 pages long) tries to print somewhere around 300 pages before locking up the whole system forcing use of Task Manager to close Word and cancel the print job. This didn’t happen before I added the code to save the letter out as lettername_last.doc and I haven’t touched any of the other code at all!!

Also, and even more weirdly, when I now open the VBA editor to look at the code, if I make any changes (for example I tried to comment out the code that saves the document to see if printing worked okay without it again) and then try to save it gives me the following error message: “The disk is full. Free some space on this drive, or save the document on another disk” and won’t let me save… this is despite the fact that both the C: drive (where the “template” document resides) and the Z: drive (where the “last printed” copy resides have gigabytes of free space each! Also, if I create a new document or modify any other existing one it saves fine with no “space” issues.

So, to summarise in brief; the original document is stored on the C: drive (on the Desktop) and everything worked fine at first – including the code to print it. Then I inserted a few lines of code to save a copy of the document out before printing. Now the original document tries to print about 300 pages when the “Print” command button is used and won’t let me save any changes made to it, whilst the ‘last printed’ copy (stored on a user’s Z: drive) still prints fine if recalled. The original document is still only 2 pages long as far as I can tell.

The original document is 207Kb in size and the copy saved out is 212Kb and we’re running Windows XP SP3 with MS Office 2003 SP3.

Does anyone have any idea what is going on? I really hope someone can help.

If it helps, here’s the code I’m using to save a copy of the document before printing it…

Application.DisplayAlerts = False
ThisDocument.SaveAs "Z:\lettername_last.doc"
Application.DisplayAlerts = True

…and here’s the code I’m using to print the document…

Application.PrintOut FileName:="", Range:=wdPrintAllDocument, Item:= _
wdPrintDocumentContent, Copies:=1, Pages:="1, 2", PageType:=wdPrintAllPages, _
ManualDuplexPrint:=False, Collate:=True, Background:=True, PrintToFile:= _
False, PrintZoomColumn:=0, PrintZoomRow:=0, PrintZoomPaperWidth:=0, _
PrintZoomPaperHeight:=0

Thank you for taking the time to read this lengthy post and my apologies if I’ve confused you all but I wanted to give as much info as possible. This really is driving me crazy!

[fumei]

As you do not have all the code it is hard to say. As posted there is no reason for printing 300 pages. It sounds like some sort of loop, but again, you did not post all the code.

BTW, this is a good example of why you should use a REAL template.

[sunjinsak]

Thanks for getting back to me fumei.

The reason I didn't post all of the code is for confidentiality - the nature of the letter/client would become clear from the variable names. However when I'm back in the office on Monday I'll change the variable names and post the code if that helps?

As to using a real template - I'm not sure how. Happy to receive any suggestions on how best to achieve the same result with less hassle and greater efficiency.

Constructive criticism is also welcome