Exporting from Access to Word - SaveAs 1

[rj51cxa]

Using FAQ 705-3237 I have compiled the following code to export data from an Access Table to a Word document. The code works fine but I have been trying to modify it to bring up the Save As dialog box so that the user, who is not very experienced, is promted to save the form letter correctly. Unforunately, the lines of code that I have added bring up the Save dialog and not the Save As dialog.

 Private Sub OpenMergedDoc(strDocName As String, StrSQL As String, strMergedDocName As String)
On Error GoTo WordError
   Dim objWord As New Word.Application
   **Dim dlgSaveAs As FileDialog**
   objWord.Application.Visible = True
   Set objDoc = objWord.Documents.Open(strDocName)
   objWord.Application.Visible = True
   **Set dlgSaveAs = objWord.Application.FileDialog(msoFileDialogSaveAs)**
  
  objDoc.MailMerge.OpenDataSource _
        Name:="F:\A&S.mdb", _
        LinkToSource:=True, AddToRecentFiles:=False, _
        Connection:="QUERY QryAddresses", _
        SQLStatement:="SELECT * FROM [QryAddresses]"

        objDoc.MailMerge.Destination = wdSendToNewDocument
        objDoc.MailMerge.Execute
        objWord.Application.Documents(1).SaveAs (strMergedDocName & ".doc")
        objWord.Application.Documents(2).Close wdDoNotSaveChanges
        DoCmd.Close acForm, "FrmDataEntry", acSaveYes
        ***dlgSaveAs.Show***

    Set objWord = Nothing
    Set objDoc = Nothing
       
Exit Sub
WordError:
        MsgBox "Err #" & Err.Number & "  occurred." & Err.Description, vbOKOnly, "Word Error"
        objWord.Quit

End Sub

Private Sub CmdMergIT_Click()
On Error GoTo ErrorHandler

Dim rs As Object

Set rs = Me.RecordsetClone
rs.FindFirst "Record = " & Me![Record]
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

Dim strRecord As String
strRecord = [Record]
Dim StrSQL As String
StrSQL = "SELECT TblAddresses.StrCompany, TblAddresses.StrSalutation, TblAddresses.StrInitials , TblAddresses.StrSurname, TblAddresses.StrAddress1, TblAddresses.StrAddress2, TblAddresses.StrAddress3, TblAddresses.StrCity, TblAddresses.StrCounty, TblAddresses.StrPostCode, TblAddresses.Record FROM TblAddresses WHERE TblAddresses.Record = " & Me.Record

Dim strDocumentName As String
strDocumentName = "F:\MD1.doc"

Call SetQuery("QryAddresses", StrSQL)
Dim strNewName As String
strNewName = "Letter1"
Call OpenMergedDoc(strDocumentName, StrSQL, strNewName)
Exit Sub
ErrorHandler:
    MsgBox "Error #" & Err.Number & " occurred. " & Err.Description, vbOKOnly, "Error"
    Exit Sub
End Sub

I have shown the modified lines of code with *** at each end. They are all in the OpenMergedDoc sub.

I am obviously using incorrect code and would be grateful if someone could correct me.

Thanks very much
John

 

[Remou]

This may help:
Problem manipulating File SaveAs dialogue box via VBA in word 2000
thread707-1185834

 

[rj51cxa]

Thanks Remou, that was all I needed. It works fine now.

Best Regards
John

 

[rj51cxa]

Hi Remou,

I think I spoke too soon. I removed the lines of code that I had anotated with **** and added the code:

Dialogs(wdDialogFileSaveAs).Show

in place of ***dlgSaveAs.Show***.

I then ran the code, the SaveAs dialog box appeared and I was able to re-name the file and save it. Great, I thought, that's done it, and sent off the reply.

I tried again but this time I received an error message as follows:

"Error #462 occurred. The remote server machine does not exist or is unavailable."

I have tried various combinations of rmikesmith's FAQ but I cannot resolve the problem.

Any ideas would be much appreciated.

Best Regards
John

 

[Remou]

Did you add objWord to the beginning of the "Dialog ..." line?

 

[rj51cxa]

Thanks Remou, that was all it needed. Have another star.

Best Regards
John