Is this possible to create?

RMerckling · #1 03-28-2018, 07:48 AM

Hey All,

Before I ask specific questions regarding a process I hope to create, I was wondering if someone can shed light on if the process is possible, to begin with.

Currently, I have a Mail Merge Template, a Document Template & a Source Document Template. The Document Template retrieves all information from the Source Document Template through IncludeText fields (the Doc Template is entirely composed of these fields) and is further updated through Mail Merge fields linked in the Source Document.

This process works great for now but I'm trying to make the process a little more streamlined. I was wondering, is it possible to have the user copy the Mail Merge Template into whatever folder, go through the Mail Merge Fields and then use VBA to create a copy of the Document Template, renamed based on fields selected within Mail Merge and run the Mail Merge all through VBA without having to actually copy the word file, repaste and run the mail merge?

P.S. I use Excel Worksheets via DDE Selection to get the correct formatting from Mail Merge to Document. Hoping that can be included in the VBA code, as well.

Appreciate the insight,
Rich

macropod · #2 03-28-2018, 02:28 PM

Your post is confusing, at best and whatever you're trying to achieve remains a mystery.

Documents and templates are not the same thing. Mailmerges don't ordinarily use templates - they use mailmerge main documents. Similarly, INCLUDETEXT fields would ordinarily point to documents, not templates.

Furthermore, it is far from clear what relationship exists between what you're calling the 'Mail Merge Template' and the other two 'templates'. You also refer to having the user 'go through the Mail Merge Fields'. To what end?

A mailmerge main document can conditionally insert content from other documents via INCLUDETEXT fields and those fields can themselves reference documents/ranges containing mergefields of their own.

As for your use of DDE, that is seriously outdated technology. Using the OLE DB provider - which is the default for Word 2002 and later - the formatting of the output in Word is independent of the data source format. Amongst other things, that means you don't have to format the data in Excel the way you want it to appear in the output (which can even necessitate having multiple columns with the same data formatted different ways with OLE). The Mailmerge Tips and Tricks 'Sticky' thread at the top of the Mail Merge forum :
https://www.msofficeforums.com/mail-...ps-tricks.html
shows how field switches can be used to generate a wide variety of formats that are independent of that of the data source.

RMerckling · #3 04-04-2018, 02:01 PM

Forgive me for not using the appropriate terminology. Anytime I mentioned "template", I was referring to a literal template, not a Word Template. Each of these documents are templates from a literal standpoint that are copied and pasted to create specific documents.

More specifically and with better labeling, my library consists of:

1 Word Document ("Source") that is entirely composed of Bookmarks with Mail Merge Fields. This file is typically not opened unless the specific bookmarked fields need to be changed.

1 Word Document ("Plan Doc") that is composed entirely of INCLUDETEXT fields that pull in the bookmarked sections of the Source Document depending on the user's choices within "Excel Doc".

1 Excel Document ("Excel Doc") that is used to dictate which sections go from the Source doc to the Plan doc based on fields filled out that go into Source through a Mail Merge.

The current process: user copies a Plan Doc and Excel Doc and places into desired folder. Fills out the Excel Doc then runs a mail merge through the Plan Doc which uses the INCLUDETEXT fields to pull from Source Doc. Then manually has to CTRL + SHIFT + F9 to turn INCLUDETEXT fields into regular text.

I want to simplify the existing process.

Future process: User copies and pastes Excel Doc into desired folder. Fills out Excel doc and runs VBA command that automatically copies the Plan Doc, pastes it into whichever folder the (copied) Excel Doc exists in, runs the mail merge with DDE (or OLE DB as you suggested, whichever works, DDE works for me at the moment) and then CTRL + SHIFT + F9 the entire document to eliminate INCLUDETEXT fields and create a doc with workable text all from Excel.

I hope this clarifies my request before. Thanks again for your time.

macropod · #4 04-04-2018, 03:16 PM

Quote:

Originally Posted by RMerckling

Anytime I mentioned "template", I was referring to a literal template, not a Word Template. Each of these documents are templates from a literal standpoint that are copied and pasted to create specific documents.

What you are calling templates are NOT templates. Furthermore, you previously said you were using:

Quote:

Originally Posted by RMerckling

