File Copy - File names from Table

[jwkolker]

Hello - I have a table of file names that has fields like this

path                            filename        destination
C:\JKWORK\USATools\jpgfiles     1234.jpg        C:\upload
C:\JKWORK\USATools\jpgfiles     5678.jpg        C:\upload
C:\JKWORK\USATools\jpgfiles     a2b3_small.jpg  C:\upload

I need to run a module that will take the path and file name and copy the file to the desitination specified in the table - I suspect it will loop through and copy each file record by record.

There are thousands of files in the path directory and I only need to copy a percentage of them to the destination directory - the access table tells me which ones I need - so copying all is not an option.


Thank you.



John Kolker
Programmer

 

[M8KWR]

Ok he we go the code below is tied to a timer,
Create a form with the textboxes, path, filename and destination.

What it will do is open the form, move from record to record and copy and paste the file you want, to where you want.

If there is anything you do not understand let me know. I have not create a module etc because I have altered code i already use for something else.

Private Sub Form_Load()
Me.TimerInterval = "100"
End Sub


Private Sub Form_Timer()


Dim SourceFile, DestinationFile
Dim Check, Counter



Check = True: Counter = 0 ' Initialize variables.
Do ' Outer loop.
Do While Counter < MaxNumber ' Inner loop.
Counter = Counter + 1 ' Increment Counter.

SourceFile = path & "\" & filename ' Define source file name.
DestinationFile = destination & "\" & filename ' Define target file name.
FileCopy SourceFile, DestinationFile ' Copy source to target.



If Counter = Me.RecordsetClone.RecordCount Then ' If condition is True.
Check = False ' Set value of flag to False.
Exit Do ' Exit inner loop.

Else

DoCmd.GoToRecord , , acNext

End If
Loop
Loop Until Check = False ' Exit outer loop immediately.



End Sub

 

[jwkolker]

wow - that is great.

Thanks very much.

John Kolker
Programmer
jwkolker@comcast.net

 

[M8KWR]

a macro, why would you want to use a macro. Do you want to go through the database, and then just click a button to save move that one, and then find the next one and so one. If thats the case then us: -

SourceFile = path & "\" & filename ' Define source file name.
DestinationFile = destination & "\" & filename ' Define target file name.
FileCopy SourceFile, DestinationFile ' Copy source to target.

just put this in a command button click procedure, and it will work to same, but only move one file at a time. But make sure your text box names are the same as in the code.


The way this code works, it looks at a form that contains is linked to the table or query that you want to move these files across to.

As soon as you open the form it will run, depending on the amount of records and the size of files, it should not take that long.

The one bit of code i did forget to put in was

Me.TimerInterval = "0"

DoCmd.Close

after the loop finished, this will close down the form for you and reset the timer back to 0

 

[jwkolker]

ok - I am going to use your form method - do I point the path filename and destination to the table as the datasource?

JK

John Kolker
Programmer

 

[M8KWR]

If you need all the file that are containing in the table to be moved then yes, if not create a query with the select amount you need to move.

Then link the table or query to the new form, and create 3 text boxes Path, Filename and Destination.

Drop the code that i wrote. Save the form. and then re-open it. Be warned once you have started it is hard to stop it. It probably will not work first time, due to a spelling mistake of a control etc. Put just check everything 3 times to make sure everything is looking at what it should.

Let me know if it works

I use this method to export individual Word and PDF files.