locked
Save As Dialog box comes up instead of using ActiveDocument.SaveAs RRS feed

  • Question

  • I'm not sure if this question should be in a Word or Access forum, but here goes:

    I am using Access to automate a Word mailmerge.  My code worked in Access/Word 2003.  Now we have upgraded to Access 2010 but users could have Word 2003,2007,2010.  The SaveAs Dialog box comes up even though I have ActiveDocument.SaveAs in the code.  Any help would be very appreciated because, as you will see, I am not knowledgeable about Word!  Thank you!

    Private Sub CallMergeToWord(strcltnum As String) 'bring plan num over from Loop procedure
    On Error GoTo Err_CallMergeToWord

    Dim strSQL As String
    Dim strDirDoc As String    'merged docs directory
    Dim strDirDot As String    '.dot template directory
    Dim strLtrDot As String    'The .dot template
    Dim strLtrDoc As String    'The merged word doc
    Const wdSendToNewDocument = 0
    Const wdDoNotSaveChanges = 0
    Const wdFormatDocument = 0

    strDirDot = "P:\.......\"
    strLtrDot = "FeeLetterTEST.dot"

    strDirDoc = ""P:\.......\"

    strLtrDoc = strcltnum & "_FeeLetter.doc"

    'Declarations
    'my pointer to Word
    Dim obWord As Object
    'my pointer to the Document object
    Dim doc As Object

    'First I try to use their opened Word application, but if it isn't already opened I handle the error in line
    On Error Resume Next
    Set obWord = GetObject(, "Word.Application")
    If Err.Number = 429 Then
        'If got this error it means that there was not an instance of Word open already so...
        Set obWord = CreateObject("Word.Application")
    End If
    obWord.Visible = True 'goes after set obword and before opening template
    'reset error handler
    On Error GoTo Err_CallMergeToWord

    Set doc = obWord.Documents.Add(strDirDot & strLtrDot)  'part of the DocumentS collection. If this runs successfully then Add returns a reference to the template.
                                                           'The DocumentS collection is to create and open.  .open for Word docs and .add for Word templates
                                                           'The DocumenT collection is to save and close

    'open merge datasource    

     doc.MailMerge.OpenDataSource _
     Name:="..................\testcustom2010.accdb", _
          LinkToSource:=True, _
          SQLStatement:="SELECT * from [FEELETTER_May2012] where cltnum = '" & strcltnum & "'"
         
    'execute mailmerge
      doc.MailMerge.Destination = wdSendToNewDocument
      doc.MailMerge.Execute

     doc.close wdDoNotSaveChanges 'doc is a reference to the template so this closes it.
      Debug.Print obWord.ActiveDocument

    '''''''''''''''''''''When I step into the line below I get a 4198 command failed error''''''''''''''''''''''''''''''''''''

     obWord.ActiveDocument.SaveAs FileName:=strDirDoc & strLtrDoc, FileFormat:=wdFormatDocument
     obWord.ActiveDocument.close  'close the newly created doc.

    Exit_CallMergeToWord:
    Set doc = Nothing
    'objWord.Quit 'To close the instance of Word. There is no Close method for the Word object. You have to use Quit instead.
                  'Don't want to close Word for this procedure since we are looping
    Set obWord = Nothing
        Exit Sub

    Err_CallMergeToWord:
       MsgBox Err.Number & Err.Description
        Resume Exit_CallMergeToWord
    End Sub

    Friday, June 29, 2012 10:32 PM

Answers

  • HI Elisabeth

    If you want to test late binding then you may NOT use named arguments. It would have to be something like

      obWord.ActiveDocument.SaveAs strDirDoc & strLtrDoc

    Looking back at your original code, I notice that you're specifying the *.doc extension, IOW the old file format. There's a WdSaveFormat that specifically saves to the Word97 document format. You might try that.

    The wdFormatDocumentDefault could be failing because it will pick up what's set in the File/Options as the default. And installation default would be *.docx, the new file format. That's not compatible with the *.doc file extension.


    Cindy Meister, VSTO/Word MVP

    • Marked as answer by Leo_Gao Friday, July 20, 2012 1:18 AM
    Thursday, July 12, 2012 3:04 PM
  • Hi Elisabeth

    Well, it certainly can be done. I do it regularly.

    By "it does not work" you mean the Save As dialog box is still appearing?

    Have you tried creating a folder on the local machine such as C:\Test and saving directly to that? Please show me the exact hard-coded path, including the name of the document, to such a folder. (I can't do anything with "\\server2\masters\....")

    Exactly which version of Office is on this test machine?

    Where is the Normal.dotm template located that's used when Documents.Add is called? (And is it Normal.dotm or Normal.dot)?

    Is there any chance this Normal.dotm contains macros? Or could there be other add-ins that may be forcing the display of the dialog box? Try holding down CTRL when starting Word as an end-user - this will start it in default installation mode and no load any macros or add-ins. Does running your code while Word is in this mode give a different result?


    Cindy Meister, VSTO/Word MVP

    • Proposed as answer by Leo_Gao Wednesday, July 18, 2012 1:05 AM
    • Unproposed as answer by Leo_Gao Wednesday, July 18, 2012 1:06 AM
    • Marked as answer by Cindy Meister MVP Sunday, July 22, 2012 7:04 AM
    Monday, July 16, 2012 8:00 AM

All replies

  • Well you have double quotes at the start of:

    strDirDoc = ""P:\.......\"

    is this a typo?  If not, then:

    obWord.ActiveDocument.SaveAs FileName:=strDirDoc & strLtrDoc, FileFormat:=wdFormatDocument

    will fail.


    Word MVP

    Saturday, June 30, 2012 12:31 AM
  • Unfortunately, that's just a typo on this forum.  Do I need to do anything special since the template is in Word 2003 and Access is 2010?  The document being saved could be 2003, 2007 or 2010.  Thanks again for your help.

    Saturday, June 30, 2012 1:04 AM
  • Hi,

    Thanks for posting in the MSDN Forum.

    In accordance with your issue, could you please clarify if you are not wanna to display the SaveAs dialog box while you save a document in word? 

    As far as I know, Word raises the DocumentBeforeSave event on the Application object before any document is saved. Word passes the Document that is about to be saved as a parameter to  this event. It also passes by reference a bool saveAsUI parameter and a bool cancel parameter. If you set saveAsUI to true, the Save As dialog box displays for the document, conversely,if you set false, the dialog box does not come up.

    Hope this can help you.

    best regards,


    Leo_Gao [MSFT]
    MSDN Community Support | Feedback to us

    • Marked as answer by Leo_Gao Friday, July 6, 2012 2:46 AM
    • Unmarked as answer by Cindy Meister MVP Monday, July 9, 2012 9:31 AM
    Tuesday, July 3, 2012 3:15 AM
  • Thanks for your reply.  I do not want the Save As Dialog box to come up.  I want to save the document through code (activedocument.saveas).  I will read about the DocumentBeforeSave event, and if anyone has sample code using it, I would love to see it.  Thanks again.
    Saturday, July 7, 2012 2:51 AM
  • Hi Elisab(eth?)

    I'm not clear at which point in the process the SaveAs dialog box is appearing? You have a comment in your code that ActiveDocument.SaveAs (supposedly referring to the mail merge result) is returning "Command Failed". Is the prompt appearing after that, when ActiveDocument.Close executes? If that's how it's (not) working, then the prompt makes sense, as Word for some reason can't save the document.

    Assuming that, the issue is why the SaveAs method isn't working. That's also what fumei2 is guessing, which is why he asked about the file path.

    In your place, I'd run the macro to the error message 4198, where everything should be stopping. I'd then switch to the Word window and check 1) is there a document open in the Word window? 2) If yes, can I manually use File/Save As to save to the location and file name in question?

    Perhaps there's a problem with the file name you're passing? Try hard-coding a file name and see if that works.

    Perhaps there are permissions problems with the network location? Try saving to a local folder.

    Perhaps it's a problem with the FileFormat parameter - try taking that out and see what happens.


    Cindy Meister, VSTO/Word MVP

    Monday, July 9, 2012 9:42 AM
  • Thank you so much for the continued help.  Yes, the command failed is happening because it can't save the document.  If I switch over to the Word document and save manually I am able to save to the location that I desire.  In code, when I hard-code the file name, try to save to a local folder, take out or switch the file format I still am not able to save.  I have tried changing all the trust center settings and adding all the necessary locations in the Trusted locations.

    It seems that something is wrong with saving a new document.  I tried just basic code in Access to open up a blank document and save it but same thing- won't save.  If I use my above merge to word code but do NOT having the mailmerge destination go to a new word doc and then save as a .docm or a .docx it WILL save.  The only problem with that is that I need the merged document to be a stand alone document (not connected to the datasource anymore) and I need to reuse my template.  If I don't merge to a new word doc that isn't possible, correct?  How about bookmarks- do you merge to a new doc with those?

    Thanks again,

    Elizabeth

    Wednesday, July 11, 2012 6:19 AM
  • Hi Elizabeth

    Did you also try my last suggestion: removing the FileFormat parameter?

      obWord.ActiveDocument.SaveAs FileName:=strDirDoc & strLtrDoc, FileFormat:=wdFormatDocument

    I typed this as an after-thought, but I suspect it is the issue as there have been problems before with this. The file formats changed going from Word 2003 to Word 2007 (and 2010). For the moment, try putting a comment (space & apostrophe) just before the comma (,), then test again.

    If that helps - Word can save - the next thing to do is check whether the file format Word used is acceptable, or if you need a different one. If the file format is OK, just leave the parameter away. If you need a specific (different) file format, then you need to look at the WdSaveFormat enumeration.


    Cindy Meister, VSTO/Word MVP

    Wednesday, July 11, 2012 2:16 PM
  • Taking it out still does not save.  I tried different wdsaveformats and that did not help either.  For late binding it would just be the below two lines for example, correct?

    Const wdFormatDocumentDefault= 16

    and

    obWord.ActiveDocument.SaveAs FileName:=strDirDoc & strLtrDoc, FileFormat:=wdFormatDocumentDefault

    I also tried obWord.ActiveDocument.SaveAs FileName:=strDirDoc & strLtrDoc, FileFormat:=16

    Thanks again for your help.  I'm so frustrated so I really appreciate your advice!

    Wednesday, July 11, 2012 4:02 PM
  • HI Elisabeth

    If you want to test late binding then you may NOT use named arguments. It would have to be something like

      obWord.ActiveDocument.SaveAs strDirDoc & strLtrDoc

    Looking back at your original code, I notice that you're specifying the *.doc extension, IOW the old file format. There's a WdSaveFormat that specifically saves to the Word97 document format. You might try that.

    The wdFormatDocumentDefault could be failing because it will pick up what's set in the File/Options as the default. And installation default would be *.docx, the new file format. That's not compatible with the *.doc file extension.


    Cindy Meister, VSTO/Word MVP

    • Marked as answer by Leo_Gao Friday, July 20, 2012 1:18 AM
    Thursday, July 12, 2012 3:04 PM
  • This works:
    Sub JustWord()
    On Error GoTo Proc_Err

    Dim obword As Object
    Dim doc As Object

    On Error Resume Next
    Set obword = GetObject(, "Word.Application")
    If Err.Number = 429 Then
         Set obword = CreateObject("Word.Application")
    End If

    obword.Visible = True

    On Error GoTo Proc_Err

    Set doc = obword.Documents.Add 'opens a new blank document

    Proc_Exit:
    doc.Close
    obword.Quit
    Set doc = Nothing
    Set obword = Nothing

       On Error Resume Next
       Exit Sub
     
    Proc_Err:
       MsgBox Err.Description, , _
         "ERROR " & Err.Number _
         & "   ProcedureName"
      
       Resume Proc_Exit
    End Sub

    But when I add in:
    Dim strSavePath As String
    strSavePath = "\\server2\masters\...."
    doc.SaveAs strSavePath
    (or any variation of the above- i've tried them all) it does not work.

    I've had Office reinstalled.  i've tried all the extensions as you indicated above.  I am at a total loss.  Do I tell my employer it can't be done???!!!!  Thank you for your advice.

    Sunday, July 15, 2012 7:42 PM
  • Hi Elisabeth

    Well, it certainly can be done. I do it regularly.

    By "it does not work" you mean the Save As dialog box is still appearing?

    Have you tried creating a folder on the local machine such as C:\Test and saving directly to that? Please show me the exact hard-coded path, including the name of the document, to such a folder. (I can't do anything with "\\server2\masters\....")

    Exactly which version of Office is on this test machine?

    Where is the Normal.dotm template located that's used when Documents.Add is called? (And is it Normal.dotm or Normal.dot)?

    Is there any chance this Normal.dotm contains macros? Or could there be other add-ins that may be forcing the display of the dialog box? Try holding down CTRL when starting Word as an end-user - this will start it in default installation mode and no load any macros or add-ins. Does running your code while Word is in this mode give a different result?


    Cindy Meister, VSTO/Word MVP

    • Proposed as answer by Leo_Gao Wednesday, July 18, 2012 1:05 AM
    • Unproposed as answer by Leo_Gao Wednesday, July 18, 2012 1:06 AM
    • Marked as answer by Cindy Meister MVP Sunday, July 22, 2012 7:04 AM
    Monday, July 16, 2012 8:00 AM
  • Aha! It was an add-in!  Thank you so much for all your help! I really appreciate it!
    Saturday, July 21, 2012 10:33 PM
  • Hi Elizabeth

    Glad we were able to track it down - and sorry it took so long for the light-bulb to go off in my head that there could be interference from another app!


    Cindy Meister, VSTO/Word MVP

    Sunday, July 22, 2012 7:05 AM