Solved

Help needed writing a Word Macro that copies only the last paragraph from several different documents in a folder and pastes it to a target document

Posted on 2008-06-19
6
386 Views
Last Modified: 2013-11-27
I have a folder that contains about 80 word documents each with a different name. Some of these (perhaps 40/80) get modified on a given day and others not. Those that get modified have one paragraph added at the end of them. I would like to have a macro that, when run at the end of a day, will check which of the dose documents have been modified (and thus have had a paragraph added to them during that day). The macro will then copy the last paragraph (which would have been the new paragraph added to the document) from each of those documents into a new document called say, Target.doc. This would mean that the Target.doc, at the end of that day will have all the new paragraphs that have been added to any of those individual files during that day. In summary, I suppose, if this macro was run at 1700 on 19 June, it will first check which of the 80 documents have been modified on 19 June. It will then open (one by one) each of those documents that have been modified on 19 June, select and copy the last paragraph from these documents and past that into a previously created document called Target.doc (which would be possibly be in a different folder from the source documents). The Target.doc will, therefore, at the end of 19 June will have a collection of all the new paragraphs added to any of the individual 80 source documents on 19 June. I would greatly appreciate help for the code for this macro. Thanks
0
Comment
Question by:FaheemAhmadGul
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 9

Expert Comment

by:JonMny
ID: 21831024
' Open and Print a Word Document
Dim ofs
Set ofs = CreateObject("Scripting.FileSystemObject")
    Set oFolder = oFs.GetFolder("c:\tools")
 
    For Each oFile In oFolder.Files
If InStr(ofile.Name,".doc")>0 Then

Set objWord = CreateObject("Word.Application")
Set oDoc = objWord.Documents.Open(ofile.Path,,true )
 
Dim oPara
        Dim oRange
        Dim nRangeStart
        Dim nRangeEnd
       nRangeStart= oDoc.Paragraphs.Last.Range.Start
     nRangeEnd = oDoc.Paragraphs.Last.Range.end
              Set oRange = oDoc.Range(nRangeStart, nRangeEnd)

                  mytext=mytext & ofile.Path & vbCrLf &  oRange & vbCrLf
                  
objWord.Documents.Close(False)
objWord.Application.Quit
Set objword=Nothing
                  
End If
Next


'now save to new


Set objWord = CreateObject("Word.Application")
objWord.Caption = "Paragraphs"
objWord.Visible = True
Set objDoc = objWord.Documents.Add()
Set objSelection = objWord.Selection
objSelection.Font.Name = "Arial"
objSelection.Font.Size = "18"
objSelection.TypeText "Paragraphs Report"
objSelection.TypeParagraph()

objSelection.Font.Size = "14"
objSelection.TypeText "" & Date() & vbCrLf
objSelection.TypeText mytext
objSelection.Font.Size = "10"


objDoc.SaveAs("C:\Tools\testdoc.doc")
objWord.Quit
0
 
LVL 1

Author Comment

by:FaheemAhmadGul
ID: 21832477
Your solutions my be correct but being a beginner I have not been able to fully understand it. I would appreciate if you could explain how to use it a little more for me. Please let me know what folder I should create on my C drive if I were to use the code you have prepared exactly as it is and what should be name of Target.doc into which your code will be copying the paragraphs from other documents.
I would also be grateful if you could comment your code a little more so that I can understand what its different parts are doing. Another thing is that when I have put your code into one my macros one line was turned into red by the visual basic editor, indicating I suppose some error. I enclose a small (3 line) part where this error is showing