a Mail Merge Template, a Document Template & a Source Document Template

but now you've introduced:

Quote:

Originally Posted by RMerckling

Excel Document ("Excel Doc")

Excel workbooks are neither documents nor templates. If you expect anyone else to understand what you're doing, you might at least use the right terminology.
As for:

Quote:

Originally Posted by RMerckling

Word Document ("Source") that is entirely composed of Bookmarks with Mail Merge Fields. This file is typically not opened unless the specific bookmarked fields need to be changed.

That is NOT your source - the source is the Excel workbook. This is apparently your mailmerge main document, since it contains your mergefields, but you say it usually doesn't get opened and, so it's also possible your "Plan Doc" is your mailmerge main document (especially since you say the merge is run through it) - but you don't mention it having any mergefields. Your descriptions are all very confusing.

Perhaps you could attach the actual files to a post with some representative content (delete anything sensitive).

RMerckling · #5 04-05-2018, 06:02 AM

Paul,

Template: "a preset format for a document or file, used so that the format does not have to be recreated each time it is used." Seems like I'm using templates.

"Excel Doc": Excel "Workbook", my apologies.

"Source": "Source" is simply a label and I think you're being a bit overly critical.

With that being said, I want to clarify that I do appreciate your feedback and remind you that I am no expert in this field which could be why my terminology and/or explanation is off. I would love to attach the files, but they're not on hand at the moment.

Thanks, Paul,
Rich

RMerckling · #6 04-18-2018, 09:52 AM

Paul,

I've managed to scrape together code that copies/renames the Plan Word Document and pastes within the same folder as the Excel Workbook. Now I just need to figure out how to run a Mail Merge on the newly created copy of Plan Word Document.

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

All data is contained in "Data" worksheet within the Excel Workbook, and only one single Mail Merge needs to be ran using DDE, preferably.

Again, your assistance is greatly appreciated,
Rich

macropod · #7 04-18-2018, 04:09 PM

Quote:

Originally Posted by RMerckling

Template: "a preset format for a document or file, used so that the format does not have to be recreated each time it is used." Seems like I'm using templates.

What you're calling a template isn't what a template means in Word. In Word, (boilerplate) documents and templates are entirely different things; they're even different file types.

In post #4, I said:

Quote:

Originally Posted by macropod

Perhaps you could attach the actual files to a post with some representative content (delete anything sensitive).

Still waiting.

eduzs · #8 04-18-2018, 04:32 PM

So I suppose that you are calling word within excel.
You want to create documents based in values in a excel worksheet? If so, is better to call excel within Word and not vice versa.

RMerckling · #9 04-19-2018, 07:40 AM

eduzs,

The only manual work to be done is in Excel. For my process, calling Word within Excel streamlines the process.

Thanks,
Rich

macropod · #10 04-19-2018, 06:06 PM

Now cross-posted at: http://www.vbaexpress.com/forum/show...ith-DDE-option
For cross-posting etiquette, please read: http://www.excelguru.ca/content.php?184

RMerckling · #11 04-20-2018, 07:22 AM

Thanks Paul,

Next time I'll leave a link within the new post to the original and include why I'm cross-posting. Was unaware this was an issue.

Also hoping for some clarification on this, is there any other way to explain what I need the code to do because all my documents are filled with sensitive company material and I don't think I'll be able to post it here. Is there no generic code for a mail merge with DDE function to run?

Thanks,
Rich

macropod · #12 04-20-2018, 04:13 PM

No, there is no 'generic code for a mail merge with DDE function' - the code is specific to each implementation. In any event, I don't see why you'd want to use DDE.

All we need is files containing sufficient material to see how each relates to the others. We don't need anything sensitive.

RMerckling · #13 04-23-2018, 08:13 AM

Paul,

I believe I've overcomplicated my question in my initial post, plus, I had a bunch of additional questions that have since been solved. All the Excel Workbook's VBA code needs to do is run a (DDE format) mail merge to the document it has just created.

Unfortunately, the workbook & documents themselves are not under my ownership and I cannot release even a part of them. I've attached some basic examples of what the workbook looks like, and a basic word document with some Merge fields attached that come from the Mail Merge Workbook.

