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

    Unhappy

    Iím using Office 2007, from an Excel worksheet, using a command button I would like to have it copy 5 different/separate word.docm (new unused copies) from one sub folder(Inspection Reports New) and transfer them to another sub folder (Inspection Reports) for replacement of existing used word.docm. Both folders and workbook are in same sub folder. Also add protection to Documents, allowing of ďfilling in formĒ. One last thing this report will be used by different people that will have this report located in different location on their computer.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Welcome to the Lounge!

    If the documents are located in different folders on different PCs, how should the macro know which folder to use?

  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='781077' date='22-Jun-2009 13:23']Welcome to the Lounge!

    If the documents are located in different folders on different PCs, how should the macro know which folder to use?[/quote]
    Hi HansV
    I search around and this is what I have found that works for my needs of course i need to add a few changes to allow more files to be changed
    Code:
    Sub CopyDocFile(ByVal filename As String)
    	Const Folder1 = "Inspection Reports", Folder2 = "Inspection Reports New"
    
    	OldPath = Replace(ThisWorkbook.FullName, ThisWorkbook.Name, Folder2 & "\")
    	NewPath = Replace(ThisWorkbook.FullName, ThisWorkbook.Name, Folder1 & "\")
    
    	If Dir(OldPath, vbDirectory) = "" Then MsgBox "Folder  " & OldPath & "  not found", vbCritical, "Error": Exit Sub
    	If Dir(NewPath, vbDirectory) = "" Then MsgBox "Folder  " & NewPath & "  not found", vbCritical, "Error": Exit Sub
    
    	If Dir(OldPath & filename) = "" Then MsgBox "File  " & filename & "  not found", vbCritical, "Error": Exit Sub
    
    	FileCopy OldPath & filename, NewPath & filename
    End Sub
    
    
    Sub Example()
    	CopyDocFile "Word.docx"
    	CopyDocFile "Microsoft Word 2.docx"
    End Sub

Posting Permissions

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