File copying & renaming using VBA & WildCards

  • Folder "A" contains a bunch of csv

    Folder "A"

    What I'd like is VBA code to copy those files in Folder "A" named red_*.csv to Folder "B" and replace "red_"
    with "blue_" in the filenames. ie the output is:

    Folder "B"

    Many thanks, Peter.

  • Re: File copying & renaming using VBA & WildCards

    No error trapping added.

    Sub test()
    Dim fn As String
    Const myDir As String = "c:\testA\"    '<- alter here
    Const newFolder As String = "c:\testB\"  '<- alter here
    fn = Dir(myDir & "red_*.csv")
    Do While fn <> ""
        FileCopy myDir & fn, newFolder & Replace(fn, "red", "blue")
        fn = Dir
    End Sub
  • Re: File copying &amp; renaming using VBA &amp; WildCards

    jindon, Excel Samurai (I love it)

    I hope you get this. I realize the post is over 3 years old but I see you’re still active.

    Your code needs to be highlighted! I have been searching for this for days now. I don't really need to copy a file, just move it, and in certain cases rename it. I have, ... or I should say had 3 problems, 1) I need to use wildcards, 2) some files (more then half) need to be renamed and 3) I would prefer to just move, not copy, because some of these files are movies and are large (> 1GB); to copy takes too long. However, the need for wildcards over rides the time requirement.

    I modified your code as follows:

    fn = Dir(myDir & Movie_Actor_Name1 & "*") ' Movie_Actor_Name1 & "*") is old name with wildcard
    FileCopy myDir & fn, newFolder & Replace(fn, Left(fn, InStr(fn, ".") - 1), Movie_Actor_Name2) ' Movie_Actor_Name2 is the new, correct name

    I then go back into the old folder and delete the old file. At this point it's the best thing I have going. Fortunately the first line of code, fn, identifies the precise file that needs deleting.

    If you know of a way to rename the file, old or current name with wildcards, to new name known in its entirety, it would really speed things up. I know using wildcards is risky, but I'm certain I can identify a unique file. We need something like FileMove, the counterpart to FileCopy, but it only exists, to my knowledge, when using fso.MoveFile which does not allow the use of wildcards. Any ideas would be greatly appreciated

    Thanks for your code


  • Re: File copying &amp; renaming using VBA &amp; WildCards


    ..I realize the post is over 3 years old

    And the policy on the board is you do not post questions in old threads - this tends to move the threads away from the subject as defined by the title making the later information unsearchable. (Example, you are now asking to bulk delete files...)

    Please start your own thread. If necessary, you can include a link to this thread by copying the URL and pasting into your message.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!