Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    New Lounger
    Join Date
    Feb 2016
    Posts
    14
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Create Combined Documents in each folder

    Hello,

    and good day to all the programming folks here

    I am looking for some assistance and advice.

    I have many folders in my work folder example


    Accounts
    Invoices
    Outstanding
    Follow Up
    Completed
    Awaiting
    Head Office
    Archive
    etc
    etc
    etc
    ........
    and some more folders

    Each one daily receives new documents.

    I wanted to merge the documents in each folder so i could email them to various people, as well as back them up.

    Example Out put documents.
    Accounts.docx
    Invoices.docx
    outstanding.docx
    followup.docx
    completed.docx
    Awaiting.docx

    Is there a process that exists that will look in each folder and combine all the word documents for me.

    I also have other folders that I would like to apply this to so I can back up all the important documents.

    This would be such a helpful process - as I am manually combining them one by one, for my self and others.

    I found this but this only does one folder at a time
    https://www.extendoffice.com/documen...documents.html


    I am new to vba but am willing to learn how to start on this process,

    I will very much appreciate any wisdom you may have to share on vba programming.

    Any expert advice will be greatly appreciated, and many thanks for your time and advice

    ac

  2. #2
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts
    Quote Originally Posted by acs77 View Post
    I wanted to merge the documents in each folder so i could email them to various people, as well as back them up.
    So why not simply select the files and zip them into a single archive? As soon as you try combining documents that use different page layouts, headers/footers, Style definitions, etc. combining files and maintaining the original formatting, page numbering etc. becomes a real challenge. Zip archives also have the advantage of allowing you to put documents, workbooks, presentations etc. all into a single file.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  3. #3
    New Lounger
    Join Date
    Feb 2016
    Posts
    14
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Hi Paul,

    thanks for your reply.

    Yes a zip would be one approach.

    Ironically enough some people don't know what a zip file is

    The documents follow one formatting approach - so just to make it easier,
    I thought i would just combine the documents, that way no files get lost or misplaced.

    I could convert to text then combine using CMD?

    - but I may lose a table here or there hence was thinking of looking for something to combine documents.

    If you do know of anything additional - do let me know

    thank you
    ac

  4. #4
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts
    Quote Originally Posted by acs77 View Post
    Yes a zip would be one approach.

    Ironically enough some people don't know what a zip file is
    Perhaps it's time they learnt. This is precisely the kind of thing zip files are good at.

    Quote Originally Posted by acs77 View Post
    The documents follow one formatting approach - so just to make it easier,
    I thought i would just combine the documents, that way no files get lost or misplaced.
    Can you be absolutely certain they don't use any:
    • headers/footers
    • Style definitions other than the Word defaults, with none of those defaults changed
    • page numbering
    and that the page layouts for all documents are exactly the same, meaning they use the same:
    • paper size
    • left, right, top, bottom margins and gutters
    • the same page layouts vis--vis 'different first page' & 'different odd & even'
    It would be a rare organisation/user where every document is exactly the same in all these respects.

    Quote Originally Posted by acs77 View Post
    I could convert to text then combine using CMD?

    - but I may lose a table here or there hence was thinking of looking for something to combine documents.
    I don't know which CMD you have in mind but whichever one you use is likely to give you a file of gibberish. Modern Office documents are themselves zip archives and merely concatenating them would destroy their structure.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  5. #5
    New Lounger
    Join Date
    Feb 2016
    Posts
    14
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Hi Paul,

    these documents are very basic just listing essential information. Black and white text.

    I have checked and no headers or footers , or anything stylistic applied.

    I understand it is not a big job to do a few folders at a time, that's easy enough to run a simple macro above.

    I have to archive a lot of documents and - it would make sense to have an extra back up of the documents found in each folder.

    Sometimes things get removed and misplaced, and well its a saga and a half

    Also one time I did zip everything to death and lost many files due to corruption

    I still have been researching for a process to concatenate or combine the word files it finds in each folder.

    Nothing yet on my journeys

    ac

  6. #6
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts
    Quote Originally Posted by acs77 View Post
    Also one time I did zip everything to death and lost many files due to corruption
    It's not as if a combined Word file would be any safer, you know. As I have already said, Word documents are themselves nothing more than a zip file (with the extension changed). Zip archives are quite robust and utilities are readily available for recovering data from them in the unlikely event they get corrupted. Indeed, zip archives are so reliable, that's the file format Microsoft uses for Office files and its own Windows backup program!

    IMHO your objections to using a zip archive are contrived and smack of someone who has decided on a particular method rather than a solution, regardless of the negative implications of that method. Be that as it may, you can use the following code. Just don't expect any sympathy when it all goes pear-shaped.
    Code:
    Sub CombineDocuments()
    Application.ScreenUpdating = False
    Dim strFolder As String, strFile As String, StrTmp, wdDocTgt As Document, wdDocSrc As Document
    Set wdDocTgt = Documents.Add(Visible:=False)
    strFolder = GetFolder
    If strFolder = "" Then Exit Sub
    strFile = Dir(strFolder & "\*.doc", vbNormal)
    While strFile <> ""
      wdDocTgt.Range.InsertAfter vbCr & Chr(12)
      Set wdDocSrc = Documents.Open(FileName:=strFolder & "\" & strFile, AddToRecentFiles:=False, Visible:=False)
      With wdDocSrc
        wdDocTgt.Characters.Last.FormattedText = .Range.FormattedText
        .Close SaveChanges:=False
      End With
      strFile = Dir()
    Wend
    StrTmp = Split(strFolder, "\")(UBound(Split(strFolder, "\"))) & "_" & Format(Now, "YYYY-MM-DD")
    wdDocTgt.Range.InsertBefore StrTmp & " Backup"
    wdDocTgt.SaveAs2 FileName:=strFolder & "\" & StrTmp & ".docx", Fileformat:=wdFormatXMLDocument, AddToRecentFiles:=False
    Set wdDocSrc = Nothing: Set wdDocTgt = Nothing
    Application.ScreenUpdating = True
    End Sub
    
    Function GetFolder() As String
    Dim oFolder As Object
    GetFolder = ""
    Set oFolder = CreateObject("Shell.Application").BrowseForFolder(0, "Choose a folder", 0)
    If (Not oFolder Is Nothing) Then GetFolder = oFolder.Items.Item.Path
    Set oFolder = Nothing
    End Function
    Last edited by macropod; 2016-04-17 at 20:37.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  7. #7
    New Lounger
    Join Date
    Feb 2016
    Posts
    14
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Hi Paul,

    thanks for your help.

    You are correct, it is contrived - i used to zip the files but some have difficulties unzipping them, it is a long story.

    I am manually combining all the documents in each folder daily for the folks.

    Some folks do have learning disabilities - when some of their files get misplaced or deleted, they can get very very upset.

    Its easier just to combine documents and place it in a central place. While I keep a copy.

    The problem is there are too many folders to keep manually combining one by one. I do this voluntarily to help them, and my work load increases daily.


    I tested your code and yes it does the job very nicely, thank you

    May I ask if I wanted to have this code move to the next folder and automatically combine the documents in that folder as well - would this be possible at all?

    Many thanks for your time and I appreciate your help

    ac
    Last edited by acs77; 2016-04-17 at 20:32.

  8. #8
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts
    If you know what the folders are, you could use code like:
    Code:
    Sub CombineDocuments()
    Application.ScreenUpdating = False
    Dim strSubFolder, strFolder As String, strFile As String, StrTmp
    Dim wdDocTgt As Document, wdDocSrc As Document, i As Long
    strFolder = "C:\Users\" & Environ("Username") & "\Documents\"
    strSubFolder = Array("Accounts", "Invoices", "Outstanding", "Follow Up", "Completed", "Awaiting", "Head Office", "Archive")
    For i = 0 To UBound(strSubFolder)
      strFile = Dir(strFolder & strSubFolder(i) & "\*.doc", vbNormal)
      Set wdDocTgt = Documents.Add(Visible:=False)
      While strFile <> ""
        wdDocTgt.Range.InsertAfter vbCr & Chr(12)
        Set wdDocSrc = Documents.Open(FileName:=strFolder & strSubFolder(i) & "\" & strFile, _
          AddToRecentFiles:=False, Visible:=False)
        With wdDocSrc
          wdDocTgt.Characters.Last.FormattedText = .Range.FormattedText
          .Close SaveChanges:=False
        End With
        strFile = Dir()
      Wend
      StrTmp = Split(strSubFolder(i), "\")(UBound(Split(strSubFolder(i), "\"))) & "_" & Format(Now, "YYYY-MM-DD")
      With wdDocTgt
        .Range.InsertBefore StrTmp & " Backup"
        .SaveAs2 FileName:=strFolder & strSubFolder(i) & "\" & StrTmp & ".docx", _
          Fileformat:=wdFormatXMLDocument, AddToRecentFiles:=False
        .Close False
      End With
    Next
    Set wdDocSrc = Nothing: Set wdDocTgt = Nothing
    Application.ScreenUpdating = True
    End Sub
    With the above code, strFolder designates the parent folder and strSubFolder is an array of the sub-folders to be processed. As coded, the user's 'Documents' folder is assumed for strFolder, but you can change that to whatever parent folder you prefer. The sub-folders in strSubFolder, too, could have path separators for whatever portion is below the parent folder, if needed. As coded, the combined documents are saved to the corresponding sub-folders. If you'd prefer to save them to the parent folder, just change:
    .SaveAs2 FileName:=strFolder & strSubFolder(i) & "\" & StrTmp & ".docx"
    to
    .SaveAs2 FileName:=strFolder & StrTmp & ".docx"
    Last edited by macropod; 2016-04-18 at 21:36.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  9. The Following User Says Thank You to macropod For This Useful Post:

    acs77 (2016-04-18)

  10. #9
    New Lounger
    Join Date
    Feb 2016
    Posts
    14
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Hi Paul,

    this is OUTSTANDING GENEROSITY

    thank you very much for donating your time helping.

    I have set it up like this to test:

    Code:
    strFolder = "C:\Users" & Environ("Username") & "\Desktop\Student\"
    strSubFolder = Array("Roisin", "Joseph", "Amanda")
    it errored on this:
    Code:
    .SaveAs2 FileName:=strFolder & strSubFolder(i) & "\" & StrTmp & ".docx", _
          Fileformat:=wdFormatXMLDocument, AddToRecentFiles:=False

    I had a final question, instead of using the array to type in folder names - is it possible to simple select the parent folder containing the subfolders.

    Is the code able to do the process as before, i know i will be using this over and over every day, we have at least 5 years worth of folders and I cant count how many documents i will combine as a result


    thank you very much again

    again
    ac

  11. #10
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts
    Quote Originally Posted by acs77 View Post
    I have set it up like this to test:

    Code:
    strFolder = "C:\Users" & Environ("Username") & "\Desktop\Student\"
    strSubFolder = Array("Roisin", "Joseph", "Amanda")
    it errored on this:
    Code:
    .SaveAs2 FileName:=strFolder & strSubFolder(i) & "\" & StrTmp & ".docx", _
          Fileformat:=wdFormatXMLDocument, AddToRecentFiles:=False
    That code would only work if you had a user whose \Desktop\Student\ folder had subfolders named Roisin, Joseph & Amanda

    Quote Originally Posted by acs77 View Post
    I had a final question, instead of using the array to type in folder names - is it possible to simple select the parent folder containing the subfolders.
    Sure, it's possible to select a given 'parent' folder but the process I've provided would still only work if that 'parent' folder had the corresponding sub-folders. After all, that is what you asked for, with a set of very specific folder names.

    Quote Originally Posted by acs77 View Post
    Is the code able to do the process as before, i know i will be using this over and over every day, we have at least 5 years worth of folders and I cant count how many documents i will combine as a result
    It now seems to me you want something quite different from what you expressed when you started off this thread. Your first post indicated a specific set of sub-folders in a single user account that get updated every day. What you are now describing is the ability to choose a variety of parent folders whose sub-folders are undefined. That is what backup software is for. It comes free with numerous versions of Windows. I don't propose to reinvent the wheel.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  12. #11
    New Lounger
    Join Date
    Feb 2016
    Posts
    14
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Hi Paul,

    please forgive me for burdening you with this complex task, please know that I really appreciate the advice, and help and code.

    Normally what happens is -

    The students work on a project. This is split up into 15 parts - folders - to make it easier for them.

    They do a part as and when its given to them or depending on their skill set and how much support available to them.

    In the end all the documents have to be merged so that we can apply formatting to them and print it for their course folder, as well as the electronic version for marking.

    If you can imagine the chaos that ensues when files go missing, from different folders sections.

    I will set up a spreadsheet with their folder names - and then try and transpose it into the array if I have different parent folders and or subfolders.

    I am testing below

    Code:
    strFolder = "C:\Users" & Environ("Username") & "\Documents\"
    strSubFolder = Array("Amanda", "Joseph")
    Same error
    .SaveAs2 FileName:=strFolder & strSubFolder(i) & "\" & StrTmp & ".docx", _
    Fileformat:=wdFormatXMLDocument, AddToRecentFiles:=False

    am I doing some thing wrong?

    thank you again

    ac

  13. #12
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts
    Again, your revised code would only work if you had a user whose \Documents\ folder had subfolders named Amanda & Joseph. I very much doubt you have a user named Fred, for example, who has a Documents folder in which there are subfolders named Amanda & Joseph.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  14. #13
    New Lounger
    Join Date
    Feb 2016
    Posts
    14
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Hi Paul,

    my apologies for not explaining - I am testing this on my home computer. I Have to be hit between the eyes 10 times to get it

    I have created the folders Amanda and Joseph in

    This PC > Documents. > Amanda

    This PC > Documents. > Joseph

    I have put 2 word documents in each folder as a test

    I am on windows 10

    ac

  15. #14
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts
    Sorry, I omitted a \ from the parent folder path. Change:
    strFolder = "C:\Users" & Environ("Username") & "\Documents\"
    to:
    strFolder = "C:\Users\" & Environ("Username") & "\Documents\"
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  16. #15
    New Lounger
    Join Date
    Feb 2016
    Posts
    14
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Hi Paul,

    that error went away, but now up popped another


    Set wdDocSrc = Documents.Open(FileName:=strFolder & "\" & strFile, _
    AddToRecentFiles:=False, Visible:=False)

    I have been eagerly looking at the code and I can't work out what the problem might be

    ac

Page 1 of 2 12 LastLast

Posting Permissions

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