mbbx5va2
07-24-2014, 01:07 PM
So I have a working mail merge procedure but it produces PDFs. Now I'm trying to save the mail merged document as a word document. The beginning of the procedure is as follows:
Dim xls As Excel.Application
Set xls = New Excel.Application
Dim wrdApp As Word.Application
Set wrdApp = New Word.Application
Dim sPathFileTemplate As String
sPathFileTemplate = ThisWorkbook.Path & "\OFF template macro.docx"
'Setting up template
Dim doc As Word.Document
Set doc = wrdApp.Documents.Add(sPathFileTemplate)
wrdApp.Visible = False ' Make MS Word Invisible
Dim sIn As String
sIn = ThisWorkbook.FullName
After this in VBA I've written the code for the SQL, Opened the merge and then done the merge. Now all I want to do is save a copy in word with the save as box appearing so that I can insert a file name. I've had a go and come up with the following:
wrdApp.Visible = True
doc.Save
wrdApp.Documents.Add doc.FullName
doc.SaveAs "C:\Documents\" & "test1.docx"
doc.Close False
Set doc = Nothing
wrdApp.Quit False
Set wrdApp = Nothing
MsgBox "Done"
End Sub
Alternatively I am thinking some variation of:
SaveAsName = Application.DefaultFilePath & "\" & "docx"
With doc
.Activedocument.SaveAs Filename:=SaveAsName
End With
would work.
Any ideas with this would be much appreciated. :think:
Thanks
Dim xls As Excel.Application
Set xls = New Excel.Application
Dim wrdApp As Word.Application
Set wrdApp = New Word.Application
Dim sPathFileTemplate As String
sPathFileTemplate = ThisWorkbook.Path & "\OFF template macro.docx"
'Setting up template
Dim doc As Word.Document
Set doc = wrdApp.Documents.Add(sPathFileTemplate)
wrdApp.Visible = False ' Make MS Word Invisible
Dim sIn As String
sIn = ThisWorkbook.FullName
After this in VBA I've written the code for the SQL, Opened the merge and then done the merge. Now all I want to do is save a copy in word with the save as box appearing so that I can insert a file name. I've had a go and come up with the following:
wrdApp.Visible = True
doc.Save
wrdApp.Documents.Add doc.FullName
doc.SaveAs "C:\Documents\" & "test1.docx"
doc.Close False
Set doc = Nothing
wrdApp.Quit False
Set wrdApp = Nothing
MsgBox "Done"
End Sub
Alternatively I am thinking some variation of:
SaveAsName = Application.DefaultFilePath & "\" & "docx"
With doc
.Activedocument.SaveAs Filename:=SaveAsName
End With
would work.
Any ideas with this would be much appreciated. :think:
Thanks