Solved FileCopy Using Wildcard Not Working

[EzGoingKev]

Good evening.

I have an Excel file name "data123.xls" in one folder and want to copy it into another folder. This here works fine:

FileCopy "C:\OldFolder\data123.xls", "C:\NewFolder\data.xls"

Sometimes the data file will have a different numerical sequence. I cannot this to work:

FileCopy "C:\OldFolder\data*.xls", "C:\NewFolder\data.xls"

According to some results that came up on Google this should work. According to some other results I need to something more involved.

Should it work?

 

[theDBguy]

FileCopy "C:\OldFolder\data*.xls", "C:\NewFolder\data.xls"

Well, let's try to analyze that for a second. I don't know if it's should work or not; but, let's imagine the filespec "data*.xls" matches the following files in that folder:

data123.xls
data456.xls
data789.xls
database.xls

Now, your code is saying copy all those files to the new folder using the new filename "data.xls." Not sure that would be interpreted by the computer. Did you want to consolidate all four files into one? Or, did you want to overwrite the previous file as each one gets copied?

See what I mean?

 

[Isaac]

According to Microsoft, FileCopy statement (VBA) | Microsoft Docs, I see no mention of wildcard. Seems to require the specific filename for both arguments, to and from.

And, as dbGuy is pointing out, how could the computer respond literally to the following logic: Combine potentially three files into one ?

I think you'll need to write some additional code for the sole purpose of defining and figuring out what the source file name is going to be.
And in fact, if >1 potential matches are found, it should probably take a different decision branch--notify user, err out, etc.

 

[EzGoingKev]

There is only one file in the folder at a time.

 

[Isaac]

Then see paragraph 3 of post 3.

 

[theDBguy]

There is only one file in the folder at a time.

In that case, you might be able to use the Dir() function, which accepts wildcards, to get the actual filename and then use that result in your FileCopy command.

 

[EzGoingKev]

The big thing I do not understand is how some people said it worked fine without the need for any other functions.

 

[FrankRuperto]

Will there be more than one xls file in the source or destination folders?

If false, then why not just cut the xls file from the source folder and paste it to the destination folder with the Move command, and then rename it to data.xls?

https://docs.microsoft.com/en-us/windows-server/administration/windows-commands/move

https://docs.microsoft.com/en-us/windows-server/administration/windows-commands/ren

Move "C:\OldFolder\data*.xls", "C:\NewFolder\"

or

Move "C:\OldFolder\data???.xls", "C:\NewFolder\"

then

Ren "data*.xls", "data.xls"

 

[EzGoingKev]

In that case, you might be able to use the Dir() function, which accepts wildcards, to get the actual filename and then use that result in your FileCopy command.

I used the Dir function this way and it worked -

FileCopy "C:\OldFolder\" & Dir("C:\OldFolder\data*.xls"), "C:\NewFolder\data.xls"

I have schooling on writing VBA, I am just picking things up as I go along so I am not sure if that is how you meant for me to use the Dir function.

 

[theDBguy]

I used the Dir function this way and it worked -

FileCopy "C:\OldFolder\" & Dir("C:\OldFolder\data*.xls"), "C:\NewFolder\data.xls"

I have schooling on writing VBA, I am just picking things up as I go along so I am not sure if that is how you meant for me to use the Dir function.

That works too, but I was thinking more along these lines.

Dim strFilename As String

strFilename = Dir("C:\OldFolder\data*.xls")

If strFilename = "" Then
    MsgBox "No file found!", vbInformation, "Info"
Else
    FileCopy "C:\OldFolder\" & strFilename, "C:\NewFolder\data.xls"
End If

Good luck with your project.

 

[EzGoingKev]

Your code includes this:

Dim strFilename As String
strFilename = Dir("C:\OldFolder\data*.xls")

I have doing something like this:

strPath = Dir("C:\OldFolder\data*.xls")

but without using the Dim portion. It functions but should I be adding:

Dim strPath As String

to my code?

 

[Isaac]

You should study this and incorporate it into your vba project from now on

Option Explicit statement (VBA) | Microsoft Docs

 

[theDBguy]

Your code includes this:

Dim strFilename As String
strFilename = Dir("C:\OldFolder\data*.xls")

I have doing something like this:

strPath = Dir("C:\OldFolder\data*.xls")

but without using the Dim portion. It functions but should I be adding:

Dim strPath As String

to my code?

Hi. The Dir() function will return a filename, not a path. That's why I called my variable that way. But, yes, you should get into the habit of declaring your variables. It's considered "best practice." If you don't, you could run into unwanted issues and/or unexpected results.

 

[arnelgp]

you can also use FSO, just don't add the filename on the Destination:

With CreateObject("Scripting.FileSystemObject")
    .CopyFile "C:\OldFolder\data*.xls", "C:\NewFolder\"
End With

 

[EzGoingKev]

Thanks for all the help guys, it is greatly appreciated.

On another note, I want to take some classes to on SQL and VBA. I was thinking SQL first with some online courses. Has anyone here taken a good online SQL class?