Save copy of Word doc from Excel

[davew]

Hi
The following code saves a copy of an open Word document, then closes the copy and leaves the original document open. This is exactly what I want it to do however I want it to do this from Excel. I’m struggling to get this to work. I've fiddled with this for ages. Any help would be much appreciated.

This is the code in Word that works:

Sub SaveCopyAsWord()
'Save changes to original document
ActiveDocument.Save
'the next line copies the active document
Application.Documents.Add ActiveDocument.FullName
'the next line saves the copy to your location and name
ActiveDocument.SaveAs "C:\Documents\" & "test1.docx"
'next line closes the copy leaving you with the original document
ActiveDocument.Close
End Sub


This is my attempt to put the same code in Excel- which doesn't work:

Sub SaveCopyAsExcel()

Dim wrdApp As Object
Dim wrdDoc As Object

Set wrdApp = CreateObject("Word.Application")

Set wrdDoc = wrdApp.Documents.Open("C:\Documents\Template1.docm")
wrdDoc.Activate
wrdApp.Visible = True

'From here on in I just get errors
wrdDoc.ActiveDocument.Save
'the next line copies the active document- the ActiveDocument.FullName is important otherwise it will just create a blank document
wrdDoc.Application.Documents.Add ActiveDocument.FullName
'the next line saves the copy to your location and name
wrdDoc.ActiveDocument.SaveAs "C:\Documents\" & "test1.docx"
'next line closes the copy leaving you with the original document
wrdDoc.ActiveDocument.Close

End Sub


Thanks in advance.

[fumei]

Technically, your comments are not quite correct.Sub SaveCopyAsWord()
'Save changes to original document
ActiveDocument.Save
'the next line copies the active document
' NO, what it does is open ANOTHER instance of that document
Application.Documents.Add ActiveDocument.FullName
'the next line saves the copy to your location and name
ActiveDocument.SaveAs "C:\Documents\" & "test1.docx"
'next line closes the copy leaving you with the original document
ActiveDocument.Close
End Sub
As for the other code, you are adding things that will -yup - get errors. In particularwrdDoc.ActiveDocument.Saveis wrong. It is essentially stating:ActiveDocument.ActiveDocument.Savebecause wrdDoc IS ActiveDocument.

Try:
Sub SaveCopyAsExcel()

Dim wrdApp As Object
Dim wrdDoc As Object

Set wrdApp = CreateObject("Word.Application")

Set wrdDoc = wrdApp.Documents.Open("C:\Documents\Template1.docm")
' do not need the Activate, it will be Activate
wrdApp.Visible = True

wrdDoc.Save
'the next line copies the active document- the ActiveDocument.FullName is important otherwise it will just create a blank document
wrdApp.Documents.Add ActiveDocument.FullName
'the next line saves the copy to your location and name
wrdDoc.SaveAs "C:\Documents\" & "test1.docx"
'next line closes the copy leaving you with the original document
wrdDoc.Close
End Sub

[davew]

Thanks very much Fumei for the code and quick response.

davew

[fumei]

So it worked?

BTW: are you doing proper handling of your objects wrdDoc and wrdApp??? By that I mean are you closing wrdApp correctly and then destroying it explicitly? This is an area of much debate, and it may be handled for you correctly, but I pesonally thinbk application objects are best destroyed explicitly with = Nothing.

[davew]

Sorry Fumei I've been on holiday hence the slow response. It did work but rather than having:

wrdDoc.Application.Documents.Add ActiveDocument.FullName


I needed to use:

wrdApp.Documents.Add wrdDoc.FullName

I'm interested to hear what you've got to say RE destroying explicitly with = Nothing, but I've not come across this before so not sure what you mean. I have used Do Events in the past- is this the same thing.

Thanks

davew

[fumei]

It did work but rather than having:

wrdDoc.Application.Documents.Add ActiveDocument.FullName

I needed to use:

wrdApp.Documents.Add wrdDoc.FullName Ummmm. I did not post

wrdDoc.Application.Documents.Add ActiveDocument.FullName

Did you copy out my code, because I used:
wrdApp.Documents.Add ActiveDocument.FullName
However, you are correct, it really should be

wrdApp.Documents.Add wrdDoc.FullName

RE: destroying objects.

As stated, the destruction of objects is a long-standing debate. It is called garbage collection. When objects are declared and Set, a block of memory is assigned to them, and given a value.

When an object - and its memory - is no longer needed it is proper to get rid of the object by "releasing" (or "destroying") its assignment to a memory block, AND releasing the the memory block itself. This is NOT the same as making it a Null or empty value.

Again, for normal objects - say a table, a bookmark or some object within the object model - Word VBA is pretty tidy. It cleans up after itself.

However, I am of the opinion that Application objects (Word itself, not what is inside it), should be explicitly destroyed. Like this:Set wrdApp = Nothing
That being said there IS an proper order to closing and doing clean up.

So...you are done with the Word document (wrdDoc), and you are done with the Word application (wrdApp). The neatest way assuming you have SAVED (if applicable) the document is like this:
wrdDoc.Close ' close the document
Set wrdDoc = Nothing
' CLOSING the document does NOT explicitly release
' the memory assigned for it!
' = Nothing does explicitly release memory
wrdApp.Quit ' close down Word
Set wrdApp = Nothing
' Quitting Word does NOT explicitly release the memory assigned for it
' and that is a LOT
' = Nothing does explicitly release memory


The reason I prefer - and encouraged my students to do so - to explicitly release application memory is because Word is a multiple instance application. That means you can many instances of Word at the same time.

It is very rare that you truly need multiple instances (although there are of course true circumstances). Word is very effective at handling multiple documents with extremely complex interactions between them. In most cases one instance will do the job.

Word though (not to say anything about ahem...Windows) can be at times a little sloppy with memory. And considering how MUCH memory is assigned to handle Word itself, it just is IMHO a good practice to clean things deliberately, explicitly.

I have personally seen examples of Word instances not be cleaned up, and the poor wee machine ending up with six full and independent instances of Word.

Can you say choke? It froze solid.

Sorry for the rant...

[davew]

Great rant. Thanks Fumei. This really helps with my understanding. Thanks for taking the time to explain using both text and VBA code- I get it so that's a big achievement for both of us! :yes

You are also quite right in spotting that I made an error in my earlier post. I did use your code, unfortunately on responding I copied and used one of my code lines instead. To clarify you posted:

wrdApp.Documents.Add ActiveDocument.FullName
and I tweaked this to use

wrdApp.Documents.Add wrdDoc.FullName
Thanks again.

[fumei]

ActiveDocument would likely fail, as if you are doing this from Excel, Excel would have no idea what you are talking about.