+ Reply to Thread
Results 1 to 14 of 14

VBA - Combining multiple word documents based on file name and preserving format

  1. #1
    Registered User
    Join Date
    12-02-2015
    Location
    Peterborough, England
    MS-Off Ver
    2007
    Posts
    19

    VBA - Combining multiple word documents based on file name and preserving format

    Hi guys,

    I'm currently working on a Mail Merge Project and at present, the letters are built using a VBA front end in Excel, exported to word then saved via a VBA script as individual files with the type of letterhead, client name and reference number forming the filename i.e:

    FormalLetterhead Smith 12345.docx
    FormalLetterhead Jones 45678.docx
    BusinessLetterhead Ali 78910.docx
    SalesLetterhead Barry 101112.docx

    However, the merge has now reached the point whereby the area printing them has asked that these individual documents are grouped into single documents by letterhead, i.e, one document containing all of the FormalLetterhead letters, one containing all of the BusinessLetterhead letters etc.

    I've experimented with the 'insert' options but this appears to throw out the formatting of the letters which are all of variable length, potentially causing issues with the printing.

    HAs anyone explored VBA scripting for merging multiple docs into one based on filename?

    I won't be able to use any plugins or third party addons for this has anyone got any ideas?

  2. #2
    Forum Expert macropod's Avatar
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    3,726

    Re: VBA - Combining multiple word documents based on file name and preserving format

    So why not generate a single merge file for all the FormalLetterhead docs, another for all the BusinessLetterhead docs and another for all the SalesLetterhead docs? Alternatively, if you need to keep the files separate (and it's not apparent why you would), run your FormalLetterhead, BusinessLetterhead and SalesLetterhead generation processes sequentially.
    Cheers,
    Paul Edstein
    [Fmr MS MVP - Word]

  3. #3
    Registered User
    Join Date
    12-02-2015
    Location
    Peterborough, England
    MS-Off Ver
    2007
    Posts
    19

    Re: VBA - Combining multiple word documents based on file name and preserving format

    Hi Macropod,

    Might be worth explaining things in a bit more depth. Essentially there are 5 different teams in the company I work for, with each individual on these teams maybe creating up to ten letters throughout the day, all with different content/layout and requirements. Some of the teams are using a VBA Userform to build these letters and others are using a series of dropdowns in Excel.

    Towards the end of the day, each team member is merging the letters in word and running a VBA script to extract the letters and save these into a shared folder using the type of letterhead, reference number and client surname as the file name. This allows us to upload each letter individually to the client's file and do random quality checks.

    Currently, these letters are being copied in bulk into an email and sent to Canon who print and post these documents offsite, however, Canon have expressed concerns about the time taken to print these and asked that the letters are grouped by letterhead.

    My original post might have simplified things somewhat as we currently have over 30 unique letterheads and running an additional merge for each team member to group these letters is likely to cut into the time savings the merge has brought about, hence looking for something to combine them together.

    I have been experimenting with the script below that will join all documents in the same folder together and preserve formatting, however, it appears to be missing the occasional document out and probably requires too much manual moving about of files for the relatively inexperienced staff running the merges:

    (Credit to Cindy MVP for this):

    Sub MergeDocuments()
    Application.ScreenUpdating = False
    MyPath = ActiveDocument.Path
    MyName = Dir(MyPath & "\" & "*.doc")
    i = 0
    Do While MyName <> ""
    If MyName <> ActiveDocument.Name Then
    Set wb = Documents.Open(MyPath & "\" & MyName)
    Selection.WholeStory
    Selection.Copy
    Windows(1).Activate
    Selection.EndKey Unit:=wdLine
    Selection.TypeParagraph
    Selection.Paste
    i = i + 1
    wb.Close False
    End If
    MyName = Dir
    Loop
    Application.ScreenUpdating = True
    End Sub

    If the above script could be adjusted to look at the first word in each filename and group all files with the same word into one document, this would be pretty close to exactly what is required, just not sure why it doesn't appear to work for some files!

    Hope that clarifies things!
    Last edited by flakjack; 02-10-2016 at 05:33 AM.

  4. #4
    Forum Expert macropod's Avatar
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    3,726

    Re: VBA - Combining multiple word documents based on file name and preserving format

    There's nothing inherently wrong with your merge code.

    An alternative would be to modify the initial save behaviour so that each document type is saved to a different shared folder. You could then either zip all those documents together for dispatch or run your aggregation process on all files in the folder (which should overcome the risk of some files being missed).

  5. #5
    Registered User
    Join Date
    12-02-2015
    Location
    Peterborough, England
    MS-Off Ver
    2007
    Posts
    19

    Re: VBA - Combining multiple word documents based on file name and preserving format

    Hi Macropod,

    That's a possibility I've overlooked, this is the code I'm using to split the merge into individual files, think you may have helped with this one too, do you know what I'd need to change to create separate folders or ideally, one single, formatting maintained word doc for each letterhead "brand" in the code below:

    Sub merge1record_at_a_time() '
    ' merge1record_at_a_time Macro
    '
    '
    Dim fd As FileDialog


    'Create a FileDialog object as a Folder Picker dialog box.
    Set fd = Application.FileDialog(msoFileDialogFolderPicker)
    With fd


    'Use the Show method to display the Folder Picker dialog box and return the user's action.
    'The user pressed the button.
    If .Show = -1 Then
    For Each vrtSelectedItem In .SelectedItems


    'vrtSelectedItem is aString that contains the path of each selected item.
    'You can use any file I/O functions that you want to work with this path.
    'This example displays the path in a message box.
    SelectedPath = vrtSelectedItem


    Next vrtSelectedItem


    Else
    MsgBox ("No Directory Selected. Exiting")
    Exit Sub
    End If
    End With


    'Set the object variable to Nothing.
    Set fd = Nothing


    Application.ScreenUpdating = False


    MainDoc = ActiveDocument.Name
    ChangeFileOpenDirectory SelectedPath
    For i = 1 To ActiveDocument.MailMerge.DataSource.RecordCount
    With ActiveDocument.MailMerge
    .Destination = wdSendToNewDocument
    .SuppressBlankLines = True
    With .DataSource
    .FirstRecord = i
    .LastRecord = i
    .ActiveRecord = i
    docname = .DataFields("Policy_Number").Value & " " & .DataFields("Brand") & ".docx" ' ADDED CODE
    End With
    .Execute Pause:=False
    Application.ScreenUpdating = False

    End With
    ActiveDocument.SaveAs FileName:=docname, FileFormat:= _
    wdFormatXMLDocument, LockComments:=False, Password:="", AddToRecentFiles _
    :=True, WritePassword:="", ReadOnlyRecommended:=False, EmbedTrueTypeFonts _
    :=False, SaveNativePictureFormat:=False, SaveFormsData:=False, _
    SaveAsAOCELetter:=False
    ActiveWindow.Close


    Windows(MainDoc).Activate
    Next i
    Application.ScreenUpdating = True


    End Sub

  6. #6
    Forum Expert macropod's Avatar
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    3,726

    Re: VBA - Combining multiple word documents based on file name and preserving format

    So the .DataFields("Brand") field indicates which letterhead is used and, hence, the folder the file should be saved to?

    PS: When posting code, please use the code tags, indicated by the # button on the posting menu. Without them, your code loses all the indents etc. that give it structure.

  7. #7
    Registered User
    Join Date
    12-02-2015
    Location
    Peterborough, England
    MS-Off Ver
    2007
    Posts
    19

    Re: VBA - Combining multiple word documents based on file name and preserving format

    Hi Macropod,

    Thanks for letting me know about the code button, I'll use it from now on.

    Yes, the .DataFields("Brand") field indicates the type of letterhead to be used, currently the code just lumps all the letters into one shared folder but if there's a way of automatically separating these out into individual folders, that would save a lot of hassle, even more so if there's a way that individual files could be saved, each containing all of the letters for that letterhead/brand.


  8. #8
    Forum Expert macropod's Avatar
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    3,726

    Re: VBA - Combining multiple word documents based on file name and preserving format

    Try:
    Please Login or Register  to view this content.
    The above should send the output to 'Brand' subfolders in the mailmerge main document's folder.

  9. #9
    Registered User
    Join Date
    12-02-2015
    Location
    Peterborough, England
    MS-Off Ver
    2007
    Posts
    19

    Re: VBA - Combining multiple word documents based on file name and preserving format

    Hi Macropod,

    Thanks again for your input on this, it's greatly appreciated.

    I've attempted to run the script today and I'm getting the following error:

    'Compile Error' Method or Data Member not found.

    Having run the debugger it's highlighted the following:

    Sub merge1record_at_a_time()
    .execute

    I've attempted it a couple of times and no joy, any ideas?

    Thanks again.

  10. #10
    Forum Expert macropod's Avatar
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    3,726

    Re: VBA - Combining multiple word documents based on file name and preserving format

    Hmm, messed that up! Try:
    Please Login or Register  to view this content.

  11. #11
    Registered User
    Join Date
    12-02-2015
    Location
    Peterborough, England
    MS-Off Ver
    2007
    Posts
    19

    Re: VBA - Combining multiple word documents based on file name and preserving format

    Macropod, you've only gone and done it!

    Thank you very very much.


  12. #12
    Registered User
    Join Date
    03-06-2017
    Location
    USA
    MS-Off Ver
    2016
    Posts
    23

    Re: VBA - Combining multiple word documents based on file name and preserving format

    Quote Originally Posted by macropod View Post
    Hmm, messed that up! Try:
    Please Login or Register  to view this content.


    Hi Dear Macropod!
    I am looking for a code to do a task similar to this but a bit different. I would like to know if you could kindly help me with this.

    I have two folders (Letters_1 and Letters_2) each containing over 800 ".doc" files. Each ".doc" file in Letters_1 has a matching ".doc" file in Letters_2 and both have identical file names. I am looking for a VBA code that can help me automatically find and append the matching file in Letters_2 folder to the end of each corresponding ".doc" in Letters_1 folder without needing to open the files one by one.
    I'd be greatly thankful if you could please assist me with this.
    Last edited by mkh862; 03-06-2017 at 11:06 AM.

  13. #13
    Forum Expert macropod's Avatar
    Join Date
    12-22-2011
    Location
    Canberra, Australia
    MS-Off Ver
    Word, Excel & Powerpoint 2003 & 2010
    Posts
    3,726

    Re: VBA - Combining multiple word documents based on file name and preserving format

    The code you cited and the problem discussed in this thread is for an entirely different task from what you describe. You should start a new thread for your topic.

  14. #14
    Registered User
    Join Date
    03-06-2017
    Location
    USA
    MS-Off Ver
    2016
    Posts
    23

    Re: VBA - Combining multiple word documents based on file name and preserving format

    Sure, will do that.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Choose multiple Word-Documents via UserForm TextBox and combine them to Master Word-Doc
    By ODeveloper in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 10-15-2015, 09:25 AM
  2. Split a single word document into multiple word documents
    By CaptainCool in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-10-2014, 03:31 AM
  3. Batch Compile 100+ multiple word documents into a single word document
    By JamesFrames8 in forum Word Formatting & General
    Replies: 4
    Last Post: 04-03-2014, 08:49 PM
  4. How to create multiple word documents using data from multiple excel files.
    By Milan Kotus in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-14-2013, 12:07 PM
  5. Replies: 2
    Last Post: 06-22-2011, 06:50 PM
  6. combining word documents and excel worksheets
    By akcaster in forum Excel General
    Replies: 1
    Last Post: 06-09-2006, 09:10 PM
  7. preserving cell format between linked documents
    By lyriamoonriver in forum Excel General
    Replies: 1
    Last Post: 05-17-2005, 04:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1