Rename and Save File using VBA - Programmers Heaven

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!


Rename and Save File using VBA

PJones1PJones1 Posts: 1Member
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.
' Removes the break that is copied at the end of the page, if any.
ChangeFileOpenDirectory "M:WPDOCEducation"
DocNum = DocNum + 1
ActiveDocument.SaveAs FileName:="TEST" & DocNum & ".doc"

' Move the selection to the next page in the document.
Next i
ActiveDocument.Close savechanges:=wdDoNotSaveChanges

With docWord

.SaveAs FileName:="M:WPDOCEducation" & Sheets("Students").Range("G2") .Value & "" & _
Sheets("Students").Range("G2") .Value & "_" & Sheets("Students").Range("G2") .Value & ".doc"

End Sub

Any assistance will be great.


Sign In or Register to comment.