Loop Error after converting Word to PDF...Almost there!!!

[jhhardin]

Hi, I've created a macro that opens many word documents, saves them as pdfs and closes them. The problem I have is coming after this is done. The loop used to save each file is throwing an error after all the files have closed. Any you might be able to offer would be much appreciated.

Error: Runtime Error 4248
The Command is not available because no document is open.

I know why it's doing it, I just need to know how to fix it. :)


Sub Convert_To_PDF()


'Open a screen to select the files you want to run the macro on and select files
CommandBars.FindControl(ID:=23, Visible:=False).Execute

'remove the .doc or .docs and save as PDF in the same file
Application.DisplayAlerts = wdAlertsNone
Dim j As Long
Do
j = InStrRev(ActiveDocument.FullName, ".")

ActiveDocument.ExportAsFixedFormat _
OutputFileName:=Left(ActiveDocument.FullName, j) & "pdf", _
ExportFormat:=wdExportFormatPDF, OpenAfterExport:=False, _
OptimizeFor:=wdExportOptimizeForPrint, Range:=wdExportAllDocument, _
From:=1, To:=1, _
Item:=wdExportDocumentContent, IncludeDocProps:=True, KeepIRM:=True, _
CreateBookmarks:=wdExportCreateNoBookmarks, DocStructureTags:=True, _
BitmapMissingFonts:=True, UseISO19005_1:=False

'close the document
ActiveDocument.Close
Loop
End Sub

[fumei]

Would not using DIR be better?

[jhhardin]

Forgive my ignorance. I am fairly new to vba, particularly for word. If there is a better way to do this, I would love to hear it.

The reason I chose this route is its easy to understand and fairly logical. The macro will be used by co-workers to convert files and will allow for very little setup on my part. If there is a way to fix that Do loop statement so as not to throw the error, that would be preferable, but if not, how do I simply tell the macro not to throw the error at all?

Thanks!

[fumei]

OK, first of all WHERE is this code? In what document? I suspect it is not in a document, but is in a module in Normal. let me see if I can walk you through.

Let's say you have document Yadda open. You run the code. You select two files (just to make it easy), DocA and DocB.

BOTH documents are open - I will get back to that later. The point being is that the code opens ALL documentws selected. Not one at a time, but ALL of them.

OK? So now the first document is processed - to PDF - and then closed. The second document is processed, and then closed.

But you have nothing to tell the code to stop running...so it does. In fact it will keep going until it closes all documents...including any already open before you started the code running.

So if the code is in the Normal module, it will keep going until ALL documents are closed, and keeps going...but there are no more documents, thus the error.

The simplest solution is to add the following to your Do loop:
Do Until Documents.Count = 0
Your code will process all document until the document count is zero. This is sloppy, but it will work. Work in that you will not get the error.

It is sloppy because - again for simplicity sake you have selected two documents - the code does not act just for those two selected documents. It acts on ALL documents.

[jhhardin]

Thanks Fumei,

Sloppy but functional with just a small amount of training. Of course, I am happy to entertain other suggestions, but this is working great for now.

[jhhardin]

Fumei got me thinking, so I made a few tweaks that make this a very user friendly macro.

1. It now loops through all open documents and closes them before running the rest of the macro. It will ask the user if they want to save those documents before closing.
2. A Msgbox pops up at the end of the macro asking if you would like to run it again and convert more files.

This seems to work pretty well for up to 20 documents at a time and I think is a pretty nifty tool at this point!

Sub Convert_2_PDF()
'
' Convert_2_PDF Macro
'
'

'Close all open Word documents
Do Until Documents.Count = 0
ActiveDocument.Close
Loop


'Open a screen to select the files you want to run the macro on and select files
CommandBars.FindControl(ID:=23, Visible:=False).Execute

'remove the .doc or .docs and save as PDF in the same file
Dim j As Long
Do Until Documents.Count = 0
j = InStrRev(ActiveDocument.FullName, ".")

ActiveDocument.ExportAsFixedFormat _
OutputFileName:=Left(ActiveDocument.FullName, j) & "pdf", _
ExportFormat:=wdExportFormatPDF, OpenAfterExport:=False, _
OptimizeFor:=wdExportOptimizeForPrint, Range:=wdExportAllDocument, _
From:=1, To:=1, _
Item:=wdExportDocumentContent, IncludeDocProps:=True, KeepIRM:=True, _
CreateBookmarks:=wdExportCreateNoBookmarks, DocStructureTags:=True, _
BitmapMissingFonts:=True, UseISO19005_1:=False

'close the document
ActiveDocument.Close
Loop

'Call macro again?
Dim Answer
Answer = MsgBox("Success! Convert more Word files to PDF?", vbYesNo, "Run Macro?")
If Answer = vbYes Then
Call Convert_2_PDF
End If

End Sub

[fumei]

This is sort of bad form. You are forcing all documents closed. It is a bit rude. What if someone has a document open that has nothing to do with this PDF business? What if they want to do the PDF thing...and leave their current open document alone - since it has nothing to do with this process? You are not letting do that, you are making EVERYTHING be involved with this.

