Results 1 to 9 of 9
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts

    Macro to Copy data from One folder to specific Folder

    I have code to copy CSV files in a directory called "CSPACPAC which is in directory C:\CSPAYPAC to the folowinh directories depending on the first letter of the file name

    1) if the vletter starts with an E , then copy to c:\Payroll Files ECM
    2) If letter starts with an R, then copy to c:\Payroll Files Con Auto
    3) If letter starts with an D, then copy to c:\Payroll Files Combotrade
    4) If letter starts with an N, then copy c:\Payroll Files Nissan

    However when activating the macro, nothing happens-see attached workbook





    Sub Copy_Payroll_Files()


    Dim fso, f
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set f = fso.GetFolder("C:\CSPAYPAC")
    For Each file In f.Files
    Select Case Left(file.Name, 1)
    Case "E"
    fso.copyfile file, "c:\Payroll Files ECM\"
    Case "R"
    fso.copyfile file, "c:\Payroll Files Con Auto\"
    Case "D"
    fso.copyfile file, "c:\Payroll Files Combotrade\"
    Case "N"
    fso.copyfile file, "c:\Payroll Files Nissan\"
    End Select
    Next
    Set f = Nothing
    Set fso = Nothing

    End Sub
    Attached Files Attached Files

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Just a guess: The Case is "case senstive" a file starting with "e" will not be thought of as "E"...
    Try the line
    Select Case UCASE(Left(file.Name, 1))

    Steve

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,815
    Thanks
    132
    Thanked 479 Times in 456 Posts
    Hi Howard

    When using fso to copy a single file, if destination is a directory, an error occurs.

    Try my attached file.
    Instead of looping, it copies all files E*.csv to the required folder etc. etc.
    My short vba routine assumes the destination folders exist.
    You could test for that of course, but I kept the code simple for you.

    zeddy
    Attached Files Attached Files

  4. The Following User Says Thank You to zeddy For This Useful Post:

    galang_ofel (2013-04-23)

  5. #4
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Zeddy & Steve, thanks for your input.

    I have used the code that Zeddy provided. It works perfectly for me

  6. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,815
    Thanks
    132
    Thanked 479 Times in 456 Posts
    Hi Howard

    Just so you are clear:
    When using fso to copy a single file, if destination is a directory, an error occurs
    When using fso with wildcard(s) in the source (last part only), you can then use a destination directory.

    zeddy
    Last edited by zeddy; 2013-03-28 at 10:52.

  7. The Following User Says Thank You to zeddy For This Useful Post:

    HowardC (2013-03-28)

  8. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,815
    Thanks
    132
    Thanked 479 Times in 456 Posts
    Hi Howard

    And one final point, your original code did NOT check specifically for csv files.
    So watch out or you could end up copying all manner of files (zip, exe, doc, etc etc)

    zeddy

  9. The Following User Says Thank You to zeddy For This Useful Post:

    HowardC (2013-03-28)

  10. #7
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Zeddy

    Thanks for the further explanation. This is a great forum with people like yourself willing to help others like myself. I learn something new each day from chaps like you, Steve & others on this site

    Regards

    Howard

  11. #8
    2 Star Lounger
    Join Date
    Feb 2013
    Posts
    153
    Thanks
    8
    Thanked 14 Times in 13 Posts
    Hello Howard:

    I also noticed something else in your programming. You use c:\ for addressing. Although this is not wrong it helps to always us use uppercase C:\ when addressing files so that you can use a search for finding all of them in any program in any language and when you see it it means disk location.

  12. #9
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Thanks for the information, much appreciated

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •