vba to copy file - error

[arkk]

Hi all,

I am trying to copy a file from a directory using relative path in a macro. This code WORKED a couple of minutes ago and now its complaining not sure why?? any help appreciated.

Sub copyfile()
Dim fso As Object
Set fso = VBA.CreateObject("Scripting.FileSystemObject")
'Call fso.copyfile("./poc/Adata.xlsx", "./Adata1.xlsx", True) ' path not found???
Call fso.copyfile("./Adata.xlsx", "./Adata1.xlsx", True) ' file not found??
End Sub

Many Thanks.


Post Moved to relevant forum section by Mod.


.

 

[Ramesh]

It looks like the path specified in the copyfile method needs to be corrected...can you try replacing this . with the absolution path i.e. c:\abc\A.Data.xlsx etc..

 

[arkk]

absolute path works need to use relative path. The relative path WORKED before on the same code 10 min ago .. but not now

 

[jindon]

Are you in the correct directory?
See

MsgBox CurDir

 

[arkk]

hmm.. the current directory is pointing wrong (not where the excel macro book is) its some where c:\users\user\documents ... how do we set this right?

 

[jindon]

Try add 2 lines before that line

ChDrive "c:\users\user\documents"
ChDir "c:\users\user\documents"

 

[arkk]

Thanks Jindon and Ramesh. I am able to do it now with the following

Sub copyfile()
Dim fso As Object
Set fso = VBA.CreateObject("Scripting.FileSystemObject")
Set objShell = CreateObject("WScript.Shell")
objShell.CurrentDirectory = Application.ThisWorkbook.Path

Call fso.copyfile("./poc/Adata.xlsx", "./Adata1.xlsx", True)
End Sub

 

[Debaser]

Why not just specify the path in the copy since you know what it is:

Call fso.copyfile(Thisworkbook.Path & "/poc/Adata.xlsx", ThisWorkbook.Path & "/Adata1.xlsx", True)

 

[arkk]

Thanks Debaser

 

[YasserKhalil]

@Debaser : What True mean in the line you posted?

 

[Debaser]

It means that existing files should be overwritten