Misbehaving Macro - can this be made more simple /more stable?

[Dan B]

Hello All.

I while back, Grham Mayor helped me with the code below (in fainess he wrote
most of it). It's supposed print PDF files of a bunch of Word docs that I
already have made. The word docs are question answer sheets with the answers
in text boxes - by chnaging the font colour of text in text boxes to white, I
can hide the answers. The idea is to end up with a 'questions only' version
and 'questions & answers' version of each Word doc in PDF form (all automated
to the point where you just choose the floder of docs to convert).

Graham did a great job to the point where it works at his end but only
sometimes at my end and with mixed success.

Can anybody help with this code or suggest another way?

I'm running Word 2003 and Acrobat Pro 8 on Win XP.

Please consider that I can program a bit in other languages but have no
knowledge of VB.

Here's the exisiting Macro code:

Sub BatchPrint2PDF()
Dim DocList As String
Dim DocDir As String
Dim sPrinter As String
Dim aShape As Shape
Dim fDialog As FileDialog
Set fDialog = Application.FileDialog(msoFileDialogFolderPicker)

With fDialog
.Title = "Select Folder containing the documents to be printed to PDF
and click OK"
.AllowMultiSelect = False
.InitialView = msoFileDialogViewList
If .Show <> -1 Then
MsgBox "Cancelled By User"
Exit Sub
End If
DocDir = fDialog.SelectedItems.Item(1)
If Right(DocDir, 1) <> "\" Then DocDir = DocDir + "\"
End With

On Error Resume Next
MkDir DocDir & "Temp\"

If Documents.Count > 0 Then
Documents.Close SaveChanges:=wdPromptToSaveChanges
End If

'First loop creates the extra documents in the temporary folder
DocList = Dir$(DocDir & "*.doc")
Do While DocList <> ""
Documents.Open DocList
With ActiveDocument
sname = Left$(.Name, (Len(.Name) - 6)) & "-a.doc"
.SaveAs DocDir & "Temp\" & sname
.SaveAs DocList
sname = Left$(.Name, (Len(.Name) - 6)) & "-q.doc"
.SaveAs DocDir & "Temp\" & sname
.SaveAs DocList
.Close SaveChanges:=wdDoNotSaveChanges
End With
DocList = Dir$()
Loop

'Second loop formats all the files ending in q.doc to lose the answers
DocList = Dir$(DocDir & "Temp\*q.doc")
Do While DocList <> ""
ChDir DocDir & "Temp\"
Documents.Open DocList
With ActiveDocument
For Each aShape In .Shapes
If aShape.Type = msoTextBox Then
With aShape
If .TextFrame.HasText Then
.TextFrame.TextRange.Font.Color = wdColorWhite
End If
End With
End If
Next
.Close SaveChanges:=wdSaveChanges
End With
DocList = Dir$()
Loop

'Final loop outputs all the document files in the temp folder to PDF
DocList = Dir$(DocDir & "Temp\*.doc")
Do While DocList <> ""
ChDir DocDir & "Temp\"
Documents.Open DocList
ActivePrinter = sPrinter
With Dialogs(wdDialogFilePrintSetup)
sPrinter = .Printer
.Printer = "Adobe PDF"
.DoNotSetAsSysDefault = True
.Execute
End With
With ActiveDocument
.PrintOut
ActivePrinter = sPrinter
.Close SaveChanges:=wdDoNotSaveChanges
End With
DocList = Dir$()
Loop
End Sub

 

[macropod]

Hi Dan,

So what parts aren't working the way you want them to, and what is it they do that you want done differently?

Cheers

 

[Jean-Guy Marcil]

Hello All.

I while back, Grham Mayor helped me with the code below (in fainess he wrote
most of it). It's supposed print PDF files of a bunch of Word docs that I
already have made. The word docs are question answer sheets with the answers
in text boxes - by chnaging the font colour of text in text boxes to white, I
can hide the answers. The idea is to end up with a 'questions only' version
and 'questions & answers' version of each Word doc in PDF form (all automated
to the point where you just choose the floder of docs to convert).

Graham did a great job to the point where it works at his end but only
sometimes at my end and with mixed success.

I believe that if you want someone to help, you need to be more explicit here.

If Graham states that it work at his end, but you are experiencing problems
at your end, given that you are both using the same code, this means that the
environment is diffrent.

I will assume that both Graham and yourself were using Word 2003 and Acrobat
8.

This means that the only environment that matters here is either the folder
strructure or the Word documents themsleves.

