Using VBA to copy and save a file folder

Topic

New Reply

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.

Reply | Quote

Replies

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 Windows 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

Reply | Quote

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

Reply | Quote

Hi HansV
When i run code I receive an error

Dim fso As Scripting.FileSystemObject

User-defined type not defined

Reply | Quote

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).

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Hi HansV
I made the changes, but now I get same error different line

Dim wrd As Word.Application

Reply | Quote

Please take the trouble to read my original reply again. It’s explained there.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

Does the code in the attached text file do what you want?

Reply | Quote

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?

Reply | Quote

Did you want to create a NEW folder?

Reply | Quote

Yes Please

Reply | Quote

Try this:

Reply | Quote

WOW HansV
With all the files in this folder and all the link fields, it only took less than a minute to process.
It worked better than I could have hoped for……
Woody’s Lounge (HansV) is by far the best !!!!!!!
Your site has always been very helpful, courteous, even with my minimal experience you treat me with the up most respect.
Thank you again

Reply | Quote

Hi HansV
Everything is working great
i had a question in regards to this code. When I only enter a new folder name in the input box, it still works and locates new folder in My documents. is that suppose to happen like this. Its not a bad thing I was just wondering.

Reply | Quote

If you enter a folder name instead of a full path, the CopyFolder method will assume that the folder is intended to be a subfolder of the “current” folder, which probably is your “My Documents” folder.

Reply | Quote

Thank you HansV
I didn’t realize that you left a reply, it’s hard to tell.
I’m not exactly sure what you mean by “current” folder?
My Excel workbook, named “Reports” that uses this code is located in “Program Files”
I will test it out on a couple other computers sometime today and see what kind of results I get. If it gives you both possible options I guess that’s not a bad thing.

Reply | Quote

Back in the old days of MS-DOS, you always worked in a single directory (directory was the term for what we now call folder). This was the current directory; you used the CD (Change Directory) command to make another directory the current one.
Although you don’t often encounter it in Windows, there is still a current directory/folder, and by default it’s your My Documents folder. The CopyFolder method falls back to the current directory if you don’t specify a path explicitly.

You can see which threads have replies that you haven’t read yet by the icon:

You can also “follow” a thread, meaning that you’ll be notified of new replies. Click the button when viewing the thread.

Reply | Quote

Thank you HansV for your assistance and clarification on which threads have replies that you haven’t read yet.

Reply | Quote

Hi HansV
This code worked great on my home computer and on other computer when i gave a full path, but when I tested it on different computers and only gave it a folder name it would send folder to different locations everytime I’d save folder.
Is it possible to have a Save dialog appear (Something like Picture below) that may better assist a new user the ability to saved a this new folder in a specified path.

Reply | Quote

Picture below???

You can use the code from post #14 higher up in this thread. The user can click the “Create new folder” button to create a new folder.
You have to remove the lines

Code:

  If fso.FolderExists(strTarget) Then
	MsgBox strTarget & " already exists!", vbExclamation
	Exit Sub
  End If

because the dialog has already created the new folder at this point.

Reply | Quote

Hi HansV
I made the necessary changes and as always it works perfect. I thank you
The other method worked also, but I believe this method is a little more user friendly.
I also apologize about the picture “MISSING” I was in a hurry to get to work.

Reply | Quote