macro's gone before/after saving to .docm

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

merger

Posted 2016-07-14T09:51:21.667

Reputation: 11

What is docm? Assuming the MS Word file format, why is this about Excel? – Dave – 2016-07-14T09:54:45.827

docm 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.680

1"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

No answers