Solved Code to batch copy files from one folder to another

[hubelea]

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]

Is it possible that the new computer's antivirus program is blocking the path?

 

[theDBguy]

Hi. Just a guess, but try changing this line of 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]

the question is, does the filename exists on the source folder?

 

[hubelea]

Hi. Just a guess, but try changing this line of 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]

Wish that helped, but no luck. Thanks for trying!

 

[hubelea]

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]

Hi. Does error 76 have something to do with permission? If so, have you double-checked that? Just curious...

 

[hubelea]

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]

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]

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.

 

[Dreamweaver]

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]

Same version of Office, but now Windows 10 instead of 8. Compiling the database returns no errors.

 

[Dreamweaver]

Try using FileSystemObject instead of filecopy

CopyFile method (Visual Basic for Applications)

Office VBA reference topic

docs.microsoft.com

Edit https://stackoverflow.com/questions...e-folder-to-another-using-vbscripting/1261298

mick

 

[Cronk]

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]

try changing the function:

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

 

[Dreamweaver]

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]

I moved the destination folder to a different location, updated the code, and it worked. Thanks everyone for your help!