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")
.... 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 ??
- Edited by KeithSheppard Thursday, May 24, 2012 4:53 PM
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.
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
Don't forget about Mail Merge:
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.
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
‘the user hits cancel, we close the document
If .FileDialog(msoFileDialogSaveAs).Show = 0 Then
elseif NEED CODE HERE TO CHECK FILENAME TYPED BEFORE EXECUTE Then
‘the user types a different name and it is saved
'we delete the DuplicateAccessReportToWord_TempReport.doc
If Dir(strDocPath, vbDirectory) <> "" Then Kill strDocPath
- Edited by KeithSheppard Friday, May 25, 2012 4:50 PM
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?
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
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
- Edited by KeithSheppard Tuesday, May 29, 2012 4:12 PM