Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    controlling Word from XL VBA (Ver 97/2K)

    I have almost finished a small application intended to let excel masquerade as a database. My last task is to arrange to extract (certain fields of) particular data records and place them in a file where they are useful to Word for a mail merge.

    I am almost there: I can identify the fields / records I want to extract; I can save them to a new Excel file; I can fire up word from within my Excel VBA routine and import the Excel file into word - but I can't get Word to save the file.

    The attached routine works to a point (the variable Pth is declared as a private for the whole module, and stores the path to the temporary XL file that holds the extracted records). My comments do not appear in the actual VBA code, of course...

    <pre>Sub ExportAlltoWord()
    Dim NewName As String
    Dim WordApp As Object
    Set WordApp = CreateObject("word.application")
    ExportAll
    </pre>

    the "ExportAll" routine does the extraction work, and creates the temporary XL file...
    <pre>NewName = Application.GetSaveAsFilename _
    (filefilter:="Word Document File (*.doc),*.doc", _
    Title:="New Name for Extracted Data File")
    </pre>

    there is no 'GetSaveAsFilename method' in Word, so I had to use XL!
    <pre>With WordApp
    Application.DisplayAlerts = 0
    .documents.Open Filename:=Pth, _
    ConfirmConversions:=False, _
    ReadOnly:=False, _
    AddToRecentFiles:=False, _
    PasswordDocument:="", _
    PasswordTemplate:="", _
    Revert:=False, _
    WritePasswordDocument:="", _
    WritePasswordTemplate:="", _
    Format:="wdOpenFormatAuto"
    </pre>

    Things seem to work up to here - it is the following line(s) where I try to save the file that cause problems
    <pre>' .activedocument.Name = NewName
    </pre>

    when this 'remmed-out' line is included, I generate an error, since "Name" is read only in Word VBA
    <pre> .activedocument.SaveAs Filename:=NewName
    </pre>

    when this line is included I get a file-protection error (or file protection conflict - I can't remember, and am getting slightly blurry-eyed)
    <pre>' .activedocument.SaveAs FileName:=NewName, _
    FileFormat:="wdFormatDocument", _
    LockComments:=False, _
    Password:="", _
    AddToRecentFiles:=True, _
    WritePassword:="", _
    ReadOnlyRecommended:=False, _
    EmbedTrueTypeFonts:=False, _
    SaveNativePictureFormat:=False, _
    SaveFormsData:=False, _
    SaveAsAOCELetter:=False
    </pre>

    this is all one (remmed-out) statement - and when it is active, I get a type mismatch error. This surprises me, since it is a direct copy out of the Word macro recorder, and should be functionally equivalent to the statement directly above it (which generates a different error...
    <pre>End With

    WordApp.Quit
    Set WordApp = Nothing

    End Sub
    </pre>


    Any suggestions will be greatly appreciated, if anyone has any experience in using Word as a remote application from Excel. One of the difficulties is that Word runs invisibly when the Excel VBA is controlling it, so it is difficult to see what is going on...

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: controlling Word from XL VBA (Ver 97/2K)

    Hi Dean,
    If you use WordApp.Visible = True you should be able to see what Word is doing. Also, in your last piece of code you shouldn't have quotes around wdFormatDocument. Finally, you can do a mail-merge in Word using data from an Excel spreadsheet, so I'm not sure you really need to import the data into Word anyway at this point.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: controlling Word from XL VBA (Ver 97/2K)

    Thanks, Rory.

    When I had the 'wdFormatDocument' constant without quotes I got an error as well - I think that I have to supply the numeric equivalent of the constant, not the literal, because I am using late binding of the word application. That will require a visit to Word's 'immediate' pane - but might be my solution

    I would prefer to generate a 'word' document, because the people who are going to have to prepare mail merges are not experienced excel users, and I would like to hide as much from them as I can. I didn't know I could set WordApp.Visible, so if nothing else I have learned one thing today.

    I'll let you know how it goes this morning.

  4. #4
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: controlling Word from XL VBA (Ver 97/2K)

    For all those who were on the edge of their seats...

    It was the literal 'wdFormatDocument' that was giving me trouble in the second-last (or so) line of code, but it was the WordApp.Visible = True suggestion that really helped sort things out.

    On the file import Word insists on popping up a dialog box (titled "Open Worksheet" - there was query about this recently, since it doesn't seem to do anything but act as a gatekeeper) to confirm the action - even though ApplicationDisplayAlerts is set to "0" (the numeric equivalent of "wdAlertsNone").

    The work-around is to activate Word just before it imports the file so that it has the focus. That makes the dialog visible to the user, they can click on it, and then word disappears and they are back in their excel "database" environment.

Posting Permissions

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