You state that it only works sometimes and with mixed success.
What does this mean?
It sometimes works, but partially...
It sometimes does not work at all...
It alway works, but with slight errors...
Does the failure always manifest itself in the same manner...

You have to describe the expected behaviour (I believe you have, above) and
the observed results (I do not believe you have done this). In a case like
this you need to identify a trend. In other words, you have to track down a
possible cause... Are failures associated with the same type of folder
structure, document content, document containing objects of same type, etc.

When coding, especially something complex like this, you need to develop the
sleuth in you...

 

[Dan B]

Thanks for the quick response guys. Apologies for the sparse info in the
first post, I just didn't want to put anybody off from responding by drowning
them in information.

More info:

Some times Acrobat hangs when converting.

The most common problem is that not enough new files are saved off in the
'Temp' folder to open and print PDFs from. I'm worried that maybe the PDF
conversion is kicking in before all files have been saved off to the Temp
folder (this is the only way I can explain the random performance - it could
be dependent on available CPU power as to how quickly/slowly it gets through
each loop).

As a result I keep ending up with below the expected amount of newly saved
docs in 'Temp' and therefore not enough PDFs. I'm pretty sure that the
problem lies in the first loop of the code.

I've also seen varying numbers of files being saved off in to Temp (always
less than required), I'm not sure if this is linked to different filenaming
conventions, CPU resource or something more bizarre.

It does seem that the newly saved docs that end up in Temp are in expected
pairs however - eg. 'filname1-q.doc' is never missing its partner -
'filname1-a.doc'.

Can any of you guys get the Macro to perform as it should at your end? Am I
going mental?

Cheers,

Dan.

 

[Helmut Weber]

Hi Dan B,

with activedocument
.printout Background:=False
....

"Background:=False" cures so many problems,
it might cure your problem, too.

--

Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Vista Small Business, Office XP

 

[Dan B]

Thanks Helmut, that seems to have stopped Acrobat from hanging on occasion.

My problem with not enough files being correctly saved in the Temp folder
still persists however.

Any ideas anybody?

Cheers,

Dan.

 

[Jonathan West]

Thanks Helmut, that seems to have stopped Acrobat from hanging on
occasion.

My problem with not enough files being correctly saved in the Temp folder
still persists however.

Any ideas anybody?

Try setting Options.BackgroundSave = False

 

[Jean-Guy Marcil]

Thanks Helmut, that seems to have stopped Acrobat from hanging on occasion.

My problem with not enough files being correctly saved in the Temp folder
still persists however.

In my experience, using Dir when opening/manipulating files has very often
given unpredictable results. Maybe that is your problem...

Try using the File System Object instead. Much more robust.
Here is an example that scans a folder and counts the Word files.
Use that to adapt your code...

'Set a reference to the Microsoft Scripting Runtime in
'the VBA editor: Tools > References


Dim fso As New FileSystemObject
Dim fldrTarget As Folder
Dim docFile As File
Dim i As Long

i = 0

Set fldrTarget = fso.GetFolder("C:\My Documents")
For Each docFile In fldrTarget.Files
If Right(docFile.Name, 3) = "doc" Then
i = i + 1
End If
Next

Set fso = Nothing

MsgBox "There were " & i & " Word files in the target folder"

 

[Dan B]

Thanks for the tip but I'm not comfortable enough with VB to adapt that for
my current code.

I have made some progress of my own though...

It seems that the filenames of the docs themselves appear to have a large
influence.

I managed to get the correct number of new docs and PDFs earlier by
converting the following filenames:

a-y1-u2-teach-a.doc
file 5.doc
file number 4.doc
third file with random name.doc
y1-u1-teach-a.doc

However, these random filenames won't do for what this macro is designed for
because we are expecting a filenaming convention for the docs to be converted:

y1-u1-teach-a.doc
y1-u2-teach-a.doc
y1-u3-teach-a.doc
y1-u4-teach-a.doc
y1-u5-teach-a.doc

The list above only processes the first file (alphabetically). I end up
with its -q and -a versions saved off in the Temp folder and then converted
to PDF. 2 files - not the 10 I was hoping for.

I've tested to see if it's got anything to do with length of filename but it
doesn't seem to change anything.

So it seems that something in the first loop of the code is freaking out
over our filenaming convention but I can't see what. Does it think that the
rest of the files are the same and therefore not bother with them?

Any ideas peeps?

Getting there!

Cheers,

Dan.