Copy workbook to another location without opening

[leeloo0505]

Hello,
I am trying to copy a workbook without opening it and save it to a different location/file name (2nd workbook) from a third workbook.
Anybody got any ideas?
Thanks,
leeloo

 

[TommyGun]

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> CopyFile()

FileCopy Source:="C:\Book3.xls", Destination:="C:\VBA Code\Book5.xls"

<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

 

[leeloo0505]

Excellent, thank ya!

 

[bamaisgreat]

How could this be adjusted to write over the destination file each time but as the same file name.. Thanks

 

[VoG]

Try like this

Sub CopyFile()
On Error Resume Next
Kill "C:\VBA Code\Book5.xls"
On Error GoTo 0
FileCopy Source:="C:\Book3.xls", Destination:="C:\VBA Code\Book5.xls"
End Sub

 

[bamaisgreat]

Peter if another user has open book5 when the code is executed will it still replace it even though its open.

 

[bamaisgreat]

If i want to use the Active Workbook could this work ?

On Error Resume Next
Kill "H:\All\Material Prep Archive\(Public)Archive .xls"
On Error GoTo 0
FileCopy Source:="Activeworkbook:="H:\All\Material Prep Archive\(Public)Archive .xls"

 

[VoG]

Peter if another user has open book5 when the code is executed will it still replace it even though its open.

I think that would fail - you'd have to test to be sure.

 

[VoG]

If i want to use the Active Workbook could this work ?

I think that you would need

On Error Resume Next
Kill "H:\All\Material Prep Archive\(Public)Archive .xls"
On Error GoTo 0
FileCopy Source:=ActiveWorkbook.FullName, Destination:="H:\All\Material Prep Archive\(Public)Archive .xls"

 

[bamaisgreat]

If i add the On Error Resume Next above the file copy will it skip the FileCopy if the destination (PublicArchive.xls) is open?
Like Below

On Error Resume Next
Kill "H:\All\Material Prep Archive\(Public)Archive .xls"
On Error GoTo 0
On Error Resume Next
FileCopy Source:=ActiveWorkbook.FullName, Destination:="H:\All\Material Prep Archive\(Public)Archive .xls"