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!


Welcome to the new platform of Programmer's Heaven! We apologize for the inconvenience caused, if you visited us from a broken link of the previous version. The main reason to move to a new platform is to provide more effective and collaborative experience to you all. Please feel free to experience the new platform and use its exciting features. Contact us for any issue that you need to get clarified. We are more than happy to help you.

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.