OK, yes it works..but it is bad form.

And again, your code opens ALL the documents selected. That could potentially have serious consequences in terms of memory usage.

Nevertheless, if you find it acceptable then...shrug...that is good.

[Paul_Hossler]

1. If you open too many documents, I'd be afraid of running out of resources

2. Having the macro call itself might lead to stack overflow

3. It would seem that the document with the macro will close itself when you ActiveDocument.Close

4. fumei is correct that it is not the most 'polite' macro

5. Some suggestions:


Option Explicit
Sub Convert_2_PDF()

Dim Answer As VBA.VbMsgBoxResult

'Close all open Word documents except this one
Do Until Documents.Count = 1
If Not ActiveDocument Is ThisDocument Then ActiveDocument.Close
Loop


If Documents.Count = 1 Then Exit Sub

Answer = vbYes

Do While Answer = vbYes
Call Convert_3_PDF

'Call macro again?
Answer = MsgBox("Success! Convert more Word files to PDF?", vbYesNo, "Run Macro?")
Loop

End Sub

Sub Convert_3_PDF()

Dim j As Long

'Open a screen to select the files you want to run the macro on and select files
CommandBars.FindControl(ID:=23, Visible:=False).Execute

'remove the .doc or .docs and save as PDF in the same file
Do Until Documents.Count = 0

j = InStrRev(ActiveDocument.FullName, ".")

ActiveDocument.ExportAsFixedFormat _
OutputFileName:=Left(ActiveDocument.FullName, j) & "pdf", _
ExportFormat:=wdExportFormatPDF, OpenAfterExport:=False, _
OptimizeFor:=wdExportOptimizeForPrint, Range:=wdExportAllDocument, _
From:=1, To:=1, _
Item:=wdExportDocumentContent, IncludeDocProps:=True, KeepIRM:=True, _
CreateBookmarks:=wdExportCreateNoBookmarks, DocStructureTags:=True, _
BitmapMissingFonts:=True, UseISO19005_1:=False

'close the document
ActiveDocument.Close
Loop
End Sub



6. I'd use Dir() to fill a multi-select list box with just the file names, let the user check the ones to convert, and just open and export and close each file one at a time

Not really tested, but something to consider

Paul

[fumei]

One issue with DIR is if the folder has a number of filesd that you do NOT want to work on. However, if the folder in question has all its files to be processed, then for sure DIR would be the best route. Files would be processed one at a time.

[Paul_Hossler]

One issue with DIR is if the folder has a number of filesd that you do NOT want to work on. However, if the folder in question has all its files to be processed, then for sure DIR would be the best route. Files would be processed one at a time.



6. I'd use Dir() to fill a multi-select list box with just the file names, let the user check the ones to convert, and just open and export and close each file one at a time


I'd just not check the files that I didn't want converted. Seems as much effort as control-clicking to select in a FileOpen dialog

Paul

[Pho3NiX]

I found the problem interesting to work with file selection.
So I'll contribute back.
Here I open only one file at a time


Sub ConvertMultipleToPdf()

Dim wDoc As Word.Document
Dim FoundFile As Variant

Dim wDialog As FileDialog
Set wDialog = Application.FileDialog(FileDialogType:=msoFileDialogFilePicker)
wDialog.AllowMultiSelect = True

If wDialog.Show <> -1 Then
Exit Sub
End If

For Each FoundFile In wDialog.SelectedItems

Set wDoc = Documents.Open(FoundFile, ReadOnly:=True, Visible:=False)

wDoc.ExportAsFixedFormat _
OutputFileName:=wDoc.Path & "\" & Left(wDoc.Name, InStrRev(wDoc.Name, ".")) & "pdf", _
ExportFormat:=wdExportFormatPDF, OpenAfterExport:=False, _
OptimizeFor:=wdExportOptimizeForPrint, Range:=wdExportAllDocument, _
Item:=wdExportDocumentContent, IncludeDocProps:=True, KeepIRM:=True, _
CreateBookmarks:=wdExportCreateNoBookmarks, DocStructureTags:=True, _
BitmapMissingFonts:=True, UseISO19005_1:=False

wDoc.Close SaveChanges:=False

Next


End Sub

[jhhardin]

Phoenix, I'm digging on your solution. One file at a time works pretty well.

Fumei and Paul, thanks for the feedback. My coworkers have been using the macro and loving it. We had about 8000 Word files to convert to PDF. They were more than willing to close their open Word documents in order to convert the files they needed to convert. For every day use, perhaps a bit rude, but for a mass conversion project, it's working just fine.

I am definitely interested in working out some of the kinks, though, so the tool could continue to be useful. Phoenix's suggestions are a great start.

Thanks everyone!

[fumei]

You may want to add an explicit destruction of the dialog object.

Set wDialog = Nothing

Technically, Word should handle this cleanly, and for the most part it does. Still it is a good habit to get into as there are some objects (which you may use later) that can cause problems if not explicitly destroyed.

