copying files with filename in table

[caosity]

Ok, I need help. First I am running Access 2000 on a Windows 7 computer, don't laugh, I know. I have a database called testing containing a table called Table1. The table consists of a single field, FileName. I am trying to code a module to look in a particular folder and copy all the files, the names of which appear in the table without suffix, from a source folder to a destination folder. Here is what I have so far:

Public Function CopyCalls()
Dim testing As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim strSource As String
Dim strDestination As String
Dim strCallPath As String
Dim strCallDest As String
Dim strCallName As String

Set testing = CurrentDb

strCallPath = "C:\Users\Richard"
strCallDest = "C:\Users\Richard\Temp\"
strSQL = "SELECT * FROM Table1;"
Set rs = testing.OpenRecordset(strSQL, dbOpenDynaset)

rs.MoveLast
rs.MoveFirst

Do
strCallName = rs!FileName
strSource = strCallPath & "\" & strCallName
strDestination = strCallDest & strCallName
FileCopy strSource, strDestination
rs.MoveNext
Loop Until rs.EOF

Set rs = Nothing
End Function

It gives an error on the line "FileCopy strSource, strDestination" that the file is not found. But the file Note, the file name in the table, is present in the source folder.

This is just an attempt to get the code working, I will be changing the necessary components to the proper table, and source and destination folders.

Thanks in advance.

 

[addy]

When you say without suffix, do you mean it doesn't have the file extension in your table?

I think this is the problem. I do a lot of this stuff with remarkably similar code to yours lol and you need to specify the file extension, i.e. the full path of the source file (and destination file).

If you don't have the file extension data in your table, you can easily write a script to look through the relevant folders and one by one, find the file extension and add this to your data.

 

[Andrzejek]

And I would strongly recomend this:

Do
    strCallName = rs!FileName
    strSource = strCallPath & "\" & strCallName
    strDestination = strCallDest & strCallName
[blue]
    Debug.Print "FileCopy " & strSource & ", " & strDestination
[/blue]
    FileCopy strSource, strDestination
    rs.MoveNext
Loop Until rs.EOF

Put the Break on the Debug line and *see* what's going on.

Have fun.

---- Andy

 

[MikeC14081972]

You'll need to include the file extension.

if you had 2 files (1 .xls and .doc) with the same name which would it move?

How about looping through the files in the source file and then checking each file present against the table and then move if it matches. That way you could remove the extension before checking.

To get the extension I would use instrrev to look for the dot rather than removing the last 4 characters. That way it will cover you against .xlsx etc going forward

 

[MikeC14081972]

post above please read source file as source folder.

I wish you could edit posts after submitting them

 

[caosity]

Thanks for the information, unfortunately, due to the way the files and folders were originally set up, before me, I don't think I can have access add the extensions. The files are named using a person's social then .med.pdf, there are two dots in the file name. We have changed the process to using dashes instead, but there are literally thousands of documents using dots.

Having said that, I fould another process to get the full name and extentions of the files and then can use that. I do, however, appreciate all the useful information.