Please, let me know if you have any additional questions and I just wanted to say thanks again for your time & advice along the way.
-Rich

RMerckling · #14 04-27-2018, 01:18 PM

Code:

Sub CopyandRename()
    Dim str1 As String
    str1 = "Q:\IC\New Structure\IC Toolkit\Templates\01 Plan Doc Template\16 Source\IC Plan Doc Template v1.0.docx"
    PlanDocTemplate = Application.ActiveWorkbook.Path & "\" & Range("A1").Value & ".docx"
    
    Call FileCopy(str1, PlanDocTemplate)
    
    strWorkbookName = ThisWorkbook.Path & "\" & ThisWorkbook.Name
    Worksheets("Data").Activate
       
'Opens New Plan Doc Template

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

    appWD.Documents.Open Filename:=PlanDocTemplate
    
    ActiveDocument.MailMerge.OpenDataSource Name:=strWorkbookName, _
    Format:=wdMergeInfoFromExcelDDE, _
    ConfirmConversions:=True, _
    ReadOnly:=False, _
    LinkToSource:=True, _
    AddtoRecentFiles:=False, _
    PasswordDocument:="", _
    PasswordTemplate:="", _
    Revert:=False, _
    Connection:="Entire Spreadsheet", _
    SQLStatement:="SELECT * FROM `Data$`", _
    SQLStatement1:="", _
    SubType:=wdMergeSubTypeOther
End Sub

macropod · #15 04-30-2018, 06:30 PM

IMHO, your project is over-engineered. There is no need for a mailmerge (or your Data worksheet) for this project. Indeed, given what you've posted, I'd have to wonder why Excel is involved at all - but that's another matter.

All you really need do is assign some bookmark names to your 'Plan Template' document (which really should be saved as a Word template (i.e. a dotx-format file), then use code like:

Code:

Sub Demo()
'Note: A reference to the Word library must be set, via Tools|References
Dim wdApp As New Word.Application, wdDoc As Word.Document
Dim xlSht As Worksheet, StrTmplt As String, StrDocNm As String
StrTmplt = "C:\Users\" & Environ("Username") & "\Desktop\Plan Template.dotx"
If Dir(StrTmplt) = "" Then Exit Sub
Set xlSht = Worksheets("Form")
StrDocNm = Application.ActiveWorkbook.Path & "\" & xlSht.Range("A1").Value & ".docx"
wdApp.Visible = False
Set wdDoc = wdApp.Documents.Add(Template:=StrTmplt, Visible:=False)
Call UpdateBookmark(wdDoc, "Client", xlSht.Range("C12").Text)
Call UpdateBookmark(wdDoc, "Product", xlSht.Range("C14").Text)
Call UpdateBookmark(wdDoc, "Role", xlSht.Range("C15").Text)
Call UpdateBookmark(wdDoc, "Start_Date", xlSht.Range("C22").Text)
Call UpdateBookmark(wdDoc, "End_Date", xlSht.Range("D22").Text)
With wdDoc
    'update any cross-references to the bookmarks
    .Fields.Update
    'save & close
    .SaveAs2 Filename:=StrDocNm, FileFormat:=wdFormatXMLDocument, AddToRecentFiles:=False
End With
wdApp.Quit
Set wdDoc = Nothing: Set wdApp = Nothing
End Sub

Sub UpdateBookmark(wdDoc As Word.Document, StrBkMk As String, StrTxt As String)
Dim BkMkRng As Range
With wdDoc
  If .Bookmarks.Exists(StrBkMk) Then
    Set BkMkRng = .Bookmarks(StrBkMk).Range
    BkMkRng.Text = StrTxt
    .Bookmarks.Add StrBkMk, BkMkRng
  End If
End With
Set BkMkRng = Nothing
End Sub

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Create PDF	Anitha	Word	5	02-01-2018 11:13 PM
How to create this?	Jil.10	Word	4	05-09-2016 11:56 AM
how to create a special template to create a clear lay out for plain text	hex	Word	1	04-01-2016 09:58 AM
Mail merge from excel - need to create sheets and create a table	bluenosebex	Mail Merge	5	08-02-2015 05:34 PM
create a datafile	acki	Excel	1	01-26-2012 07:49 AM