VBA - Memory not freed when Word Document Closed. 2

[ChrisW75]

Hi, I've written a VBA program to open and close a large number of word documents, saving as RTF and removing the password. The problem is that when a word document is closed, the memory doesn't seem to get freed up, so after processing about 2000 documents, I start getting warning messages about system/virtual memory.

Does anyone know how to free the memory after closing the word document?
Here's the code, any help is gratefully received. :-

=============================

Private Sub btn_Convert_Click()
On Error GoTo PROC_ERR

Dim inFile
Dim outFile
Dim from_Path
Dim to_Path
Dim passwd

Application.WindowState = wdWindowStateMinimize

' Get source and destination dir. Get Password.

from_Path = txt_From_Path.Text
to_Path = txt_To_Path.Text
passwd = txt_passwd.Text

' Open log files.
Open to_Path & "convert.log" For Output As #1
Open to_Path & "filenames.dat" For Output As #2

' Get list of files ot process.
inFile = Dir$(from_Path & "*.doc")

'Write messae to log.
Print #1, "Reading *.doc from " & from_Path
Print #1, "Writing *.rtf to " & to_Path

'loop through documents.
Do While inFile <> ""

outFile = Left(inFile, Len(inFile) - 3) & "rtf"

ChangeFileOpenDirectory from_Path
' Open file
Documents.Open fileName:=inFile, ConfirmConversions:= _
False, ReadOnly:=True, AddToRecentFiles:=False, PasswordDocument:=passwd _
, PasswordTemplate:="", Revert:=False, WritePasswordDocument:=passwd, _
WritePasswordTemplate:="", Format:=wdOpenFormatAuto

'move to destination directory
ChangeFileOpenDirectory to_Path

'Save file as rtf.
ActiveDocument.SaveAs fileName:=outFile, FileFormat:= _
wdFormatRTF, LockComments:=False, Password:="", AddToRecentFiles:= _
False, WritePassword:="", ReadOnlyRecommended:=False, _
EmbedTrueTypeFonts:=False, SaveNativePictureFormat:=False, SaveFormsData _
:=False, SaveAsAOCELetter:=False

'close document.
ActiveDocument.Close
'Documents.Close

'Log conversion.
Print #1, "Time: " & Format$(Now(), "dd-mmm-yyyy hh:mm:ss AM/PM") & " File " & inFile & " converted to " & outFile & " successfully."
Print #2, outFile
inFile = Dir
Loop


PROC_EXIT:
On Error Resume Next
Close #1
Close #2
Unload Me

Exit Sub
PROC_ERR:
lError = Err.Number
sError = Err.Description
If lError = 5180 Then
Resume
Else
Print #1, "!!!!! File " & inFile & " LOAD FAILED !!!!!!"
Print #1, "Error: " & lError & " - " & sError
GoTo PROC_EXIT
End If
End Sub

=============================

 

[cLFlaVA]

Try the following:

1. At the beginning of your code, Dim a Document object:
[tt]Dim doc as Document[/tt]

2. Whenever you open a document, set it to this variable:
[tt]Set doc = Documents.Open...[/tt]

3. When you reference the document, use [tt]doc[/tt] instead of [tt]ActiveDocument[/tt].

4. At the end, when you close the file, add a line setting the object to Nothing. This "destroys" the object:
[tt]doc.Close
Set doc = Nothing[/tt]

Hope that helps.

*cLFlaVA
----------------------------
A pirate walks into a bar with a huge ship's steering wheel down his pants.
The bartender asks, "Are you aware that you have a steering wheel down your pants?"
The pirate replies, "Arrrrr! It's driving me nuts!

 

[ChrisW75]

It doesn't seem to like the

set doc = Documents.Open fileName:=inFile, ConfirmConversions:= _
False, ReadOnly:=True, AddToRecentFiles:=False, PasswordDocument:=passwd _
, PasswordTemplate:="", Revert:=False, WritePasswordDocument:=passwd, _
WritePasswordTemplate:="", Format:=wdOpenFormatAuto

statement. Says it's expecting the End of Statement instead of fileName.... I think I've got my syntax wrong here. I've tried a few variations, and not managed to get it working yet, any ideas? I tried using New in the Dim statement, but that didn't work either.
Cheers.

 

[fumei]

Setting an object reference for an opening document requires the filename as an argument.

Set aDoc = Documents.Open(FileName:="c:\testme.doc")

is different than:

Documents.Open FileName:="c:\testme.doc"

Setting as an object requires the parenthesis (as an argument).


Gerry

 

[ChrisW75]

Hi cLFlaVA and fumei,
Thanks for your help, I've got it running now using Flavas method, unfortunately it still doesn't free up the memory.
I'm going to try to convert the code to run in VB so that it's separate from the Word Application, then I'll just close the application down once every 1000 documents to clear the memory. It's a crap way of doing it, but nothing else seems to work.

I even contacted Micro$oft, and their response was as follows:

=======================
Dear Chris,

Thank you for contacting Microsoft Online Assisted Support.

A preliminary investigation of your issue has determined that it is related to a BackOffice/Developer product. Presently, Online Support does not handle such cases but is looking to do so in the future. This information is available in the below Microsoft Agreement:

=======================

So no help there.

Thanks anyway.

 

[ChrisW75]

I got a reply on the Microsoft community forums which may help anyone else with this problem:-

"Try inserting an Undo.Clear into your code."

Perhaps the undo buffer is what is filling up. Since I started this thread there's beena re-scoping so I'm not going to be saving the data as RTF anymore, however I'm still using my code to extract info form the documents (Creation Date and Title). I've found that running this code without the Save doesn't hog memory (which ties in with the undo buffer theory). So, I've not tried this method, but I thought I'd post it here for anyone else who get this issue.

Enjoy.