Solved Code to batch copy files from one folder to another (2 Viewers)

hubelea

Registered User.
Local time
Today, 11:45
Joined
Nov 9, 2011
Messages
33
Thanks to a lot of help from this forum over the years I have a form with a button that copies files listed on the form from one folder to another on my computer. Was working perfectly until I got a new computer. I made absolutely sure that the file locations for the files I need to copy stayed the same, along with the destination folder. But now my code isn't working. I am not a code expert, I had a lot of help with this. Can someone look it over and see what could possibly be happening? I'm getting 'the path could not be found' message. The field that contains the file path is called PDFLocation, and here's an example of a file path:
C:\Users\Anne\Dropbox\Account Specific\Kroger\0 11110 02817 4 Kroger Greek Nonfat Yogurt Plain 32oz.pdf. It may be important to note that in the error message the path that could not be found is shown as '0 11110 02817 4 Kroger Greek Nonfat Yogurt Plain 32oz.pdf' without the start of the path.

Private Sub CopyPDFcmd_Click()
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
Do Until rs.EOF
CopyMyPdf (rs!PDFLocation)
rs.MoveNext
Loop
Set rs = Nothing
End Sub
Private Sub CopyMyPdf(strFile As String)
Dim Source As String
Dim Destination As String
Dim strName As String
Source = strFile
strName = Mid(Source, InStrRev(Source, "\") + 1)
Destination = "C:\Users\Anne\Dropbox\SubmissionPDFs\" & strName
On Error GoTo ErrorHandler
FileCopy Source, Destination
ErrorHandler:
If Err.Number = 76 Then
MsgBox "The path '" & strName & "' could not be found. Please validate the" & _
" location and name of the specifed file and try again."
End If
End Sub
 

hubelea

Registered User.
Local time
Today, 11:45
Joined
Nov 9, 2011
Messages
33
Is it possible that the new computer's antivirus program is blocking the path?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:45
Joined
Oct 29, 2018
Messages
18,788
Hi. Just a guess, but try changing this line of code:
Code:
Destination = "C:\Users\Anne\Dropbox\SubmissionPDFs\""" & strName & """"
Hope that helps...

PS. Come to think of it, you might also have to do the same with strSource.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:45
Joined
May 7, 2009
Messages
16,116
the question is, does the filename exists on the source folder?
 

hubelea

Registered User.
Local time
Today, 11:45
Joined
Nov 9, 2011
Messages
33
Hi. Just a guess, but try changing this line of code:
Code:
Destination = "C:\Users\Anne\Dropbox\SubmissionPDFs\""" & strName & """"
Hope that helps...

PS. Come to think of it, you might also have to do the same with strSource.
 

hubelea

Registered User.
Local time
Today, 11:45
Joined
Nov 9, 2011
Messages
33
Wish that helped, but no luck. Thanks for trying!
 

hubelea

Registered User.
Local time
Today, 11:45
Joined
Nov 9, 2011
Messages
33
the question is, does the filename exists on the source folder?

Yes, absolutely, there are a list of files and all of them are the exact file location. I right clicked on the file, copied the file location, and pasted it into the PDFLocation field.

I took out the error handling code and just got the runtime error 76, which allowed me to see the issue is in the line FileCopy Source, Destination. Hovering over the source it reads the correct source, and hovering over the destination it reads the correct destination.

Something is stopping the code from working, but I'm not sure what. Still works on the old computer, just doesn't work on this new one.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:45
Joined
Oct 29, 2018
Messages
18,788
Hi. Does error 76 have something to do with permission? If so, have you double-checked that? Just curious...
 

hubelea

Registered User.
Local time
Today, 11:45
Joined
Nov 9, 2011
Messages
33
It could have something to do with folder permission - our IT policies have this new computer locked up tight. But I'm not sure what to check.....
 

Cronk

Registered User.
Local time
Tomorrow, 02:45
Joined
Jul 4, 2013
Messages
2,660
Um, the folder C:\Users\Anne\Dropbox\SubmissionPDFs\ does exist on the new PC?

Change the error message to the following and see what the destination is
MsgBox "The path '" & Destination & .......
 

hubelea

Registered User.
Local time
Today, 11:45
Joined
Nov 9, 2011
Messages
33
Um, the folder C:\Users\Anne\Dropbox\SubmissionPDFs\ does exist on the new PC?

Change the error message to the following and see what the destination is
MsgBox "The path '" & Destination & .......
I changed to your exact suggestion (copied/pasted) and received compile/syntax error.
1581971584017.png
 

Dreamweaver

Well-known member
Local time
Today, 17:45
Joined
Nov 28, 2005
Messages
2,453
Just a thought when you installed office on your new computer did you make sure you installer the same version I.E. 32/64 Bit if you have any api calls this can happen try compiling you project and see where it errors out.

hope it helps

mick
 

hubelea

Registered User.
Local time
Today, 11:45
Joined
Nov 9, 2011
Messages
33
Same version of Office, but now Windows 10 instead of 8. Compiling the database returns no errors.
 

Cronk

Registered User.
Local time
Tomorrow, 02:45
Joined
Jul 4, 2013
Messages
2,660
Hubelea wrote in #11
I changed to your exact suggestion (copied/pasted) and received compile/syntax error.

The ellipsis (dots) were meant you were to put the rest of what you had. To write in out in full for you, replace the line with
MsgBox "The path '" & Destination & strName

Sorry to confuse you.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:45
Joined
May 7, 2009
Messages
16,116
try changing the function:
Code:
Private Sub CopyMyPdf(strFile As String)
    Dim Source As String
    Dim Destination As String
    Dim strName As String
   
    Source = strFile
   
    strName = Mid(Source, InStrRev(Source, "\") + 1)
   
    Destination = "C:\Users\Anne\Dropbox\SubmissionPDFs\" & strName
    On Error GoTo ErrorHandler
   
    'arnelgp
    FileCopy Source, Replace(Destination, " ", "~")
    If Instr(Source, " ") > 0 Then _
        Name Replace(Destination, " ", "~") As Destination
    'eoc arnelgp
   
ErrorHandler:
    If Err.Number = 76 Then
        MsgBox "The path '" & strName & "' could not be found. Please validate the" & _
        " location and name of the specifed file and try again."
    End If
End Sub
 
Last edited:

Dreamweaver

Well-known member
Local time
Today, 17:45
Joined
Nov 28, 2005
Messages
2,453
From the looks of it you are storing the full path in your table for each entry have you checked what is stored

When you created your system user did you use the exact same name as last system.

You can use FileSystemObject to check for existence I would use that with a debug.print to check all lo locations.

NOTE: I never store the full path just in case the main folder is moved don't know if others do the same
 

hubelea

Registered User.
Local time
Today, 11:45
Joined
Nov 9, 2011
Messages
33
I moved the destination folder to a different location, updated the code, and it worked. Thanks everyone for your help!
 

Users who are viewing this thread

Top Bottom