copyFile in visual basic for applications

  • Thread starter Thread starter Matthijs de Z
  • Start date Start date
M

Matthijs de Z

Hi,

I want to just do a simple copy action using visual basic for application in
excel.

In a directory I have some file that need to be copied by time. Most of the
files have the extinction .dat (like file1.dat, file2.fat..etc...). And
there are two files with a different name and extinction. The rest of the
file are not to be copied.

For the file with a specific name, I wanted to use this sub code:

Sub copy_my_ files()

fileSystem.FileCopy("c:\tmp\file.txt","c:\backup\file.txt")

End Sub

But when I leave the row when the fileCopy code is on, I get the following
error:"Compile error: Expected: ="
How come??

When I did a first test I probably wrote it slightly different, because I
did manage to make a copy this way. But I bumped in to another problem
then, when I tried to use this

fileSystem.FileCopy("c:\tmp\*.dat","c:\backup\*.*")
But this doesn't work at all.... :-(

Can anybody help me with these problems?
Best regards,

Matthijs
 
Try removing the ()'s from your first example.

And if you're only copying one file, VBA has a builtin statement, FileCopy:

FileCopy Source:="c:\tmp\file.txt", _
Destination:="c:\backup\file.txt"

You don't need to use File System Object.

If you want to copy lots of files with similar extensions, then FSO does work
nicer:

Option Explicit
Sub testme()

' Dim FSO As Scripting.FileSystemObject
Dim FSO As Object

Dim FromPath As String
Dim FromFileSpec As String
Dim ToPath As String

' Set FSO = New Scripting.FileSystemObject
Set FSO = CreateObject("scripting.filesystemobject")

FromPath = "C:\temp"
If Right(FromPath, 1) <> "\" Then
FromPath = FromPath & "\"
End If
FromFileSpec = "*.xls"
ToPath = "C:\my documents\excel\test"

If FSO.FolderExists(FromPath) = False Then
MsgBox "From Folder doesn't exist"
Exit Sub
End If

If FSO.FolderExists(ToPath) = False Then
MsgBox "To Folder doesn't exist"
Exit Sub
End If

FSO.CopyFile Source:=FromPath & FromFileSpec, _
Destination:=ToPath, _
overwritefiles:=True

End Sub

The commented lines would be used if you added a reference to microsoft
scripting runtime.
 
"Dave Peterson" <[email protected]> schreef in bericht

Thank you for the code. I'll go and give it a try and figure out what all
the code means. Thanks fof the effort!

Matthijs
 
Back
Top