How do I copy a file via VBA from one folder to another? Currently getting Error 424 (1 Viewer)

[Cark]

I have an Access Database which has a bit of VBA code which aims to take a file on a user's desktop and send it to a shared I: drive on the network with the filepath being saved in the database.

sPath, sFile and savePathDirectory are all set up to be Strings, however I cannot seem to be able to get it to work with the code below when I replace the terms with "sPath & sFile" and "savePathDirectory":

FileSystemObject.CopyFile "c:\mydocuments\*\R1???97.xls", "c:\tempfolder"

When I run the code with FileSystemObject.CopyFile sPath & sFile, savePathDirectory I get Run-time error 424 Object required.

When running the Debug.Print for the various terms I get:

sPath & sFile = C:\Users\Cark\Documents\Pump & Motor Issues.docx

savePathDirectory = I:\SharedFolder\TestingFolderForDatabaseCopying\32523\

Any suggestions on how to solve / a better technique to use?

 

[arnelgp]

you need to create a filesystem object first:

dim fsObject As Object
set fsObject = CreateObject("Scripting.FileSystemObject")
fsObject.CopyFile "c:\mydocuments\*\R1???97.xls", "c:\tempfolder\filename.xls"
set fsObject = Nothing

 

[theDBguy]

Hi. You could also try the VBA FileCopy statement.