VBA copy self to other location

I have an Excel-Macro in VBA in which I want to copy the file from where the macro is executed into another location.

I tried it like this

Call FileCopy(currentDir & "\" & Filename, _
otherDir & "\" & Filename)

But I get an Access restricted Exception, although I have full access to all of the directories involved. Is it because I'm trying to "copy myself"? Is 开发者_StackOverflow社区this possible? If not, could I build a workaround?

Try using

ThisWorkbook.SaveCopyAs otherDir & "Test1"

or

ThisWorkbook.SaveAs otherDir & "Test2"

ThisWorkbook refers to the workbook which contains the macro you are running...

Update : This should work for you to create a folder...

Make sure you add "Microsoft Scripting Runtime" under Tools -> references.

 Dim fso As FileSystemObject
 Set fso = New FileSystemObject
 fso.CreateFolder ("C:\test\test2")
 ThisWorkbook.SaveCopyAs "c:\test\test2\ttt.xlsm"

Using FileCopy didnt work for me either but using CopyFile from FileSystemObject seems to work.

First you will need to add a Reference (Menu: Tools->References) to the Microsoft Scripting Runtime and then use the FileSystemObject

Dim fso As FileSystemObject
Set fso = New FileSystemObject

fso.CopyFile currentDir & "\" & Filename, otherDir & "\" & Filename, True
''the last parameter decides weather or not you want to overwrite existing files

Set fso = Nothing

Alternative: Save the document at the new destination and then save it back.

ThisWorkbook.SaveAs otherDir & "\" & Filename
ThisWorkbook.SaveAs currentDir & "\" & Filename