Access 2010: Compare and copy files to new directory

Topic

New Reply

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.

Reply | Quote

Replies

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. :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

You are correct, the list of file names is in an Access table, tbl_FileNameList_v18 and field name is v18_FileName.

Reply | Quote

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:BEKDocsScripts"   '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 :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Thank you sooooo much, it work beautifully!!!!

Reply | Quote