objSelection.Font.Size = "18"
objSelection.TypeText "Paragraphs Report"
objSelection.TypeParagraph()  (this third line is coming up as red in visual basic editor

Many thanks for your help.
To make it a little clearer what want my macro to do I am framing what I want to achieve in a slightly different way as follow.

I have a folder on my C drive (path)
C:\ Important Documents\
This folder contains the following files
1.      aaa.doc
2.      bbb.doc
3.      ccc.doc
4.      ddd.doc
5.      eee.doc
6.      Target.doc

Every day a paragraph is added at the end of two or more of the files (for example to file  aaa.doc  and  ccc.doc  on 19 June) but not to others. At the end of the day I want to run a macro that will first check which files have been modified that day (in this case on 19 June file  aaa.doc  and file  ccc.doc  would have been modified and saved). I want my macro to then get that last paragraph that has been added to file aaa.doc  and file  ccc.doc  on 19 June, copy it and past it into the document 6 which in this case is called Target.doc and save Target.doc.

A simpler form of the macro for this kind of purpose will copy the last paragraph from all the five documents in the folder C:\Important Documents\ but I want the last paragraph from only those documents which have been modified and saved on the day the macro is run (that is the current date). Therefore, I think the desired macro will perhaps first check which files have been modified on the day it is run and then get last paragraph from only those files to be pasted into Target.doc.
0
 
LVL 9

Expert Comment

by:JonMny
ID: 21833006
Sorry My original post used vbscript, here is the vb code, this will run in word.  Added comments...



Sub GetParagraph()
'------------------------------------------
'Declare Variables
'------------------------------------------
Dim oPara
Dim odoc As Document
Dim oRange
Dim nRangeStart As Integer
Dim nRangeEnd As Integer
Dim ofs
Dim workingfolder As String
Dim outputfile As String
Dim newdoc As Document
Dim mytext As String
'------------------------------------------
'Set Working folder and output file
'------------------------------------------
workingfolder = "C:\Important Documents" 'Path to folder with docs
outputfile = "c:\tools\Target.doc"       'Where to save the createdreport
'------------------------------------------
Set ofs = CreateObject("Scripting.FileSystemObject") 'create file object to read folder
    Set oFolder = ofs.GetFolder(workingfolder)       'get folder
    For Each ofile In oFolder.Files                  'iterate all files
If InStr(ofile.Name, ".doc") > 0 Then               'if a doc file process
    If Format(ofile.DateLastModified, "mmDDYY") = Format(Now(), "mmDDYY") Then
        Set odoc = Application.Documents.Open(ofile.Path, , True)  'open file
        nRangeStart = odoc.Paragraphs.Last.Range.Start 'get start of the last paragraph
        nRangeEnd = odoc.Paragraphs.Last.Range.End     'get end of the last paragraph
        Set oRange = odoc.Range(nRangeStart, nRangeEnd) 'copy
        mytext = mytext & ofile.Path & vbCrLf & oRange & vbCrLf 'add to final output variable
        odoc.Close (False)
    End If
End If
Next
'------------------------------------------
'Create Report
'------------------------------------------
    Set newdoc = Application.Documents.Add   'Add new document for the report
    Set objSelection = Application.Selection
    objSelection.Font.Name = "Arial"          'set fonts and info
    objSelection.Font.Size = "18"
    objSelection.TypeText "Paragraphs Report"  'add title
    objSelection.Font.Size = "14"
    objSelection.TypeText "" & Date & vbCrLf
    objSelection.TypeText mytext               'Type report that we saved to mytext
    objSelection.Font.Size = "10"
    newdoc.SaveAs outputfile
   
End Sub
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 1

Author Comment

by:FaheemAhmadGul
ID: 21835124
The code worked partially. It seems it correctly identified which of a total of 6 six documents was modified and saved on 20 June (in this case it was C:\Important Documents\Temple Court.doc), but it did not copy into the Target.doc the last paragraph from the  document Temple Court.doc. Instead what I got in the Target.doc after running the macro was the following.

Paragraphs Report20/06/2008
C:\Important Documents\Court Temple.doc
Perhaps the code needs some modification.
I thank you for your help in anticiaption.


0
 
LVL 1

Author Comment

by:FaheemAhmadGul
ID: 21837196
Please ignore my earilier comment. Your macro is working nearly perfectly. There are only two problems. The first is that when it copies the last paragraph from a source document into the target document, the formating of the paragraph from the source document is lost. The second problem is that in the Target document, above each paragraph copied, your macro adds the path and file name of the document from which it has copied the paragraph (I  do not want this to happen). I only need the paragraph from the source document with its formating retained.
Please see the two files attached. The first is the Target Document as it looked after running your macro. The Second is the Target Document as I would have liked it to look after running the macro. Here Mr ABC, Mr XYZ and MR QRS are the names of the files that have changed in C:\Important Documents  on 21 June.
Once these two little issues are resolved your solution will become perfect for me. Thanks again for help. It is greatly appreciated.
Target-How-It-Looked-After-Runni.doc
Target-How-I-would-have-liked-it.doc
0
 
LVL 9

Accepted Solution

by:
JonMny earned 500 total points
ID: 21837744
This should do it..


Sub GetParagraph()
'------------------------------------------
'Declare Variables
'------------------------------------------
Dim oPara
Dim odoc As Document
Dim oRange
Dim nRangeStart As Integer
Dim nRangeEnd As Integer
Dim ofs
Dim workingfolder As String
Dim outputfile As String
Dim newdoc As Document
Dim mytext As String


'------------------------------------------
'Set Working folder and output file
'------------------------------------------
workingfolder = "C:\tools" 'Path to folder with docs
outputfile = "c:\tools\Target.doc"       'Where to save the createdreport
'------------------------------------------
Set newdoc = Application.Documents.Add   'Add new document for the report
Set objselection = Application.Selection
objselection.TypeText "Paragraphs Report"  'add title
objselection.TypeText "" & Date & vbCrLf

Set ofs = CreateObject("Scripting.FileSystemObject") 'create file object to read folder
    Set oFolder = ofs.GetFolder(workingfolder)       'get folder
    For Each ofile In oFolder.Files                  'iterate all files
If InStr(ofile.Name, ".doc") > 0 Then               'if a doc file process
    If Format(ofile.DateLastModified, "mmDDYY") = Format(Now(), "mmDDYY") Then
        Set odoc = Application.Documents.Open(ofile.Path, , True)  'open file
        nRangeStart = odoc.Paragraphs.Last.Range.Start 'get start of the last paragraph
        nRangeEnd = odoc.Paragraphs.Last.Range.End     'get end of the last paragraph
        odoc.Range(nRangeStart, nRangeEnd).Copy 'copy
        newdoc.Activate
        Selection.PasteAndFormat (wdPasteDefault)
        Selection.MoveEnd
        Selection.TypeParagraph
        odoc.Close (False)
    End If
End If
Next
'------------------------------------------
'Create Report
'------------------------------------------
newdoc.SaveAs (outputfile)
   
End Sub
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I would like to show you some basics you can do with Mailings in MS Word. It´s quite handy feature you can use for creating envelopes, labels, personalized letters etc. First question could be what is this feature good for? Mailing can really he…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
This video shows where to find templates, what they are used for, and how to create and save a custom template using Microsoft Word.
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question