Duplicate open word document before making changes - VBA

TG2812

Board Regular
Joined
Apr 15, 2015
Messages
180
Hi everyone!

I’m trying to finish off a sub routine but I’m facing a small issue. The code tries to open a given file (word template) through a dialog box. However I need to duplicate this Word document so that I do not lost the initial template. I would simply like to have the code applied to the duplicate and not the initial template.

Below is the code:

-----------------------------------------------------------------

Sub report()


Application.DisplayAlerts = False
Application.ScreenUpdating = False


Dim WordApp As Word.Application
Dim report As Word.Document
Dim FileToOpen As Variant


Set WordApp = CreateObject("Word.Application")
WordApp.Visible = True


FileToOpen = Application.GetOpenFilename(Title:="Please select a Word file to open", FileFilter:="Word Files (*.doc;*.docx),*.doc;*.docx")


If FileToOpen = False Then
MsgBox "No File selected!"
Exit Sub
Else
Set report = WordApp.Documents.Open(FileToOpen)
End If


'Code to duplicate the file should be placed here?????


Application.ThisWorkbook.Sheets(2).ChartObjects("Chart 1").Copy
report.Bookmarks("Chart1").Range.Paste
Application.ThisWorkbook.Sheets(2).ChartObjects("Chart 2").Copy
report.Bookmarks("Chart2").Range.Paste
Application.ThisWorkbook.Sheets(2).ChartObjects("Chart 8").Copy
report.Bookmarks("Chart3").Range.Paste
Application.ThisWorkbook.Sheets(2).ChartObjects("Chart 11").Copy
report.Bookmarks("Chart4").Range.Paste
Application.ThisWorkbook.Sheets(2).ChartObjects("Chart 9").Copy
report.Bookmarks("Chart5").Range.Paste
Application.ThisWorkbook.Sheets(2).Range("Table1[#All]").Copy
report.Bookmarks("Table1").Range.PasteSpecial xlPasteValues
report.Tables(1).AutoFitBehavior wdAutoFitWindow
report.TablesOfContents(1).update






Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub


----------------------------------------------------------------------------------------------------------------

Any thoughts?
Thanks in advance
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
To duplicate a document use SaveAs with a different path/name.

I have tried this but it Excel indicates an error of name or path. The path is the same but I gave it a diferent name....

With report
.SaveAs FileFormat:=wdFormatTemplate, Filename:=ActiveWorkbook.Path & "\MP Report " & Date
End With
 
Upvote 0
Basically, I have a word document (a template that I've made ready to include different charts from an Excel dashboard). When selecting this template, I want to create a duplicate of it so that the charts won't get pasted in the original template...otherwise i'll be left with no documents in the end...
That's why i want to duplicate the document, edit it, and saving it afterwards..

Tell me if you don't understand. It's tough for me to explain properly.
 
Upvote 0
With report
date1 = Format(Date, "dd/mm/yyyy") & ".docx"
.SaveAs Filename:=ActiveWorkbook.Path & "\MP Report" & date1
End With


This one...I have a problem with the date...but Excell keep on refusing me the name stating that the name/path isn't appropriate....I have any idea what could be wrong =(
 
Upvote 0
You can't have forward slashes in a file name. So use the format ddmmyyyy. Also the extension for a template is dotx not docx. Why can't you save it as a normal document (wdFormatDocument)?
 
Upvote 0

Forum statistics

Threads
1,214,613
Messages
6,120,515
Members
448,968
Latest member
Ajax40

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top