Copy File from One Folder to Another

[feinj]

Hello all,

I need to create a function that I can launch using a macro that will copy an excel file from one folder to another in the file system.

Can anyone help please???

 

[Isskint]

One way is to use FileSystemObject

Dim fs As Object
Dim oldPath As String, newPath As String
oldPath = "C:\Documents and Settings\user\My Documents\filename.xls" 
newPath = "C:\Documents and Settings\user\My Documents\Misc\filename.xls"
Set fs = CreateObject("Scripting.FileSystemObject")
fs.CopyFile oldPath , newPath 
Set fs = Nothing

 

[feinj]

I tried applying your code in a public function and when I launch the function from a macro I get the error message

"The expression you entered has a function name that Microsoft Office can't find"

function code I created so far is shown below:

Public Function CopyFile() As Boolean
Dim fs As Object
Dim oldPath As String, newPath As String
oldPath = "Y:\SpecialityProducts\Ormco\Fein\DailyBOTest\Backorder Detail ATP Report.XLS"
newPath = "Y:\SpecialityProducts\Ormco\Fein\DailyYesterdayBOTest\Backorder Detail ATP Report.XLS"
Set fs = CreateObject("Scripting.FileSystemObject")
fs.CopyFile oldPath, newPath
Set fs = Nothing
End Function

 

[feinj]

I figured out the problem. I renamed the function to FileCopy, rather than CopyFile and it worked just fine. Thanks for the help.

 

[Isskint]

That is probably due to not having the necessary library registered. "Scripting.FileSystemObject" requires that you have set a reference to the Microsoft Scripting Runtime. To do this (or check it) do the following;

From the VB editor select Tools>>References. In the box set references will be ticked and displayed at the top. If you do not see Microsoft Scripting Runtime at the top, scroll down to find it at tick it. Close the box and try the code again.