none
Access2003 Report to Word + SAVE AS Prompt

    Question

  • hi, I have this line in access vba code.... I am sending a report to Word having word open automatically.

    DoCmd.OutputTo acReport, "MyReport_OutPutDoc", "RichTextFormat(*.rtf)", "Report.doc", True, "", 0

    it prompts WORD to open

    then I have these lines

    Dim bjWordApp as object 

    Set objWordApp = GetObject(, "Word.Application")

    objWordApp.Application.FileDialog(msoFileDialogSaveAs).Show

    .... the user now has the FILE SAVE dialog and not the FILE SAVE AS dialog....

    even though the msoDileDialofSaveAs is indicated.

    if I enter another name in the dialog ( not Report.doc ) as stated in the "Docmd.*"  line  - and hit save in the dialog... nothing happens...

    how can I prompt from access VBA the WORD dialog FILE SAVE AS and it actually works ??

    thanks


    Thursday, May 24, 2012 4:52 PM

All replies

  • well no one has given you a reply so here is a non authoritative view....

    your docmd appears to work fine.  and that is stand alone.  which is to say if the other lines of code aren't there your export works fine.

    the other code talking to Word I don't see any reference to a specific document...  on the otherhand I'm not sure why you are needing it either - but maybe you have your reasons.

    Sorry not to be more helpful.

    Thursday, May 24, 2012 10:06 PM
  • Hi Keith,

    I suggest that after opening Word you need to attach to the document then invoke the "File Save As" dialog.

        Dim objWordApp As Word.Application
        Dim objWordDoc As Word.Document
        
        Set objWordApp = GetObject(, "Word.Application")
        Set objWordDoc = objWordApp.Documents("Report.doc")
        objWordDoc.SaveAs
    

    • Proposed as answer by Ray Brack Tuesday, May 29, 2012 10:15 PM
    Friday, May 25, 2012 2:25 AM
  • Don't forget about Mail Merge:

    http://www.ssw.com.au/ssw/Standards/DeveloperAccess/HowToDoMailMergeinWordusingAccessdata.aspx

    http://office.microsoft.com/en-us/access-help/use-a-table-or-query-as-a-mail-merge-data-source-HA001233657.aspx

    http://office.microsoft.com/en-us/access-help/demo-merge-access-data-with-word-HA010285219.aspx

    There are many ways to automatically move data from MS Access and Word, or from Word to Access.  Nevertheless, the non-coding options are almost always easier.  If those aren't powerful enough for you, of course you can always develop your own custom solution.


    Ryan Shuell

    Friday, May 25, 2012 12:51 PM
  • ok, here is what I came up with so far... it works but I now need to figure out how to capture the filename entered by the user and when save is hit, advise the user to choose a different filename only IF the filename is the same as my default filename that I use in my docmd line... which is (DuplicateAccessReportToWord_TempReport.doc) - Any suggestions on that would be appreciated... thanks

    This all occurs as the user closes the original access report... they are asked to save in WORD... keep in mind that the requirement is that the user has to be able to choose own directory and filename.

    Private Sub Report_Close()

    If MsgBox("Do you want to Save the Report as Microsoft Word Format?", vbYesNo, "SAVE REPORT") = vbYes Then

                Dim objWordApp As Object

            Dim lngWordDocCount As Long

                Dim strWordDocName As String

                Dim strDocPath As String

                strWordDocName = "DuplicateAccessReportToWord_TempReport.doc"

                Set objWordApp = GetObject(, "Word.Application")

                DoCmd.OutputTo acReport, "DuplicateAccessReport", "RichTextFormat(*.rtf)", "DuplicateAccessReportToWord_TempReport.doc", True, "", 0                       

                strDocPath = objWordApp.ActiveDocument.Path & "\" & strWordDocName

               With objWordApp           

                    .Activate               

    ‘the user hits cancel, we close the document

                    If .FileDialog(msoFileDialogSaveAs).Show = 0 Then

                     objWordApp.ActiveDocument.Close

    elseif NEED CODE HERE TO CHECK FILENAME TYPED BEFORE EXECUTE  Then

                       ‘the user types a different name and it is saved

                    Else: .FileDialog(msoFileDialogSaveAs).Execute            

                    End If               

                End With

                'we delete the DuplicateAccessReportToWord_TempReport.doc               

                    If Dir(strDocPath, vbDirectory) <> "" Then Kill strDocPath

    End If

    End Sub






    Friday, May 25, 2012 4:42 PM
  • Hi Keith,

    The only way you're going to get this information is to subclass the Word dialog window and even then I'm not sure if you could determine what had been entered (it's been a while since I had to do any low-level API calls) without a lot of fiddly code to hook the calls.

    I recommend that you trust your users to enter a new file name each time, or at least the second time when Word warns the file already exists if they try to use the same name.  Is there a reason they must change the file name?

    Monday, May 28, 2012 12:57 AM
  • hi Ray, thanks for the reply... the reason we need the user to enter a different name now is simply/just to avoid the crash... as I have no way around it right now.

    cheers.

    Monday, May 28, 2012 1:10 PM
  • If it is essential they don't overwrite a previous file then consider exporting the file with a unique file name each time.  For example;

    DoCmd.OutputTo acReport, "MyReport_OutPutDoc", "RichTextFormat(*.rtf)", "Report_" & Format(Now(), "yyyy-mm-dd_hhmm") & ".doc", True, "", 0


    • Edited by Ray Brack Monday, May 28, 2012 10:30 PM Code correction
    Monday, May 28, 2012 10:29 PM
  • hi Ray, but they can still pick that filename as it is seen from the dialog box... as the dialog shows all the files in the directory and the default access report output name is at the top of the list....

    what I have done, however is provide a "sample" unique name (similar to what you provided) in the file name filter within the dialog box... 

    so basically if the user doesnt like it, they can still change it (meeting the requirements) but by doing it this way, it should entice the user to just save it or rename it manually then hit save... the risk is still there but less in a way...sort of.

    the risk now is they lose the data and simply have to rerun the report should they still pick that default name.....I put an "on error resume next" on it !!. too bad so sad !! lol

    cheers !


    Tuesday, May 29, 2012 4:11 PM