Page 1 of 2 12 LastLast
Results 1 to 15 of 27
  1. #1
    3 Star Lounger
    Join Date
    Jun 2009
    Location
    Hemet CA
    Posts
    310
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Talking

    What I’m trying to do is from my excel workbook I want to use a command button that will copy a file folder named "Reports" that is located same workbook path and save it , destination to be determined

    Also if possible, this file folder holds from 4 to 20 possible word document reports that have linked fields. I would like to have these linked fields broken but still keeping the current data from links in tacked. This way I can achieve these reports and if I need to make a minor adjustment, the link field will be a problem.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I don't think it would be a good idea to store the workbook in the folder that you want to copy. When you run the code, the workbook is locked, so you might run into problems when you try to copy the folder.

    Here is the general idea of a macro that will copy a folder and break all links in Word documents in the target folder. You need to set a reference to the Microsoft Word n.0 Object Library and to the [s]Windows[/s] Microsoft Scripting Runtime in Tools | References...

    Code:
    Sub CopyAndProcessFolder()
      Dim fso As Scripting.FileSystemObject
      Dim fol As Scripting.Folder
      Dim fil As Scripting.File
      Dim wrd As Word.Application
      Dim doc As Word.Document
      Dim fld As Word.Field
      Dim strSource As String
      Dim strTarget As String
      strSource = "C:\OldFolder" ' Source folder path
      strTarget = "C:\NewFolder" ' Target folder path
      Set fso = CreateObject("Scripting.FileSystemObject")
      If fso.FolderExists(strTarget) Then
    	MsgBox strTarget & " already exists!", vbExclamation
    	Exit Sub
      End If
      fso.CopyFolder strSource, strTarget
      Set wrd = CreateObject("Word.Application")
      Set fol = fso.GetFolder(strTarget)
      For Each fil In fol.Files
    	If fil.Name Like "*.doc*" Then
    	  Set doc = wrd.Documents.Open(Filename:=fil.Path, _
    		AddToRecentFiles:=False)
    	  For Each fld In doc.Fields
    		If fld.Type = wdFieldLink Then
    		  fld.Update
    		  fld.Unlink
    		End If
    	  Next fld
    	  doc.Close SaveChanges:=True
    	End If
      Next fil
      wrd.Quit SaveChanges:=False
    End Sub

  3. #3
    3 Star Lounger
    Join Date
    Jun 2009
    Location
    Hemet CA
    Posts
    310
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi HansV
    I'm sorry for any confusion
    I use the Excel workbook to navagate to different word document in folder named "Reports". When a reports is completed I wanted to copy folder named "Reports" remove all link fields and then save.
    I'm no code master but code that you have provided looks like it may work, I'll give it a try
    Thank you so very much

  4. #4
    3 Star Lounger
    Join Date
    Jun 2009
    Location
    Hemet CA
    Posts
    310
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Unhappy

    Hi HansV
    When i run code I receive an error

    Dim fso As Scripting.FileSystemObject

    User-defined type not defined

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Sorry, I made a mistake in my previous reply, you should set a reference to Microsoft Scripting Runtime, not to Windows Scripting Runtime (which doesn't exist).

  6. #6
    3 Star Lounger
    Join Date
    Jun 2009
    Location
    Hemet CA
    Posts
    310
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Unhappy

    Hi HansV
    I looked it over and I'm not sure how that done.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Select Tools | References... in the Visual Basic Editor.
    Scroll down the list until you see Microsoft Scripting Runtime.
    Tick the check box to the left of this entry.
    Click OK.

  8. #8
    3 Star Lounger
    Join Date
    Jun 2009
    Location
    Hemet CA
    Posts
    310
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='800524' date='29-Oct-2009 07:40']Select Tools | References... in the Visual Basic Editor.
    Scroll down the list until you see Microsoft Scripting Runtime.
    Tick the check box to the left of this entry.
    Click OK.[/quote]
    Hi HansV
    I made the changes, but now I get same error different line

    Dim wrd As Word.Application

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Please take the trouble to read my original reply again. It's explained there.

  10. #10
    3 Star Lounger
    Join Date
    Jun 2009
    Location
    Hemet CA
    Posts
    310
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Post

    Hi HansV
    Thank you so much for your assistance and patience. It appears to be working….

  11. #11
    3 Star Lounger
    Join Date
    Jun 2009
    Location
    Hemet CA
    Posts
    310
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi HansV
    I love the new look, but its going to take a little time to get use to... all good though.

    First of all it works great, but I do need to make a couple of changes if possible.

    1.In this folder that I am coping, I noticed I was having a problem with a couple of docm, that are not removing link fields, I believe part of the problem is due to document protection and that some link fields are located inside header
    I know how to add protection and remove protection. So how do I remove protection only on the docm that have protection, do I use something like this? Or does this only apply to a single document.

    Code:
    If ActiveDocument.ProtectionType = wdAllowOnlyFormFields Then
        ActiveDocument.Unprotect ""
      End If
    I tried it but I may have put it in the wrong location, I inserted this right after
    Code:
    Set doc = wrd.Documents.Open(Filename:=fil.Path, _
            AddToRecentFiles:=False)
    I’m not sure how to do the header link fields….or if it is even possible

    My next question is, instead of having a fixed destination with a new folder name, is it possible to open a dialog box that will allow user to choose name and location.

    once again I thank you for all your help

  12. #12
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts
    If you open the document with the code
    Code:
    Set doc = wrd.Documents.Open(Filename:=fil.Path, _
            AddToRecentFiles:=False)
    then you should unprotect it with
    Code:
    If doc.ProtectionType = wdAllowOnlyFormFields Then
        doc.Unprotect ""
      End If
    ActiveDocument is a pointer to the document that is currently displayed
    doc is a pointer to the document that you just opened
    These two may be different

  13. #13
    3 Star Lounger
    Join Date
    Jun 2009
    Location
    Hemet CA
    Posts
    310
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi StuartR
    Thank you so much, I was thinking the same thing about the ActiveDocument, it didnt make any since, i just wasnt sure what was the correct method.
    Many thanks

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Does the code in the attached text file do what you want?

    [attachment=86270:Code.txt]

    Attached Files Attached Files

  15. #15
    3 Star Lounger
    Join Date
    Jun 2009
    Location
    Hemet CA
    Posts
    310
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi HansV
    I got an error message. View JPEG below
    I set the Source folder path to the folder I want copied
    I added the password to unprotect
    Did I miss something?
    Attached Images Attached Images

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
  •