Excel/Word timing problem

[jwise]

Some months ago, I wrote some code to send values from an Excel worksheet to a Word document and print that document. The values were from a row, with multiple rows rendering multiple print files. VBAX responders provided several items that assisted in this function. When I used it recently, it began "skipping" rows, i.e. there were about 33 documents produced instead of the intended 42.

When I originally encountered this problem, Charlize suggested that I add a "wait" after my "print command". This worked... until now. Since my symptoms were identical to the original symptoms, I have added additional "wait" commands and the problem is solved... again.

Chances are my shotgun technique is not very good. I am enclosing two snippets of the code, showing the "before" and "after". Where do I really need these waits? I thought I understood the original answer!

The original thread : http://www.vbaexpress.com/forum/show...t=12181&page=2

Set wrdApp = New Word.Application
    wrdApp.Visible = False
    Set wrdDoc = wrdApp.Documents.Open(FileName:="c:\Recap\New Monthly Recap.dot")
    wrdApp.Application.PrintOut FileName:="", Range:=wdPrintAllDocument, Item:= _
    wdPrintDocumentContent, Copies:=1, Pages:="", PageType:=wdPrintAllPages, _
    Collate:=True, Background:=True, PrintToFile:=False, PrintZoomColumn:=0, _
    PrintZoomRow:=0, PrintZoomPaperWidth:=0, PrintZoomPaperHeight:=0
    Application.Wait (Now + TimeValue("00:00:04"))
    Set wrdDoc = Nothing
    wrdApp.Quit savechanges:=False
Set wrdApp = Nothing

This is the original code with the one inserted wait after the print. The second example shows the added "wait" commands.

 Set wrdApp = New Word.Application
    Application.Wait (Now + TimeValue("00:00:01"))
    wrdApp.Visible = False
    Set wrdDoc = wrdApp.Documents.Open(FileName:="c:\Recap\New Monthly Recap.dot")
    Application.Wait (Now + TimeValue("00:00:02"))
    wrdApp.Application.PrintOut FileName:="", Range:=wdPrintAllDocument, Item:= _
    wdPrintDocumentContent, Copies:=1, Pages:="", PageType:=wdPrintAllPages, _
    Collate:=True, Background:=True, PrintToFile:=False, PrintZoomColumn:=0, _
    PrintZoomRow:=0, PrintZoomPaperWidth:=0, PrintZoomPaperHeight:=0
    Application.Wait (Now + TimeValue("00:00:04"))
    Set wrdDoc = Nothing
    Application.Wait (Now + TimeValue("00:00:01"))
    wrdApp.Quit savechanges:=False
Set wrdApp = Nothing

Thanks for your input.

[Bob Phillips]

Here is something that you could try.

As long as wrdApp is declared at at least module scope, instead of closing Word down inline, put the closure code ingto a separate procedure, and issue an Ontime Command inline in place of that code

Application.Ontime Now+TimeSerial(0,0,1),"CloseWord"

BTW since when has Quit had a Savechanges argument?

[jwise]

Thanks again for your reply. I looked at it right after I got the notification, but due to a sudden situation, was not able to acknowledge your reply.

The added items on "Quit" came from something I saw... I routinely put this kind of stuff in because I am unsure of exactly what the ACTUAL requirements are. I'm a shotgunner! I should go back and clean up this kind of stuff.

[Bob Phillips]

But the big question is, did it solve your problem?

[jwise]

The situation is that my "shotgun technique" SOLVED the problem; all rows were printed. The question was really about which of the three added "wait" statements were really needed. That part is still unclear. The clarity or lack thereof may be ENTIRELY of my own making, so I am reluctant to ask for more assistance until I have properly investigated what I have.

Since this thing spits out 42 pages, I feel a little guilty just "trying" it. I admit that I have been a little reluctant to experiment in my normal fashion just because of this. If I cut the rows to say 5 or 10, the nature of the problem says that is not a very good test. On the mainframe, you can "hold" the print, look at it on the spool, and then can it... no trees involved. Perhaps there is a way in this realm as well. I'm just not very well versed on all these tricks.

I do thank you. I expect to be able to resume this search on Tuesday.

[mdmackillop]

Hi Jwise
Why not print to pdf or mdi when testing. It may not give 100% results, but it saves on trees!

[Bob Phillips]

