Consulting

Results 1 to 9 of 9

Thread: Solved: Copy files from folder to folder according to list

  1. #1

    Solved: Copy files from folder to folder according to list

    I have a folder with .dwg files. I have a spreadsheet with a list of items, some of which but not all are in the folder. The spreadsheet does not include the file extension.
    I would like to make a macro that will go through the spreadsheet, find the matching files in the folder and copy them to another folder.
    The spreadsheet with the list will always be the same name ( a template), the searched folder will always be the same, Y:\Gould Southern Info\GA Kits\AutoCad Library, and the destination folder will always be the same, lets say Y:\Gould Southern Info\GA Kits\Temp Cad Folder.
    Any help would be appreciatted.

  2. #2
    Knowledge Base Approver VBAX Guru GTO's Avatar
    Joined
    Sep 2008
    Posts
    3,362
    Location
    Try:
    [vba]
    Option Explicit

    Sub exa()
    Dim FSO As Object
    Dim rCell As Range
    Dim rngFileNames As Range

    '// Change paths and extension to suit. //
    Const FOL_FROM As String = "D:\110609M\123109\Ch2_Lv1\"
    Const FOL_TO As String = "D:\110609M\123109\Ch1_Lv1\"
    Const EXT As String = ".txt"

    '// Change range to suit //
    Set rngFileNames = Selection

    Set FSO = CreateObject("Scripting.FileSystemObject")

    For Each rCell In rngFileNames
    '// Ensure that source file exists and that its not //
    '// already copied. //
    If FSO.FileExists(FOL_FROM & rCell.Value & EXT) _
    And Not FSO.FileExists(FOL_TO & rCell.Value & EXT) Then
    FSO.CopyFile FOL_FROM & rCell.Value & EXT, FOL_TO, False
    End If
    Next
    End Sub
    [/vba]
    Hope that helps,

    Mark

  3. #3
    Thanks for the help GTO.
    I changed the code to as follows
    [vba]Sub PullFromLibrary()
    Dim FSO As Object
    Dim rCell As Range
    Dim rngFileNames As Range

    '// Change paths and extension to suit. //
    Const FOL_FROM As String = "Y:\Gould Southern Info\GA Kits\AutoCad Library"
    Const FOL_TO As String = "Y:\Gould Southern Info\GA Kits\P.O. Assigned (Ordered) Kits\CADHolder"
    Const EXT As String = ".dwg"

    '// Change range to suit //
    Set rngFileNames = ("A1:A50")

    Set FSO = CreateObject("Scripting.FileSystemObject")

    For Each rCell In rngFileNames
    '// Ensure that source file exists and that its not //
    '// already copied. //
    If FSO.FileExists(FOL_FROM & rCell.Value & EXT) _
    And Not FSO.FileExists(FOL_TO & rCell.Value & EXT) Then
    FSO.CopyFile FOL_FROM & rCell.Value & EXT, FOL_TO, False
    End If
    Next
    End Sub
    [\vba] and get a "Type Mismatch" error at
    Set rngFileNames = ("A1:A50").
    Also what happens if there is not a corrosponding file in the folder being copied from? As I said not all of the items in the list will have a matching file in the folder.
    Also what does GTO stand for? I used to own a 67 Goat and was wondering if it had anything to do with the car.

  4. #4
    Goofed on the code tags
    [VBA]Sub PullFromLibrary()
    Dim FSO As Object
    Dim rCell As Range
    Dim rngFileNames As Range

    '// Change paths and extension to suit. //
    Const FOL_FROM As String = "Y:\Gould Southern Info\GA Kits\AutoCad Library"
    Const FOL_TO As String = "Y:\Gould Southern Info\GA Kits\P.O. Assigned (Ordered) Kits\CADHolder"
    Const EXT As String = ".dwg"

    '// Change range to suit //
    Set rngFileNames = ("A1:A50")

    Set FSO = CreateObject("Scripting.FileSystemObject")

    For Each rCell In rngFileNames
    '// Ensure that source file exists and that its not //
    '// already copied. //
    If FSO.FileExists(FOL_FROM & rCell.Value & EXT) _
    And Not FSO.FileExists(FOL_TO & rCell.Value & EXT) Then
    FSO.CopyFile FOL_FROM & rCell.Value & EXT, FOL_TO, False
    End If
    Next
    End Sub

    [/VBA]

  5. #5
    Moderator VBAX Wizard lucas's Avatar
    Joined
    Jun 2004
    Location
    Tulsa, Oklahoma
    Posts
    7,323
    Location
    You can always edit your own post and add the tags you know.
    Steve
    "Nearly all men can stand adversity, but if you want to test a man's character, give him power."
    -Abraham Lincoln

  6. #6
    Thanks Lucas, I will try to remember.

    I changed the line in question to
    Set rngFileNames = ThisWorkbook.Worksheets("Sheet1").Range("A1:A50")
    I do not get the error message but I also do not get any results.

  7. #7
    Knowledge Base Approver VBAX Guru GTO's Avatar
    Joined
    Sep 2008
    Posts
    3,362
    Location
    Hi there,

    You left out the trailing path seperators:
    '// Change paths and extension to suit. //
    Const FOL_FROM As String = "Y:\Gould Southern Info\GA Kits\AutoCad Library\"
    Const FOL_TO As String = "Y:\Gould Southern Info\GA Kits\P.O. Assigned (Ordered) Kits\CADHolder\"
    Mark

  8. #8
    One little thing can make such a difference. Thank you GTO for your help.

  9. #9
    Knowledge Base Approver VBAX Guru GTO's Avatar
    Joined
    Sep 2008
    Posts
    3,362
    Location
    You are most welcome

Posting Permissions

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