Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Word by Remote (Excel 2k Word 2k)

    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 <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    Sub TEST_HTM_CONVERSION()
    Documents.Open FileName:="CataRUG OEETotal Complaint Report.rtf", _
    ConfirmConversions:=False, ReadOnly:=False, AddToRecentFiles:=False, _
    PasswordDocument:="", PasswordTemplate:="", Revert:=False, _
    WritePasswordDocument:="", WritePasswordTemplate:="", Format:= _
    wdOpenFormatAuto
    '
    ActiveDocument.SaveAs FileName:="CataRUG 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 <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23>

    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:="CataRUG OEETotal Complaint Report.rtf", _
    ConfirmConversions:=False, ReadOnly:=False, AddToRecentFiles:=False, _
    PasswordDocument:="", PasswordTemplate:="", Revert:=False, _
    WritePasswordDocument:="", WritePasswordTemplate:="", Format:= _
    wdOpenFormatAuto
    '
    .ActiveDocument.SaveAs Filename:="CataRUG 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

  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Word by Remote (Excel 2k Word 2k)

    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!).

  3. #3
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Word by Remote (Excel 2k Word 2k)

    Many thanks <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    This has done the trick and I can get on with the next bit of the puzzle now <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    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

  4. #4
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Word by Remote (Excel 2k Word 2k)

    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. <img src=/S/wink.gif border=0 alt=wink width=15 height=15>

  5. #5
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Word by Remote (Excel 2k Word 2k)

    Thanks again. will just go tidy up now <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •