Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access 2010: Compare and copy files to new directory

    Good Morning,I have a list of file names and I would like assistance with VBA code to compare that list to a specific folder if there is a match, copy and paste to a destination folder, is this possible? If so, can someone help me with a jump start. I'm thinking I will need to use File Scripting.Thanks so much in advance.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Roberta,

    Are we to assume the list of file names are stored in am Access table?

    If so, What is the table name and field name of the file names.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts
    You are correct, the list of file names is in an Access table, tbl_FileNameList_v18 and field name is v18_FileName.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Roberta,

    This should do the trick:
    Code:
    Sub CopyFilesInList()
    
       Dim rs       As DAO.Recordset
       Dim zSrcDir  As String
       Dim zDestDir As String
       Dim iCopyCnt As Integer
       
       zSrcDir = "G:\BEKDocs\Scripts"   'Where are the existing files located
       zDestDir = "G:\Test"             'Where do you want the files to go!
       
       Set rs = CurrentDb.OpenRecordset("SELECT * FROM tbl_FileNameList_v18")
       iCopyCnt = 0
       
       'Check to see if the recordset actually contains rows
       If Not (rs.EOF And rs.BOF) Then
        rs.MoveFirst 'Unnecessary in this case, but still a good habit
        Do Until rs.EOF = True
            
          On Error GoTo NextFile
          FileCopy zSrcDir & "\" & rs!v18_FileName, zDestDir & "\" & rs!v18_FileName
          iCopyCnt = iCopyCnt + 1
    
    NextFile:
          rs.MoveNext   'Move to the next record.
        
        Loop
       Else
        MsgBox "There are no records in the recordset."
       End If
    
       MsgBox "There were " & iCopyCnt & " files copied."
    
       rs.Close 'Close the recordset
       Set rs = Nothing 'Clean up
       
    End Sub    '*** CopyFilesInList ***
    Adjust the Source & Destination paths.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you sooooo much, it work beautifully!!!!
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

Posting Permissions

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