Copying files selected by the user and then saving them in a nominated folder (1 Viewer)

atrium

Registered User.
Local time
Today, 23:07
Joined
May 13, 2014
Messages
348
I have a document library table , that holds all documents generated and imported for clients. The entries displayed in the clients document library is relative to that client only. The document entries in the document library don't carry the physical file only the files physical name.

I am trying to write code that will allow the user to select one to many files from a list of documents relative a Client. I display the list and the user ticks each one they want copied. Once they have completed their selection I ask for the folder they want them copied to.
Once I have the destination folder I go through the file and pick out the ticked documents and copy them to the destination folder.

I keep on getting an error 52 on the CopyFile (FileCopy) Bad file name or number

Here is the code I currently have

Code:
Private Sub SelectOKButt_Click()
 
  Me.Dirty = False
  Me.Refresh
  Me.Requery
    Dim strQryFile As String
  ' This is where we check if any of the documents has been selected to copy.
 
  If DLookup("GeneralFlag", "DocumentLibrary", "GeneralFlag  = -1") = False Then
     MsgBox "No Documents Have been selected to be Copied"
     Exit Sub
  Else
     Dim DestinationAddress, SourceAddress, SystemAddress As String
     SystemAddress = DLookup("SysAddress", "SystemPreferences", "[SysPrefId] =  1")
     SourceAddress = SystemAddress & "TOGAS\ClientMergeFiles\"
  End If

    ' What is the destination address
    '------------------------------------------------------------------------------------------------------
    Dim stExportPath, Response As String
    
    Response = MsgBox("Select the folder location for where you want to copy the files to.", vbOKOnly + vbQuestion, "Folder Location")
    If Response <> vbOK Then
       Exit Sub
    Else
       Dim DFolder As String
       Dim fd As FileDialog
       Set fd = Application.FileDialog(msoFileDialogFolderPicker)
       ' Open the select folder prompt
       With fd
           .Title = "Select a Folder"
           If .Show = -1 Then ' if OK is pressed
               DFolder = .SelectedItems(1)
           End If
       End With
       SourceAddress = SourceAddress & Me.PrecFilename
       DestinationAddress = DFolder & "\"
       If DFolder = "" Then ' if a folder was chosen
          MsgBox "A folder has not been selected"
          Exit Sub
       Else
      
          '-----------------------------------There is at least 1 document selected------------------------------------------------------------------
          strQryFile = "DocumentLibrary"
          '----------------------------------
          Dim strQryFileSql As String
          Dim strWhereStmnt As String
          Dim db As Database
          Dim RecCounter As Integer
          Dim rs As DAO.Recordset
          Dim lUserID As Long
          DBEngine.SetOption dbMaxLocksPerFile, 1000000
        
          Me.QryFile = strQryFile
          Set db = CurrentDb
          strWhereStmnt = " WHERE MatterId = " & Me.MatterIdFld & " AND GeneralFlag = " & -1 & ";"
          strQryFileSql = "SELECT * FROM " & strQryFile & strWhereStmnt          '
          Set rs = db.OpenRecordset(strQryFileSql, dbOpenDynaset)
        
        
          '----------------------------------------------------------------------------------------------
          '----------------- The LOOP starts Here -------------------------------------------------------
          RecCounter = 1
          Do While Not rs.EOF
             SourceAddress = SystemAddress & "TOGAS\ClientMergeFiles\"
             Me.DocTransId = rs![DocId]
             Me.PrecDocCodeFld = rs![PrecDocCode]
             Me.TransTypeFld = rs![TransType]
             Me.DescriptionFld = rs![Description]
            
             Me.MatterNoFld = rs![MatterNo]
             Me.MatterIdFld = rs![MatterId]
             Me.ClientIdFld = rs![ClientId]
             Me.DateCreatedFld = rs![DateCreated]
            
             Me.UserIdFld = rs![UserId]
             Me.PrecFileNameFld = rs![PrecFilename]
             Me.PrecTypeFld = rs![PrecType]
             Me.FolderNameFld = rs![FolderName]
            
             Me.AttachedFld = rs![Attached]
             Me.PaperLessFileSNoFld = rs![PaperLessFileSNo]
             Me.LastModifiedFld = rs![LastModified]
             Me.ParentFolderFld = rs![ParentFolder]
             Me.RecycledFld = rs![Recycled]
             Me.GeneralFlagFld = rs![GeneralFlag]
             ' Now copy this file to the selected destination
             SourceAddress = SourceAddress & Me.PrecFilename
             DestinationAddress = DFolder & "\"
             On Error GoTo CopyFile_Error
             FileCopy SourceAddress, DestinationAddress
            
             'FileCopy SourceAddress, DestinationAddress
             rs.MoveNext
          Loop
             MsgBox "You Files have been copied to " & DestinationAddress
              ' We have files to copy
              ' Get the destination address
       End If
    End If
