Excel Forum

Code: Select all

Public Sub CopyFileToNewDes()

    Dim strSQL As String
    Dim rst As DAO.Recordset
    Dim strNewPath As String
    Dim FSO As Object
           
    strSQL = "SELECT [sFol] & [FileName] AS FullPath, temCopyList.FileName " & _
                "FROM temCopyList INNER JOIN sFolder ON temCopyList.ID = sFolder.CopyListID;"
    Set rst = CurrentDb.OpenRecordset(strSQL)
    
    Do While Not rst.EOF
            Set FSO = CreateObject("Scripting.FileSystemObject")
            strNewPath = "C:\tempDwg\"
            FSO.CopyFile (rst!FullPath), strNewPath & rst!FileName, True 'Copy and paste file to destination folder
            Set FSO = Nothing
        rst.MoveNext
    Loop
    
    MsgBox "Move File Successful", vbInformation
    rst.Close
    Set rst = Nothing
    
End Sub