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

    Talking

    From my 2007 workbook I would like to use VBA to copy a folder (“Reports”), remove all links and save to a separate folder, let’s say C:\ My Documents. Both workbook and folder (“Reports”) are located in same sub folder. By the way the links are from workbook to word docm.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    What exactly do you mean by "folder" here?

  3. #3
    3 Star Lounger
    Join Date
    Jun 2009
    Location
    Hemet CA
    Posts
    310
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='786086' date='24-Jul-2009 07:36']What exactly do you mean by "folder" here?[/quote]
    The folder that I am refering to is named "Reports" and it holds a few word.docm that are linked to my excel spreedsheet. I would like to be able to copy this folder and save it in archieves but of course I'd like to remove links

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I'm confused, you originally wrote "the links are from workbook to word docm" and now "a few word.docm that are linked to my excel spreedsheet". Which is it?

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

    Talking

    [quote name='HansV' post='786093' date='24-Jul-2009 08:00']I'm confused, you originally wrote "the links are from workbook to word docm" and now "a few word.docm that are linked to my excel spreedsheet". Which is it?[/quote]
    Hi HansV
    I'm sorry, I Apologize for any confusion, my mistake
    I have a main folder named "Home Inspection" that holds a sub folder named "Reports" and an Excel workbook named "Inspection". The sub folder named "Reports" holds 18 word.docm's that are linked to data from my Excel workbook named "Inspection".

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Breaking the links has to be done in Word, not in Excel. Here is some code you can run from within Word to break the links in all .docm files in the folder C:\Home Inspection\Reports:

    Code:
    Public Sub ProcessFolder()
      ' Modify as needed, but keep the trailing backslash
      Const strFolder = "C:\Home Inspection\Reports\"
      Dim strFile As String
      Dim doc As Document
      Application.ScreenUpdating = False
      strFile = Dir(strFolder & "*.docm")
      Do While Not strFile = ""
    	Set doc = Documents.Open(strFolder & strFile)
    	Call BreakLinks(doc)
    	doc.Close SaveChanges:=True
    	strFile = Dir
      Loop
      Application.ScreenUpdating = True
    End Sub
    
    Private Sub BreakLinks(doc As Document)
      Dim fld As Field
      On Error Resume Next
      For Each fld In doc.Fields
    	fld.LinkFormat.BreakLink
      Next fld
    End Sub

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

    Talking

    [quote name='HansV' post='786162' date='25-Jul-2009 02:16']Breaking the links has to be done in Word, not in Excel. Here is some code you can run from within Word to break the links in all .docm files in the folder C:\Home Inspection\Reports:

    Code:
    Public Sub ProcessFolder()
      ' Modify as needed, but keep the trailing backslash
      Const strFolder = "C:\Home Inspection\Reports"
      Dim strFile As String
      Dim doc As Document
      Application.ScreenUpdating = False
      strFile = Dir(strFolder & "*.docm")
      Do While Not strFile = ""
    	Set doc = Documents.Open(strFolder & strFile)
    	Call BreakLinks(doc)
    	doc.Close SaveChanges:=True
    	strFile = Dir
      Loop
      Application.ScreenUpdating = True
    End Sub
    
    Private Sub BreakLinks(doc As Document)
      Dim fld As Field
      On Error Resume Next
      For Each fld In doc.Fields
    	fld.LinkFormat.BreakLink
      Next fld
    End Sub
    [/quote]
    Hi HansV
    Thank you this should work great for breaking the links.
    Is there a way that I could first copy "Report" folder, rename it and send this new copy to a designated location that was pre selected for an achieves folder and yet still be able to break links. All this using VBA.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I think you should break the links first, then copy or move the folder. You may run into problems if you break the links after copying or moving the folder.

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

    Talking

    [quote name='HansV' post='786194' date='25-Jul-2009 08:03']I think you should break the links first, then copy or move the folder. You may run into problems if you break the links after copying or moving the folder.[/quote]
    Hi HansV
    If I break the links first before I copy the folder will that not affect the original folder that is staying in same location.

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Ah, I misinterpreted your intention, I thought you eventually wanted to move the folder.

    The following code can be run from Excel (or any application with VBA), it uses Automation to run Word.
    [codebox]
    Sub CopyFolderAndBreakLinks()
    Dim fso As Object
    Dim wrdApp As Object
    Dim wrdDoc As Object
    Dim strFile As String

    ' Modify the paths as needed
    Const strSource = "C:\Home Inspection\Reports"
    Const strTarget = "C:\Archive"

    On Error GoTo ErrHandler

    ' Copy the folder
    Set fso = CreateObject("Scripting.FileSystemObject")
    If Not fso.FolderExists(strTarget) Then
    fso.CopyFolder strSource, strTarget
    End If

    ' Start Word
    Set wrdApp = CreateObject("Word.Application")
    wrdApp.ScreenUpdating = False
    ' Loop through the documents in the target folder
    strFile = Dir(strTarget & "\*.docm")
    Do While Not strFile = ""
    ' Open document in Word
    Set wrdDoc = wrdApp.Documents.Open(strTarget & "\" & strFile)
    ' Process it
    Call BreakLinks(wrdDoc)
    ' Save and close it
    wrdDoc.Close SaveChanges:=True
    ' On to the next one
    strFile = Dir
    Loop

    ExitHandler:
    ' Clean up
    On Error Resume Next
    wrdApp.Quit SaveChanges:=False
    Set fso = Nothing
    Set wrdDoc = Nothing
    Set wrdApp = Nothing
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub

    Private Sub BreakLinks(doc As Object)
    Dim fld As Object
    On Error Resume Next
    For Each fld In doc.Fields
    fld.LinkFormat.BreakLink
    Next fld
    End Sub
    [/codebox]
    Test carefully!

  11. #11
    3 Star Lounger
    Join Date
    Jun 2009
    Location
    Hemet CA
    Posts
    310
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='786206' date='25-Jul-2009 09:12']Ah, I misinterpreted your intention, I thought you eventually wanted to move the folder.

    The following code can be run from Excel (or any application with VBA), it uses Automation to run Word.
    [codebox]
    Sub CopyFolderAndBreakLinks()
    Dim fso As Object
    Dim wrdApp As Object
    Dim wrdDoc As Object
    Dim strFile As String

    ' Modify the paths as needed
    Const strSource = "C:\Home Inspection\Reports"
    Const strTarget = "C:\Archive"

    On Error GoTo ErrHandler

    ' Copy the folder
    Set fso = CreateObject("Scripting.FileSystemObject")
    If Not fso.FolderExists(strTarget) Then
    fso.CopyFolder strSource, strTarget
    End If

    ' Start Word
    Set wrdApp = CreateObject("Word.Application")
    wrdApp.ScreenUpdating = False
    ' Loop through the documents in the target folder
    strFile = Dir(strTarget & "\*.docm")
    Do While Not strFile = ""
    ' Open document in Word
    Set wrdDoc = wrdApp.Documents.Open(strTarget & "\" & strFile)
    ' Process it
    Call BreakLinks(wrdDoc)
    ' Save and close it
    wrdDoc.Close SaveChanges:=True
    ' On to the next one
    strFile = Dir
    Loop

    ExitHandler:
    ' Clean up
    On Error Resume Next
    wrdApp.Quit SaveChanges:=False
    Set fso = Nothing
    Set wrdDoc = Nothing
    Set wrdApp = Nothing
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub

    Private Sub BreakLinks(doc As Object)
    Dim fld As Object
    On Error Resume Next
    For Each fld In doc.Fields
    fld.LinkFormat.BreakLink
    Next fld
    End Sub
    [/codebox]
    Test carefully![/quote]
    Hi HansV
    Awesome this looks like what I needed
    Thank you

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

    Talking

    [quote name='ababenchrist' post='786209' date='25-Jul-2009 09:26']Hi HansV
    Awesome this looks like what I needed
    Thank you[/quote]
    Hi HansV
    I used caution as you mentioned with this code because of the fact of all my link connections. I wasn’t able to check out this code until today. Let me first start off with saying that, the code to remove the links works beautiful, it removed all links from the original file folder. But it was unable to send a copy to selected location.

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    The code in my previous reply first copies the Reports folder, then breaks the links in the documents in the target folder. It shouldn't do anything with the documents in the original Reports folder!

    If the code fails at the point where it tries to copy the Reports folder, it shouldn't even attempt to break links. So I don't understand what happened to you.

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

    Talking

    [quote name='HansV' post='786305' date='26-Jul-2009 07:37']The code in my previous reply first copies the Reports folder, then breaks the links in the documents in the target folder. It shouldn't do anything with the documents in the original Reports folder!

    If the code fails at the point where it tries to copy the Reports folder, it shouldn't even attempt to break links. So I don't understand what happened to you.[/quote]
    Hi HansV
    I got this to work just as you mentioned. I was having a problem at first, I’m guessing because I made a copy of the original and relocated the copy which in turn caused my linking problems. So when I adjusted the code to fit the location and gave it a try, that when I noticed that it developed an error to the links.
    Once I realized my mistake and corrected it, it seem to take a while for it to break the links, is that common.

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Breaking links can be slow, Word has to do a lot of work behind the scenes to convert the data.

Posting Permissions

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