Rename (and move) files based on MS Access table with VBA (1 Viewer)

TB11

Member
Local time
Today, 15:15
Joined
Jul 7, 2020
Messages
78
Hi. I have a table with a field for the temporary file name, which includes the original (temporary) folder path, the name of the document and the file extension (TempPicturePathName) and another field with the new folder path and new name (FinalPicturePathName; the final folder path is different than the original folder path).

I'm stuck at the Name function (line 24 of code), as the debugger does not like Name OldPathName As New PathName

Some further info: table is named tblTempPictures; field with old path name is TempPicturePathName (sample of field contents: C:\Users\T\Documents\My Databases\RenamePlay\A.pdf); field with new path name = FinalPicturePathName (sample of C:\Users\T\Documents\My Databases\RenamePlay\AsRENAMED\Test_A.pdf) --I know a pdf is not a picture, I'm just using these names to follow along with code - I'll change the table name and fields later.

Code:
Private Sub cmdRename_Click()

'This code MAY FAIL IF THE USER DOES NOT HAVE WRITE PERMISSION TO THE FOLDER THE DOCUMENTS ARE TO BE MOVED TO
'With Loop through recordset

Dim i As Integer

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim tbl As String

Set myObject = New Scripting.FileSystemObject
Set db = CurrentDb
tbl = "tblTempPictures"
Set rs = db.Openrecordset("tblTempPictures")

For i = 0 To rs.RecordCount - 1
Dim OldPathName As String
Dim NewPathName As String

    OldPathName = rs.Fields("TempPicturePathName")
    NewPathName = rs.Fields("FinalPicturePathName")

Name OldPathName As NewPathName
  
    rs.MoveNext

Next i

Counter = Counter + 1
  
    ' Clean up
    rs.Close
    db.Close
    Set rs = Nothing
    Set db = Nothing
    
    Set myObject = Nothing
    Set mySource = Nothing
    
    MsgBox "Listed " & Counter & " Files in Table."

rs.Close
Set rs = Nothing
db.Close

End Sub
 

LarryE

Active member
Local time
Today, 13:15
Joined
Aug 18, 2021
Messages
563
If you want to change the OldPathName to the NewPathName then
rs.Fields("TempPicturePathName") = rs.Fields("FinalPicturePathName")
ACCESS doesn't know what Name OldPathName As NewPathName is
 

TB11

Member
Local time
Today, 15:15
Joined
Jul 7, 2020
Messages
78
@LarryE I'm trying to use the Name function to rename those files in a file folder on my computer, and move them to a new folder on my computer using the fields from MS Access table.

I tried your suggestion, and it did not work.
 

TB11

Member
Local time
Today, 15:15
Joined
Jul 7, 2020
Messages
78
Further note: While the debugger stopped at OldPathName As NewPathName, it did rename and move 1 of the 27 files before I got the error Run-time error 53, file not found.
 

conception_native_0123

Well-known member
Local time
Today, 15:15
Joined
Mar 13, 2021
Messages
1,826
if it worked on one file, then obviously your code is written correctly. one such reason for a file not found error in this case is that there is a space in the file path name of either field. one other reason is the obvious. the file listed is not in the place where it should be, thus it wasn't found.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 15:15
Joined
Feb 28, 2001
Messages
27,001
Concur with Adam. If it works once, take a good look at the records where it fails. I don't see a glaring error in the code you showed us. I also noted that you went to the trouble of opening a FileSystemObject but then never used it.

Look at the FSO method MoveFile, which is another way to do what you want - and it is capable of moving the file across drives.


Note that restrictions apply to the "Name X As Y" construct:

 

Users who are viewing this thread

Top Bottom