CopyFile_Error:
    If err.Number = 0 Then
    ElseIf err.Number = 70 Then
        MsgBox "The file is currently in use and therfore is locked and cannot be copied at this" & _
               " time.  Please ensure that no one is using the file and try again.", vbOKOnly, _
               "File Currently in Use"
    ElseIf err.Number = 53 Then
        MsgBox "The Source File '" & SourceAddress & "' could not be found.  Please validate the" & _
               " location and name of the specifed Source File and try again", vbOKOnly, _
               "File Currently in Use"
    Else
        MsgBox "MS Access has generated the following error" & vbCrLf & vbCrLf & "Error Number: " & _
               err.Number & vbCrLf & "Error Source: CopyFile" & vbCrLf & _
               "Error Description: " & err.Description, vbCritical, "An Error has Occurred!"
    End If
End Sub


I hope someone can help me

Have a great day
 

Minty

AWF VIP
Local time
Today, 12:07
Joined
Jul 26, 2013
Messages
10,355
Add a

Debug.Print SourceAddress, DestinationAddress

before your Filecopy line and see what you are actually getting.
I suspect you have a Folder not a file name in there.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:07
Joined
May 7, 2009
Messages
19,175
check this.
also, you don't have Me.PrecFilename
what you have is Me.PrecFileNameFld.
Code:
Private Sub SelectOKButt_Click()
 
  Me.Dirty = False
  Me.Refresh
  Me.Requery
    Dim strQryFile As String
  ' This is where we check if any of the documents has been selected to copy.
 
  If DLookup("GeneralFlag", "DocumentLibrary", "GeneralFlag  = -1") = False Then
     MsgBox "No Documents Have been selected to be Copied"
     Exit Sub
  Else
     Dim DestinationAddress, SourceAddress, SystemAddress As String
     SystemAddress = DLookup("SysAddress", "SystemPreferences", "[SysPrefId] =  1")
     SourceAddress = Replace$(SystemAddress & "\TOGAS\ClientMergeFiles\", "\\", "\")
  End If

    ' What is the destination address
    '------------------------------------------------------------------------------------------------------
    Dim stExportPath, Response As String
    
    Response = MsgBox("Select the folder location for where you want to copy the files to.", vbOKOnly + vbQuestion, "Folder Location")
    If Response <> vbOK Then
       Exit Sub
    Else
       Dim DFolder As String
       Dim fd As FileDialog
       Set fd = Application.FileDialog(msoFileDialogFolderPicker)
       ' Open the select folder prompt
       With fd
           .Title = "Select a Folder"
           If .Show = -1 Then ' if OK is pressed
               DFolder = .SelectedItems(1)
           End If
       End With
       If DFolder = "" Then ' if a folder was chosen
          MsgBox "A folder has not been selected"
          Exit Sub
       Else
          'arnelgp
          'redundant
          'SourceAddress = Replace$(SourceAddress & "\" & Me.PrecFilename, "\\", "\")
          'DestinationAddress = Replace$(DFolder & "\", "\\", "\")
          '-----------------------------------There is at least 1 document selected------------------------------------------------------------------
          strQryFile = "DocumentLibrary"
          '----------------------------------
          Dim strQryFileSql As String
          Dim strWhereStmnt As String
          Dim db As Database
          Dim RecCounter As Integer
          Dim rs As DAO.Recordset
          Dim lUserID As Long
          DBEngine.SetOption dbMaxLocksPerFile, 1000000
        
          Me.QryFile = strQryFile
          Set db = CurrentDb
          strWhereStmnt = " WHERE MatterId = " & Me.MatterIdFld & " AND GeneralFlag = " & -1 & ";"
          strQryFileSql = "SELECT * FROM " & strQryFile & strWhereStmnt          '
          Set rs = db.OpenRecordset(strQryFileSql, dbOpenDynaset)
        
        
          '----------------------------------------------------------------------------------------------
          '----------------- The LOOP starts Here -------------------------------------------------------
          RecCounter = 1
          Do While Not rs.EOF
             SourceAddress = Replace$(SystemAddress & "\TOGAS\ClientMergeFiles\", "\\", "\")
             Me.DocTransId = rs![DocId]
             Me.PrecDocCodeFld = rs![PrecDocCode]
             Me.TransTypeFld = rs![TransType]
             Me.DescriptionFld = rs![Description]
            
             Me.MatterNoFld = rs![MatterNo]
             Me.MatterIdFld = rs![MatterId]
             Me.ClientIdFld = rs![ClientId]
             Me.DateCreatedFld = rs![DateCreated]
            
             Me.UserIdFld = rs![UserId]
             Me.PrecFileNameFld = rs![PrecFilename]
             Me.PrecTypeFld = rs![PrecType]
             Me.FolderNameFld = rs![FolderName]
            
             Me.AttachedFld = rs![Attached]
             Me.PaperLessFileSNoFld = rs![PaperLessFileSNo]
             Me.LastModifiedFld = rs![LastModified]
             Me.ParentFolderFld = rs![ParentFolder]
             Me.RecycledFld = rs![Recycled]
             Me.GeneralFlagFld = rs![GeneralFlag]
             ' Now copy this file to the selected destination
             SourceAddress = SourceAddress & Me.PrecFileNameFld
             DestinationAddress = Replace$(DFolder & "\", "\\", "\") & Me.PrecFileNameFld
             On Error GoTo CopyFile_Error
             FileCopy SourceAddress, DestinationAddress
            
             'FileCopy SourceAddress, DestinationAddress
             rs.MoveNext
          Loop
             MsgBox "You Files have been copied to " & DestinationAddress
              ' We have files to copy
              ' Get the destination address
       End If
    End If
CopyFile_Error:
    If Err.Number = 0 Then
    ElseIf Err.Number = 70 Then
        MsgBox "The file is currently in use and therfore is locked and cannot be copied at this" & _
               " time.  Please ensure that no one is using the file and try again.", vbOKOnly, _
               "File Currently in Use"
    ElseIf Err.Number = 53 Then
        MsgBox "The Source File '" & SourceAddress & "' could not be found.  Please validate the" & _
               " location and name of the specifed Source File and try again", vbOKOnly, _
               "File Currently in Use"
    Else
        MsgBox "MS Access has generated the following error" & vbCrLf & vbCrLf & "Error Number: " & _
               Err.Number & vbCrLf & "Error Source: CopyFile" & vbCrLf & _
               "Error Description: " & Err.Description, vbCritical, "An Error has Occurred!"
    End If
End Sub
 

atrium

Registered User.
Local time
Today, 23:07
Joined
May 13, 2014
Messages
348
Minty this is what is printed
\\DC01\Togas\TOGAS\ClientMergeFiles\87207-Searcode-haleys.docx \\DC01\UserDir$\JohnHaley\Documents\CopyTo\

arnelgp the above is exactly what I'm after. I select the documents from from the selected list and move them to the given folder
 

atrium

Registered User.
Local time
Today, 23:07
Joined
May 13, 2014
Messages
348
Do I need a filename in the destination address?
 

atrium

Registered User.
Local time
Today, 23:07
Joined
May 13, 2014
Messages
348
I tried with the filename in the destination and it worked. No error
I tried it with 1 document, then many - all OK

Thanks guys
 

Users who are viewing this thread

Top Bottom