1
I am working on an excel file that opens a source document(word) that I want to save to another name but with the macro's from the source.
Opening the file is not the problem but after this merge (I am using mailmerge) and changing the source document name the macro's are gone (even before saving to .docm). In the source document the macro is available after merging though.
any idea's how to fix this?
The code I use is this:
Dim wd As Object
Dim wdocSource As Object
Dim strWorkbookName As String
Dim DocSrc As String
Dim number As String
number = CStr(ActiveWorkbook.Sheets("MailMerge").Cells(2, 5))
Const wdFormLetters = 0, wdOpenFormatAuto = 0
Const wdSendToNewDocument = 0, wdDefaultFirstRecord = 1, wdDefaultLastRecord = 1
'If Len(Dir("<directory>" & number, vbDirectory)) = 0 Then MkDir "<directory>" & number
fileSaveName = "X:\SAVENAME.docm"
On Error Resume Next
Set wd = GetObject(, "Word.Application")
If wd Is Nothing Then
Set wd = CreateObject("Word.Application")
End If
On Error GoTo 0
Set wdocSource = wd.Documents.Open("<directroy>\Test.docm")
strWorkbookName = ThisWorkbook.Path & "\" & ThisWorkbook.Name
wdocSource.MailMerge.MainDocumentType = wdFormLetters
wdocSource.MailMerge.OpenDataSource _
Name:=strWorkbookName, _
AddToRecentFiles:=False, _
Revert:=False, _
Format:=wdOpenFormatAuto, _
Connection:="Data Source=" & strWorkbookName & ";Mode=Read", _
SQLStatement:="SELECT * FROM `MailMerge$` "
With wdocSource.MailMerge
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=False
End With
wd.Visible = True
' wdocSource.Close SaveChanges:=False
'wd.ActiveDocument.SaveAs fileSaveName
'wd.ActiveDocument.Close
Set wdocSource = Nothing
Set wd = Nothing
What is
docm
? Assuming the MS Word file format, why is this about Excel? – Dave – 2016-07-14T09:54:45.827docm as in word file format :) and I am merging from excel to word – merger – 2016-07-14T09:56:12.520
Thanks for explanation. Can you edit your question to explain that, it will help avoid confusion for others :)
– Dave – 2016-07-14T09:56:57.6801"and changing the source document name the macro's are gone (even before saving to .docm)" - how can you change it's name without saving it? Why don't you change name and save as .docm in one step? I'm afraid we can't really help you without seeing relevant parts of your code. – Máté Juhász – 2016-07-14T10:22:42.110
It seems that the newly created document is a .docx, which can't contain macros. Your only option is to improve your macro to copy the code from the original document to the new one. – Máté Juhász – 2016-07-14T10:47:47.353
any suggestions on how to do so? – merger – 2016-07-14T10:53:30.130
Is it possible to open the newly created document as .docm? – merger – 2016-07-14T14:48:37.840
@MátéJuhász a docx file (or xlsx file) can contain a macro, you can even save it and the macros will stay. But as soon as you close the document without resaving as a domc (or xlsm) then you will lose the macros. So it shouldn't matter if the file is created as a docx – gtwebb – 2016-07-14T17:55:38.613
The source file is a .dcom the new file it opens is apparently a docx without any macro's that I can save as a .docm but without the macro's from the source file it is useless to me. – merger – 2016-07-14T19:04:28.557