Word by Remote (Excel 2k Word 2k)

Topic

New Reply

I have been trying to use word to convert a RTF file to a HTM file.

Using the code below which worked fine in Word but when run from Excel generates an “Command Failed” error on the Open Document statement. does anyone have any idea of where I am going wrong??

in Word this works
Sub TEST_HTM_CONVERSION()
Documents.Open FileName:=”C:DataRUG OEETotal Complaint Report.rtf”, _
ConfirmConversions:=False, ReadOnly:=False, AddToRecentFiles:=False, _
PasswordDocument:=””, PasswordTemplate:=””, Revert:=False, _
WritePasswordDocument:=””, WritePasswordTemplate:=””, Format:= _
wdOpenFormatAuto
‘
ActiveDocument.SaveAs FileName:=”C:DataRUG OEETotal Complaint Report.htm”, FileFormat:= _
wdFormatHTML, LockComments:=False, Password:=””, AddToRecentFiles:=True, _
WritePassword:=””, ReadOnlyRecommended:=False, EmbedTrueTypeFonts:=False, _
SaveNativePictureFormat:=False, SaveFormsData:=False, SaveAsAOCELetter:= _
False
ActiveWindow.View.Type = wdWebView
End Sub

in Excel this does not

Private Sub CommandButton1_Click()
Dim WordApp As Object ‘ Declare variable to hold the reference.
Set WordApp = CreateObject(“word.application”)
With WordApp
.Visible = True
.Documents.Open Filename:=”C:DataRUG OEETotal Complaint Report.rtf”, _
ConfirmConversions:=False, ReadOnly:=False, AddToRecentFiles:=False, _
PasswordDocument:=””, PasswordTemplate:=””, Revert:=False, _
WritePasswordDocument:=””, WritePasswordTemplate:=””, Format:= _
wdOpenFormatAuto
‘
.ActiveDocument.SaveAs Filename:=”C:DataRUG OEETotal Complaint Report.htm”, FileFormat:= _
wdFormatHTML, LockComments:=False, Password:=””, AddToRecentFiles:=True, _
WritePassword:=””, ReadOnlyRecommended:=False, EmbedTrueTypeFonts:=False, _
SaveNativePictureFormat:=False, SaveFormsData:=False, SaveAsAOCELetter:= _
False
.ActiveWindow.View.Type = wdWebView

End With
Set WordApp = Nothing ‘ clear the reference.
End Sub

Many Thanks

Peter

Reply | Quote

Replies

In your code editing window, pop open the Immediate window (ctrl-G) and type:

?wdOpenFormatAuto [then press Enter]

If you do not get an integer, then Excel cannot decode the constant and you need to add a Reference to Word in Tools, References (something like “Microsoft Word 9.0 Object Library”). Once you do that, you can replace these two lines:

Dim WordApp As Object ‘ Declare variable to hold the reference.
Set WordApp = CreateObject(“word.application”)

With this:

Dim WordApp As New Word.Application

And your intellisense prompts will work in the Excel VB Editor (always a plus!).

Reply | Quote

Many thanks

This has done the trick and I can get on with the next bit of the puzzle now

on quick check though, I take it that I no longer need the
Set WordApp = Nothing ‘ clear the reference.
statement any more?

Thanks again
Peter

Reply | Quote

Good question. If you are leaving Word open for the user, then the user probably can take care of it. Otherwise, you normally would do both WordApp.Quit to exit the application and Set WordApp = Nothing. VBA is supposed to clean up its object references, but it is considered good practice to do it expressly. Practice for working in VB, I guess.

Reply | Quote

Thanks again. will just go tidy up now

Reply | Quote