Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 217
  • Last Modified:

Move files based on MS Access query match

Hi, I need to move and rename PDF files if the existing filename matches a database query. My query has a result called case_num. Where the case_num matched the name of a PDF file in a known folder, I want to move it to another location and add the suffix "_original", the .pdf.

I've been struggling with this for a little while now so your help would be most appreciated.

Regards, Jon
0
jonlake
Asked:
jonlake
  • 5
  • 2
1 Solution
 
Nick67Commented:
VBA is good?

'pseudocode
'fire up a recordset of the existing filenames
'fire up a FilesystemObject
'walkdown the recordset
Do until rs.EOF
   if fs.FileExists(Path & rs!filename) then
         fs.movefile Path & rs!filename, newPathAndFileName
   End if
   rs.movenext
Loop

That's the idea, anyway.
Now, you haven't really given me enough detail to flesh more than pseudocode
Is it the logic you are struggling with, the syntax, or the whole ball of wax?
0
 
jonlakeAuthor Commented:
Hi Nick, the query (qryCase_File_Docs) has a field called case_file_num. In a folder location (e.g. C:\temp\case_files\) there are several hundred PDF files, each of which will have a name which matches a case_file_num result in the query.

I want to loop through the query and where a file exists of the same name, move it to another folder (e.g. c:\temp\case_files\renamed\), with a new file name, for example:

File c:\temp\case_file_num\123456.pdf would become c:\temp\case_file_num\newfile\123456_original.pdf

I hope this is enough to work with, thanks.
0
 
Nick67Commented:
is the value of [case_file_num] = 123456
or 123456.pdf
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Nick67Commented:
This will be very close to production code

Dim db As Database
Set db = CurrentDb

Dim rs As Recordset
Set rs = db.OpenRecordset("select * from qryCase_File_Docs;", dbOpenDynaset, dbSeeChanges)

Dim fs As Object 'our friend the filesystemobject
Set fs = CreateObject("Scripting.FileSystemObject")

Dim BuiltPath As String
Dim FinalPath As String

BuiltPath = "C:\temp\case_files\"
If fs.FolderExists(BuiltPath) = False Then
    MsgBox "The Source folder doesn't exist!"
    Exit Sub
End If

FinalPath = "c:\temp\case_files\newfile\"
If fs.FolderExists(FinalPath) = False Then
    MsgBox "The Destination folder doesn't exist!"
    Exit Sub
End If

'the loop
Do Until rs.EOF
    If fs.FileExists(BuiltPath & rs!case_file_num & ".pdf") Then 'does it exist
        fs.MoveFile BuiltPath & rs!case_file_num & ".pdf", FinalPath & rs!case_file_num & "_original.pdf" 'move it
    End If
    rs.MoveNext 'next record
Loop 'rinse and repeat

MsgBox "Done!"

Open in new window

0
 
Nick67Commented:
Note that I didn't put checking in to deal with a destination file that already exists.
If you are accidentally creating identically named files, things are going to go BANG!
0
 
jonlakeAuthor Commented:
Superb! Completely nailed it!

I've built it, tested it and it's working perfectly. It should only ever be a one-off each time so checking if a file already exists won't (shouldn't) be a problem. Thank you, thank you......clean and elegant solution.
0
 
Nick67Commented:
Glad you liked it!
Hopefully, it'll be useful going forward.
It's always nice to know 'hey I've coded something like that before!'

Nick67
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now