I have an Excel spreadsheet that contains my data. I have a MS Word form set up to merge with the Excel data.
After the merge completes, I want the code below to run. The step are:
1. Merge from Word with the Excel database (still manual).
2. Split merged document into separate files (code in MS Word VBA)
3. Rename or name the separate files using the data in column G of the spreadsheet (still need help with code here).
I have found the code below to break and save a mail merged document into individual documents.
What I need help with is writing code to:
1. Create a file directory using today's date
2. Rename/name the individual document using the cell value of a concatenate field in Excel (column G)
3. Final, save the documents in the newly created directory. Here what I have:
Sub BreakOnPage()
' Used to set criteria for moving through the document by page.
Application.Browser.Target = wdBrowsePage
For i = 1 To ActiveDocument.BuiltInDocumentProperties("Num ber of Pages")
'Select and copy the text to the clipboard.
ActiveDocument.Bookmarks("\page").Range. Copy
' Open new document to paste the content of the clipboard into.
Documents.Add
Selection.Paste
' Removes the break that is copied at the end of the page, if any.
Selection.TypeBackspace
ChangeFileOpenDirectory "M:\WPDOC\Education\"
DocNum = DocNum + 1
ActiveDocument.SaveAs FileName:="TEST" & DocNum & ".doc"
ActiveDocument.Close
' Move the selection to the next page in the document.
Application.Browser.Next
Next i
ActiveDocument.Close savechanges:=wdDoNotSaveChanges
With docWord
.Activate
.SaveAs FileName:="M:\WPDOC\Education\" & Sheets("Students").Range("G2") .Value & "\" & _
Sheets("Students").Range("G2") .Value & "_" & Sheets("Students").Range("G2") .Value & ".doc"
End Sub
Any assistance will be great.