The situation is that my "shotgun technique" SOLVED the problem; all rows were printed. The question was really about which of the three added "wait" statements were really needed. That part is still unclear. The clarity or lack thereof may be ENTIRELY of my own making, so I am reluctant to ask for more assistance until I have properly investigated what I have.

Since this thing spits out 42 pages, I feel a little guilty just "trying" it. I admit that I have been a little reluctant to experiment in my normal fashion just because of this. If I cut the rows to say 5 or 10, the nature of the problem says that is not a very good test. On the mainframe, you can "hold" the print, look at it on the spool, and then can it... no trees involved. Perhaps there is a way in this realm as well. I'm just not very well versed on all these tricks.

I do thank you. I expect to be able to resume this search on Tuesday.

Printpreview?

[TonyJollans]

Try changing the Printout Background argument to False. You shouldn't then need the Waits at all.

[jwise]

Thanks to all responders.

Tony's suggestion (change background print to FALSE) seemed very plausible because I thought I understood why Excel/Word was acting this way... I caused it! However, when I eliminated ALL waits and changed the parameter to "FALSE", I received 23 pages of output. This is the exact symptom of my original problem. My gut feel is that this is the correct approach, but some of those other "waits" need similar preceding statement changes.

I still have this logistic problem of re-directing the output so that the 42 pages are not actually printed. When I invoke my macro, I get no option to choose a printer destination. It is printing to the "default" printer, which is a high-speed laser. It's interesting watching this print, because it will print a page (sometimes two, three, or four pages), then wait a few seconds, and begin printing again. SInce it should print 42 pages, the 23 pages printed was about half the expected number.

My next attempt will be to see if I can find similar options to change all the statements which preceded where my "waits" were located to make Word a "subroutine" and not a sub-process. My terminology is probably incorrect, my apologies.

[TonyJollans]

You have:

 
wrdApp.Application.PrintOut ' etc.  
Set wrdDoc = Nothing

I wonder whether dropping the doc reference is causing an abrupt forced close of the document. It might be worth trying closing the document properly first.

Akso - and this is just speculation - might it make a difference if you did

wrdDoc.Printout ' etc.

instead of

wrdApp.Printout ' etc.

[jwise]

Tony, thanks again for your thought-provoking tip.

Somehow, I discovered that I reverted to the "Background = True". I also thought about the fact that I destroyed the wrdApp each time through the loop, and recreated it. I went to Word and "recorded a macro", to determine how to "delete" the current document. The new loop does not destroy Word, it does "ActiveDocument.Close".

I am assuming this "ActiveDocument.Close" defaults to "saving changes". When I ran the macro, I got all the documents BUT only the first printed correctly. The document has around 14 bookmarks, each of which is updated by values from the Excel row. Thus my second report had the first report's data as well. By #42 there was lots of data on a page (pages)!

I'll try this again with these updates and see what happens.

Thanks again.

[jwise]

Thanks to all responders.

The "Wait" statements are gone and it is printing all rows. My problem before was with the "ActiveDocument.Close". I added the parameter "SaveChanges:=False" and each document only had its data.

I did "improve" the code by removing the create/destroy Word application statements from the loop. The object create is now before the loop, and the destroy is after the loop. The loop "opens" the template file, updates the bookmarks within the template, prints the document, and then closes the document without saving.

I say "improve" because the printer now basically begins printing the documents and continues until the last without pausing during this printing. That was annoying. It was also annoying because other print jobs could be interspersed within my documents. Although this may be load-dependent, it certainly reduces the amount of time it takes for the documents to print.

Thanks again to all responders.

I am still at a loss as to how I would handle this problem without wasting so much paper. If there were 1,000 rows of data, how do you handle the testing? Do I need some code at the beginning to ask for the destination printer, and send the output to a PDF file (a fake printer) or the real printer?

[mdmackillop]

Set the PDF printer as the default printer, or record code making the change to get the relevant details

[jwise]

I was not seeing the obvious. By changing the default printer during testing, all the trees are safe. It's also kind of embarrassing to have all of those stacks of paper sitting around my desk. I have an image to keep up!

Seriously, I overlooked the obvious. I think I am making some of these problems more difficult than they really are.

Thanks again to all responders for all the help.

[TonyJollans]

I think I am making some of these problems more difficult than they really are.

I think we all do that from time to time. When you're close to a problem you often can't see the wood for the trees. That's where forums like these can really help and I'm glad you're all sorted now.