Others: there is no need to to renew the debate on the explicit destruction of objects. My point is that IMO it is still a good practice to do so.

[jhhardin]

You may want to add an explicit destruction of the dialog object.

Set wDialog = Nothing



You've peaked my interest. I honestly have no idea what this does or what it's purpose is. I would love a bit more information. And the fact that some controversy appears to be swirling around the topic helps it's case.

Fumei, would you mind expanding a bit?

[fumei]

Dim wDialog As FileDialog
Set wDialog = Application.FileDialog(FileDialogType:=msoFileDialogFilePicker)
This declares an object - the Dim statement. It then SETS that object. The Set instructions allocates a memory block to the object. Note that allocates is NOT the same as assign.

The object is CREATED. It remains "in existence" until it is destroyed. As stated, VBA - for the most part - is fairly tidy, and the object is destroyed (releasing the memory) on code termination.

This is NOT the same as declaring a data type (which are NOT objects) and assigning memory by giving it a value. A string data type that is given a valueDim strBlah as String
strBlah = "yadda"is not given a specific block of memory. It takes memory dynamically. When a procedure terminates ALL data type variables are gone. This is SCOPE and Scope is one of the most important concept in VBA programming.

An object - again for the most part - is also terminated when a procedure terminates. At least there is one side of the debate that insists this is the case.

Except....sometimes this does not seem to be the case, in particualr Application objects - when you declare and Set an instance of Word (Excel, Access etc.) itself. Application objects, because they are such high level objects with literally thousands of components, are allocated massive (relative speaking) blocks of memory.

The other side of the debate believes (and I am in this camp) that memory sometimes leaks. Perhaps things are better than they used to be. No, I amend that. Things ARE better than they used to be. In the mid-90s (I think) Word 6 was so bad that it stole memory and hide it. It was so bad that you could open Word, open a document and wait. Eventually Word stole enough memory that Windows itself could not run!

There is another factor. Word (and Excel) is a multi-instance application. You can different instancess of Word happening at the same time. Outlook on the other hand is a single instance appluication. You can only have one.

So say you have Word open. You can be in Excel and create a NEW instance of Word, and it can function independently of the existing Word. If you do not explicitly destroy that new instance - say you forgot - when you close Excel, that other instance of Word is still there. It still has that chuck of memeory allocated to it. Therefore particularly with application objects always destroy it when you are done, with a Set wordApp = Nothing.

Again, the debate is that lower level objects (a dialog, a table, a paragraph...anything that can be made into an object) ARE internally destroyed when the procedure terminates. And again, for the most part I think this is true.

However..I still think it is a best practice to destroy all objects explicitly.

If you do not understand Scope (where variables - not objects - are valid), look it up in Help. Help is not hugely well written for this, but it is not bad. Again, this is a VERY VERY important concept. In my VBA course I give it a full two hours.

[Pho3NiX]

Thanks Fumei for the remark.

I'll add dialog to my list of thing being worth to set as nothing.
So far I only do it religiously when I use the "new" keyword or I call an object in an external library. I would probably recommend setting wDoc as nothing too... as hidden word document are a pain to deal with.

----

However I will add that in my understanding of VBA, there is no direct calling to destructor. Garbage collecting is done by instance counting, and Set oMyPtr = Nothing merely take the object pointer and make it a null pointer (as well as triggering instance counting).

I tend to think that exiting a function also clear those pointers...

Additionally using Set oMyPtr=Nothing on something else than last pointer of that object can induce the reader in error thinking the object will be destroyed...
For example, something like that will not destroy the object Foo

Set Foo = New Bar
Set Yadda.MyFoo = Foo

Set Foo = Nothing

[fumei]

Quite correct, but I did not want to get too many people confused. Foo is not destroyed because there is a circular reference. The pointer Yadda.Foo points to Foo (which is pointing to Bar), so even if Foo = Nothing there is STILL a reference to Bar.

You can (sort of) say that Foo is destroyed (but technically you are correct...the reference is terminated), but the OBJECT Bar is not.

Regarding the garbage collector, again you are quite correct. There is no direct call to the destructor. However, with NO circular referencing, as instance counting IS initialized, Set Blah = Nothing can be considered, from a newbie POV, a clean removal of the allocated memory.

While it certainly is possible (if carefully legitimate) to make circular references, this exception to Set Nothing = instance counting = destruction makes it even MORE important (IMO) to use Set Nothing as a rule (rather than an exception).

Thoughts?

p.s. also IMO the code¸

Set Foo = New Bar
Set Yadda.MyFoo = Foo

is not a best practice because of the circular reference. Unless there is a definite need for such (and there CAN be), this should be avoided.

Set Foo = New Bar
Set Yadda.MyFoo = New Bar

still retains both Foo and Yadda.MyFoo as being a Bar (which obviously is what is desired), but does in fact separate them memory wise.

Granted this can be considered as a sub-strata of the Nothing debate.

[fumei]

And of course we could REALLY hijack the thread and expand into a sidebar discussion of ByRef and ByVal...

Just kidding (although newcomers would be well advised to look these up).