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
371 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
  • 3
  • 3
6 Comments
 
LVL 9

Expert Comment

by:JonMny
Comment Utility
' 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
 

Author Comment

by:FaheemAhmadGul
Comment Utility
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
Comment Utility
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

Author Comment

by:FaheemAhmadGul
Comment Utility
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
 

Author Comment

by:FaheemAhmadGul
Comment Utility
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
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

A few years ago I was very much a beginner at VBA, and that very much remains the case today.  I'll do my best to explain things as I go in the hope that other beginners can follow.  If you just want to check out a tool that creates a Select Case fu…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

771 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now