CODEWITHSUNDEEP
vbaexcel
John Berry
John Berry

Reputation: 21

copy folder based on cell

Would it be possible to copy folder to another folder using EXCEL-VBA?

example here

enter image description here

If i entered the v in the example in D9 and fill the target path in E3 then it will copy the folder in E:\DATABASE2012Data2012 along the file inside to the Target folder F:\Workdata

So basically what I need is to input the target path in E3 and just need to insert v in D7 or/and/to D9

Is it possible to do this with VBA?

Upvotes: 2

Views: 1160

Answers (1)

Our Man in Bananas
Our Man in Bananas

Reputation: 5981

You can do this Old School Style by using the Command Interpreter XCOPY command:

' VBA Code to open a command window and run XCOPY
Shell Environ("comspec") & " /k xcopy " & Range("E9").text & " " & Range("F9").text & " /s /e /i"

so what that does is open a Command window, and execute the XCOPY command to copy the folder in Range E9 to the new directory specified in F9.

/k - this will keep the Command window open - useful for testing

/c - this will close the command window when it's finished.

There are numerous switches you can use with XCOPY, detailed on Microsoft - XCOPY Switches

alternatively you could use FILECOPY as detailed on Ron De Bruin: VBA FileCopy and Ron De Bruin: VBA FolderCopy

if you have Excel 2010-2013, you can use the new CopyFolder method

yet another option is to use the FILESYSTEMOBJECT - CopyFolder method (ex: FileSystemObject.CopyFolder "c:\mydocuments\letters*", "c:\tempfolder\")

HTH

Philip

Upvotes: 3

Related Questions