Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 01-03-2020, 05:40 PM
chunkyfresh chunkyfresh is offline Mail Merge from Excel to Separate Documents and Different Folders Windows 10 Mail Merge from Excel to Separate Documents and Different Folders Office 2016
Novice
Mail Merge from Excel to Separate Documents and Different Folders
 
Join Date: Jan 2020
Posts: 2
chunkyfresh is on a distinguished road
Default Mail Merge from Excel to Separate Documents and Different Folders

Hello,

May I please get some help in forming a VBA script meeting the following Process Flow. I've tried searching online for something to work out of since I have VBA knowledge basics. However, I think I'm unable to find something similar I can use to work around with because I'm lacking in being able to put into words through a google search what could relate to this request.

Process Flow

Excel file with data is sent to or downloaded by User responsible for running script.
Excel file is placed unto User's work station where script can call it from (locate it).
User opens word file template existing within work station.
User locates button within template. <Hypothetically>
Button triggers script.
Merging and exporting takes place.
User goes to designated location to retrieve files.

Refer to Powerpoint for depiction of process flow.

Attached Files

- Just Form


- Just Data
- Original (All-in-One)
- PAF_ppt


Note:

In the past, I was able to accomplish this entirely through a script I built when the form (template) existed within Excel and not through a word file externally. One can see this from the Original file I've attached. The script is within the file. Please check it out. This I'm sure will help a lot in understanding what I'm trying to output. Make sure to edit path strings as needed.



I don't think this is something that someone hasn't thought of doing in the past. Thus, if anyone knows a of an article, forum post, etc. I could follow to accomplish this and can point me to it, I'd very much appreciate it. Making this happen will definitely help out a whole lot.

Thanks so much in advance!
Attached Files
File Type: xlsm Original.xlsm (62.8 KB, 5 views)
File Type: docx Just Form.docx (47.6 KB, 7 views)
File Type: pptx PAF_ppt.pptx (483.8 KB, 5 views)
Reply With Quote
  #2  
Old 01-03-2020, 09:46 PM
macropod's Avatar
macropod macropod is offline Mail Merge from Excel to Separate Documents and Different Folders Windows 7 64bit Mail Merge from Excel to Separate Documents and Different Folders Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

This is addressed in detail in the Mailmerge Tips and Tricks 'Sticky' thread at the top of this forum: Mailmerge Tips & Tricks
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #3  
Old 01-04-2020, 04:12 PM
chunkyfresh chunkyfresh is offline Mail Merge from Excel to Separate Documents and Different Folders Windows 10 Mail Merge from Excel to Separate Documents and Different Folders Office 2016
Novice
Mail Merge from Excel to Separate Documents and Different Folders
 
Join Date: Jan 2020
Posts: 2
chunkyfresh is on a distinguished road
Default

Hello,

Thanks for the article. I'm having a difficult time understanding a lot of it since my knowledge of VBA is basic. Sorry about that. I have a few questions.

Where do I indicate the Data Source in this script? Is there a way I can call it directly by it? For example, I tell the script the data source is file of name "Data Source" and it is located at X path.




Code:
Sub Merge_To_Individual_Files()

Application.ScreenUpdating = False

Dim StrFolder As String

Dim StrName As String 

Dim MainDoc As Document 

Dim i As Long 

Dim j As Long

Const StrNoChr As String = """*./\:?|"
Set MainDoc = ActiveDocument
With MainDoc
  'StrFolder = .Path & "\"
  With .MailMerge
    .Destination = wdSendToNewDocument
    .SuppressBlankLines = True
    On Error Resume Next
    For i = 1 To .DataSource.RecordCount
      With .DataSource
        .FirstRecord = i
        .LastRecord = i
        .ActiveRecord = i
        StrFolder = .DataFields("Supervisor") & "\"
        StrName = i & "-" & .DataFields("Name")
      End With
      .Execute Pause:=False
      If Err.Number = 5631 Then
        Err.Clear
        GoTo NextRecord
      End If
      For j = 1 To Len(StrNoChr)
        StrName = Replace(StrName, Mid(StrNoChr, j, 1), "_")
      Next
      StrName = Trim(StrName)
      With ActiveDocument
        'Add the name to the footer
        '.Sections(1).Footers(wdHeaderFooterPrimary).Range.InsertBefore StrName
        .SaveAs FileName:=StrFolder & StrName & ".docx", FileFormat:=wdFormatXMLDocument, AddToRecentFiles:=False
        ' and/or:
        .SaveAs FileName:=StrFolder & StrName & ".pdf", FileFormat:=wdFormatPDF, AddToRecentFiles:=False
        .Close SaveChanges:=False
      End With
NextRecord:
    Next i
  End With
End With
Application.ScreenUpdating = True
End Sub
Reply With Quote
  #4  
Old 01-04-2020, 09:01 PM
macropod's Avatar
macropod macropod is offline Mail Merge from Excel to Separate Documents and Different Folders Windows 7 64bit Mail Merge from Excel to Separate Documents and Different Folders Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Quote:
Originally Posted by chunkyfresh View Post
Where do I indicate the Data Source in this script? Is there a way I can call it directly by it?
You don't - the code is intended to be run from a mailmerge main document, in which case the document is automatically connected to the data source when you answer 'Yes' to the SQL prompt Word gives when you open it.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Mail merge separate recipients same file Cafeaulait Outlook 6 05-10-2018 08:00 PM
Mail Merge from Excel to Separate Documents and Different Folders Mail merge into separate list mds Mail Merge 5 04-06-2016 02:17 AM
Mail Merge from Excel to Separate Documents and Different Folders Merge info into 5 separate documents kenjmaraty Word 3 07-06-2015 07:30 AM
Mail Merge from Excel to Separate Documents and Different Folders Split each page of mail merge into separate pdf Declan Mail Merge 1 05-28-2014 02:02 AM
Mail Merge from Excel to Separate Documents and Different Folders Creating separate file for each mail merge doshshirl Mail Merge 3 02-15-2014 08:49 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 11:14 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft