Copy & Paste Word Doc from Excel VBA

RMerckling · #1 04-12-2018, 07:44 AM

Hi all,

Attempting to simplify an existing process. Right now I have users copying & pasting an Excel Workbook and a Word Document ("Plan Doc") into specific folders, filling out the Excel Workbook then running a Mail Merge through the Plan Doc.

New Process:
I'd like the user to copy & paste the Excel Workbook into their desired folder and after filling out the Excel Workbook's cells to be utilized by Mail Merge, run a VBA code that copies the Plan Doc (will always have the same location) into the same folder as the newly placed Excel Workbook and re-names it based on certain cell values in Excel.

Attaching an example Excel Workbook with some examples of what the user would be filling out. These fields in Excel Workbook are used in the Plan Doc as a Mail Merge. Eventually, I'd like to automate the Excel VBA to contain code to run a Mail Merge in the Plan Doc after copying / pasting the Plan Doc... but one step at a time.

Forgive me if this is confusing, I'd be happy to answer any questions.

Thanks,
Rich

BobBridges · #2 04-14-2018, 08:14 PM

I don't know from Mail Merge—I've done it a few times, but not often enough to remember how—but it seems all you want, at least for now, is to copy a document with a known path and name to a new document with the current path and a calculated name. Nothing hard or complex about that, right? What am I missing?

First you calculate the new Word document name, probably using this formula:

Code:

=$C$2&" "&$C$4&" "&$C$3&" "&$C$6&" "&TEXT($C$7,"m-d-yyyy")&"-"&TEXT($C$8,"m-d-yyyy")&".docx"

Then you have code that fetches a copy of the template (so to speak) from the old location, and copy it to C:\<current path>\<calculated filename>.

Either I've misunderstood you, or there's some part of that you don't know how to do. If the latter, which part? I don't want to insult you by telling you all the things you already know and leaving out the one part that's confusing you.

For the copying part, you could open up the Word application, then do an Open and a Save-As. But I'm sure it'll be simpler to access some Windows feature that does file copies. Is that the part you're having trouble with? Because if it is, I'd have to google it myself. Won't be hard, though.

macropod · #3 04-14-2018, 10:21 PM

This seems to be a resurrection of your discussion in: https://www.msofficeforums.com/word-...le-create.html for which you have yet to provide the documentation requested. Kindly keep discussions on a particular topic in a single thread.

RMerckling · #4 04-18-2018, 09:54 AM

Code:

Sub CopyandRename()
    Dim str1 As String
    Dim str2 As String
    str1 = "Q:\IC\New Structure\IC Toolkit\Templates\01 Plan Doc Template\16 Source\IC Plan Doc Template v1.0.docx"
    str2 = Application.ActiveWorkbook.Path & "\" & Range("A1").Value & ".docx"
    
    Call FileCopy(str1, str2)
    
'Opens New Plan Doc Template
    Set appWd = CreateObject("Word.Application")
    appWd.Visible = True

    appWd.Documents.Open Filename:=Application.ActiveWorkbook.Path & "\" & Worksheets("Form").Range("A1").Value & ".docx"

End Sub

macropod · #5 04-18-2018, 03:57 PM

You don't seem to be paying attention. Thread closed. Kindly keep all of the discussion in your original thread.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Copy/paste from Excel to Word problems!	mross127	Word	10	08-16-2017 04:41 PM
Copy Underline text from Word and Paste into excel	rfaris	Excel Programming	7	10-05-2015 05:56 AM
Copy/Paste EXCEL cells as pic in WORD	A_Lau	Drawing and Graphics	3	12-19-2014 06:57 AM
Word 2007 Copy/Paste Excel issue	raven26c	Word	1	11-18-2011 02:49 AM
copy/paste charts from excel to word	bielak01	Excel	0	04-16-2009 02:27 AM