Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Aug 2006
    Location
    Gtr London, England
    Posts
    256
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Macro to change documents in a directory

    Hi,

    Im trying to amend the top margin of a number of documents held in a directory but cant seem to get passed the problem with the beginning of the For Each loop which creates a syntax error. Ive tried some variations but still seem to get an error at this point, in some shape or form. The code Ive put together is below, Id be grateful for any pointers or suggestions please;


    Code:
    Sub Copy_Content()
    Dim targDoc As Word.Document
    'Dim FileName As String
    
    'Set FileName = "c:\change\"
    Set targDoc = Application.Documents.Open("c:\change\*.doc")
    
    For Each targDoc
      Selection.WholeStory
      Selection.PageSetup.TopMargin (1)
    Next
    End Sub
    Many Thanks

    Lee

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,235
    Thanks
    202
    Thanked 796 Times in 729 Posts
    Lee,

    You can only use the For Each construct when you have a collection of items in an object. TargetDoc is a single document. You need something more like this:
    Code:
    Sub Copy_Content()
    
       Dim targDoc As Word.Document
       Dim zFileName As String
    
      zFileName = Dir("c:\change\*.doc") 'Get first file
    
      if zFileName = "" then Exit Sub   'No files found
    
      Do 
         Set targDoc = Application.Documents.Open(zFileName)
         Selection.WholeStory
         Selection.PageSetup.TopMargin (1)
        targDoc.Save
       targDoc.Close
       zFileName = Dir()   'Get next file
    Loop While zFileName<>""
    
    End Sub
    Please note that this code is not Fully Tested. I did test the Do-Loop to make sure it would loop through a directory and that works. From the Set targDoc through targdoc.Close lines are untested "Air Code" from my point of view.

    Here's the code I used to test the loop:
    Code:
    Sub Copy_Content()
    
       Dim targDoc As Word.Document
       Dim zFileName As String
    
      zFileName = Dir("G:\BEKDocs\Word\Letters\*.doc") 'Get first file
    
      If zFileName = "" Then Exit Sub   'No files found
    
      Do
         Debug.Print zFileName
        zFileName = Dir()   'Get next file
    Loop While zFileName <> ""
    
    End Sub
    HTH
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  3. #3
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,235
    Thanks
    202
    Thanked 796 Times in 729 Posts
    Lee,

    Here's a fully tested version:
    Code:
    Option Explicit
    
    Sub Copy_Content()
    
       Dim targDoc   As Word.Document
       Dim zFileName As String
       Dim zFilePath As String
    
       Application.ScreenUpdating = False 'Keep screen from flashing
       
       zFilePath = "G:\BEKDocs\Word\JunkDir\"  ' <-- Your File path here
       zFileName = Dir(zFilePath & "*.doc")    'Get first file
    
       If zFileName = "" Then Exit Sub         'No files found get out
    
       Do
          Set targDoc = Application.Documents.Open(zFilePath & zFileName)
          ActiveDocument.PageSetup.TopMargin = InchesToPoints(1#)
          targDoc.Save
          targDoc.Close
          zFileName = Dir()   'Get next file
       Loop While zFileName <> ""
    
    End Sub
    HTH
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  4. #4
    3 Star Lounger
    Join Date
    Aug 2006
    Location
    Gtr London, England
    Posts
    256
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Hi HTH,

    Many thanks for your reponse. Unfortunately it errors at ;

    Code:
     Set targDoc = Application.Documents.Open(zFileName)
    stating that it cannot find zFileName. Ive tried a few variations but still keep getting this. Stepping through shows that zFileName only holds the document name. Should it be holding the path and the document name maybe?

    Best Regards
    Lee

  5. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,235
    Thanks
    202
    Thanked 796 Times in 729 Posts
    Lee,

    You copied the first version you need the tested version from post #3.

    HTH = Hope this Helps!
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  6. The Following User Says Thank You to RetiredGeek For This Useful Post:

    leepurton (2013-11-19)

  7. #6
    3 Star Lounger
    Join Date
    Aug 2006
    Location
    Gtr London, England
    Posts
    256
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Hi RG,

    Thats great, many thanks for your help, nuch appreciated.

    Best Regards (from a cold London)
    Lee

Posting Permissions

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