[VB help needed] Copy mulit folder files to Destination based on excel
Page 2 of 4 FirstFirst 1234 LastLast
Results 11 to 20 of 36

Thread: [VB help needed] Copy mulit folder files to Destination based on excel

  1. #11
    Board Regular
    Join Date
    Apr 2010
    Location
    Singapore
    Posts
    239
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: [VB help needed] Copy mulit folder files to Destination based on excel

    omg thanks pro.
    it work great (tested on excel 2016) and it will help me alot. i had thousand of files to copy and paste, this will save my time.
    really appreciate it.

    Quote Originally Posted by Fluff View Post
    Try this
    Code:
    Sub CopyFile()
    
        Dim Cl As Range
        Dim SrcFle As String
        Dim DestFle As String
        Dim Fname As String
        
        
        For Each Cl In Range("A2", Range("A" & Rows.Count).End(xlUp))
            If Dir(Cl.Offset(, 2).Value, vbDirectory) = "" Then MkDir Cl.Offset(, 2).Value
            Fname = Cl.Value
            SrcFle = ""
            DestFle = ""
            SrcFle = Dir(Cl.Offset(, 1).Value & "\" & Fname)
            DestFle = Dir(Cl.Offset(, 2).Value & "\" & Fname)
            If Len(SrcFle) > 0 And Len(DestFle) = 0 Then
                FileCopy Cl.Offset(, 1).Value & "\" & Fname, _
                    Cl.Offset(, 2).Value & "\" & Fname
                Cl.Offset(, 3).Value = "Copied"                     'File was copied to new folder
            ElseIf Len(SrcFle) = 0 Then
                Cl.Offset(, 3).Value = "File not found"             'File was not found in source folder
            ElseIf Len(DestFle) > 0 Then
                Cl.Offset(, 3).Value = "File Exists"                'File already exists in destination folder
            End If
        Next Cl
        
    End Sub
    Last edited by harky; Oct 18th, 2017 at 11:03 AM.

  2. #12
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    27,029
    Post Thanks / Like
    Mentioned
    460 Post(s)
    Tagged
    46 Thread(s)

    Default Re: [VB help needed] Copy mulit folder files to Destination based on excel

    Glad to help & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #13
    Board Regular
    Join Date
    Apr 2010
    Location
    Singapore
    Posts
    239
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: [VB help needed] Copy mulit folder files to Destination based on excel

    Hi Fluff..

    Really thank your help but i notice my file naming had some issue.

    e.g
    My filename is named as
    130_2_8_xxxx.PDF
    132_2_8_xxxx.PDF
    32_2_8_xxxx.PDF
    145_2_8_xxxx.PDF

    Under column A (Filename), will it possible to to recognise the first 3 letter? anything after _ will be ignore?


    Quote Originally Posted by Fluff View Post
    Glad to help & thanks for the feedback

  4. #14
    Board Regular
    Join Date
    Apr 2010
    Location
    Singapore
    Posts
    239
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: [VB help needed] Copy mulit folder files to Destination based on excel

    maybe not first 3 letter but anything after _ will be ignore.

  5. #15
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    27,029
    Post Thanks / Like
    Mentioned
    460 Post(s)
    Tagged
    46 Thread(s)

    Default Re: [VB help needed] Copy mulit folder files to Destination based on excel

    Not quite sure what you mean
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  6. #16
    Board Regular
    Join Date
    Apr 2010
    Location
    Singapore
    Posts
    239
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: [VB help needed] Copy mulit folder files to Destination based on excel

    ehh

    My PDF filename is named (below) but there are thousand of filename which is allocated with a S/N foldername.

    Folder name "
    1234" contain:
    130_1_8_1234.PDF
    132_2_8_1234.PDF
    32_3_8_1234.PDF
    145 _4_8_1234.PDF

    Folder name "9999" contain:
    130_5_8_9999.PDF
    132_1_8_9999.PDF
    32_3_8_9999.PDF
    145_5_8_9999.PDF

    For the above VB, is easy for me to set source folder but not the filename.
    the one in red, is what i hope VB can recognize and anything after _ will be ignore when copy the file even i did not key in the full filename.

    Quote Originally Posted by Fluff View Post
    Not quite sure what you mean

  7. #17
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    27,029
    Post Thanks / Like
    Mentioned
    460 Post(s)
    Tagged
    46 Thread(s)

    Default Re: [VB help needed] Copy mulit folder files to Destination based on excel

    Rather than putting the whole file name into col A just put the start (ie 130, or 130_5_8) & try this
    Code:
    Sub CopyFile()
    
        Dim Cl As Range
        Dim SrcFle As String
        Dim DestFle As String
        Dim Fname As String
        
        
        For Each Cl In Range("A2", Range("A" & Rows.Count).End(xlUp))
            If Dir(Cl.Offset(, 2).Value, vbDirectory) = "" Then MkDir Cl.Offset(, 2).Value
            Fname = Cl.Value
            SrcFle = ""
            DestFle = ""
            SrcFle = Dir(Cl.Offset(, 1).Value & "\" & Fname & "*")
            DestFle = Dir(Cl.Offset(, 2).Value & "\" & Fname & "*")
            If Len(SrcFle) > 0 And Len(DestFle) = 0 Then
                FileCopy Cl.Offset(, 1).Value & "\" & SrcFle, _
                    Cl.Offset(, 2).Value & "\" & SrcFle
                Cl.Offset(, 3).Value = "Copied"                     'File was copied to new folder
            ElseIf Len(SrcFle) = 0 Then
                Cl.Offset(, 3).Value = "File not found"             'File was not found in source folder
            ElseIf Len(DestFle) > 0 Then
                Cl.Offset(, 3).Value = "File Exists"                'File already exists in destination folder
            End If
        Next Cl
        
    End Sub
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  8. #18
    Board Regular
    Join Date
    Apr 2010
    Location
    Singapore
    Posts
    239
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: [VB help needed] Copy mulit folder files to Destination based on excel

    not able to put the whole filename as our data base only recorded the one in red.

    and if File was not found in source folder, is it possible not to create the folder?


    Quote Originally Posted by Fluff View Post
    Rather than putting the whole file name into col A just put the start (ie 130, or 130_5_8) & try this
    Code:
    Sub CopyFile()
    
        Dim Cl As Range
        Dim SrcFle As String
        Dim DestFle As String
        Dim Fname As String
        
        
        For Each Cl In Range("A2", Range("A" & Rows.Count).End(xlUp))
            If Dir(Cl.Offset(, 2).Value, vbDirectory) = "" Then MkDir Cl.Offset(, 2).Value
            Fname = Cl.Value
            SrcFle = ""
            DestFle = ""
            SrcFle = Dir(Cl.Offset(, 1).Value & "\" & Fname & "*")
            DestFle = Dir(Cl.Offset(, 2).Value & "\" & Fname & "*")
            If Len(SrcFle) > 0 And Len(DestFle) = 0 Then
                FileCopy Cl.Offset(, 1).Value & "\" & SrcFle, _
                    Cl.Offset(, 2).Value & "\" & SrcFle
                Cl.Offset(, 3).Value = "Copied"                     'File was copied to new folder
            ElseIf Len(SrcFle) = 0 Then
                Cl.Offset(, 3).Value = "File not found"             'File was not found in source folder
            ElseIf Len(DestFle) > 0 Then
                Cl.Offset(, 3).Value = "File Exists"                'File already exists in destination folder
            End If
        Next Cl
        
    End Sub
    Last edited by harky; Oct 19th, 2017 at 12:17 PM.

  9. #19
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    27,029
    Post Thanks / Like
    Mentioned
    460 Post(s)
    Tagged
    46 Thread(s)

    Default Re: [VB help needed] Copy mulit folder files to Destination based on excel

    Try
    Code:
    Sub CopyFile()
    
        Dim Cl As Range
        Dim SrcFle As String
        Dim DestFle As String
        Dim Fname As String
        
        
        For Each Cl In Range("A2", Range("A" & Rows.Count).End(xlUp))
            Fname = Cl.Value
            SrcFle = ""
            DestFle = ""
            SrcFle = Dir(Cl.Offset(, 1).Value & "\" & Fname & "*")
            If Len(SrcFle) > 0 Then
                If Dir(Cl.Offset(, 2).Value, vbDirectory) = "" Then MkDir Cl.Offset(, 2).Value
                DestFle = Dir(Cl.Offset(, 2).Value & "\" & Fname & "*")
                If Len(DestFle) = 0 Then
                    FileCopy Cl.Offset(, 1).Value & "\" & SrcFle, _
                        Cl.Offset(, 2).Value & "\" & SrcFle
                    Cl.Offset(, 3).Value = "Copied"                     'File was copied to new folder
                ElseIf Len(DestFle) > 0 Then
                    Cl.Offset(, 3).Value = "File Exists"                'File already exists in destination folder
                End If
            Else
                Cl.Offset(, 3).Value = "File not found"             'File was not found in source folder
            End If
        Next Cl
        
    End Sub
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  10. #20
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    27,029
    Post Thanks / Like
    Mentioned
    460 Post(s)
    Tagged
    46 Thread(s)

    Default Re: [VB help needed] Copy mulit folder files to Destination based on excel

    If you have, say 130, in Col A are you likely to have more than 1 file in the source folder that begins with 130?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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