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.



Matthijs de Z wrote:
>
> 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

--

Dave Peterson
(E-Mail Removed)

"Dave Peterson" <(E-Mail Removed)> schreef in bericht
news:(E-Mail Removed)...

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

> 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.
>
>
>
> Matthijs de Z wrote:
> >
> > 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
>
> --
>
> Dave Peterson
> (E-Mail Removed)

"Dave Peterson" <(E-Mail Removed)> schreef in bericht
news:(E-Mail Removed)...

I've tested the codes and they both work perfect. Thanks a lot!

Matthijs