Rename files from VBA based on a query

[arpesystem]

Hi all,
Fist of all, sorry for my bad English.

I have a database with a lot of articles. Each article has an image, but this image is stored in a folder called c:\Images. Now I need to rename all the images to a new name based in a field value of the table. i.e.: Table Articles: Fields: OldName, Newname. Then I have to rename or copy to another path all files, oldname1.jpg, oldname2.jpg, etc to newname1.jpg, newname2.jpg, etc.

I wonder if is possible to make this with VBA.
Thanks in advance.

Jose

[zmbd]

Use the oldschool NAME() (click me (^_^) )

You may find this article handy too: http://bytes.com/topic/access/insigh...i-o-statements


BTW: This is one reason one should not use "Name," "Dir," "Date," etc.. as field names:
Access 2007 reserved words and symbols
AllenBrowne- Problem names and reserved words in Access

[ADezii]

The basic concept is as follows:

1. Create a Recordset based on the Articles Table.
2. Loop thru the Recordset and determine if the [OldName] Field is NULL, if it is skip this Record.
3. If the [OldName] Field is not NULL, see if the File exists in the C:\Images Path and that the [NewName] Field is not NULL.
4. Assuming both conditions are True, copy the File [OldFile] to the same Folder renaming it [NewFile]
5. Demo Code follows to get you started, any questions feel free to ask.
   

   Const conIMAGE_PATH As String = "C:\Images\"
   Dim MyDB As DAO.Database
   Dim rst As DAO.Recordset
    
   Set MyDB = CurrentDb
   Set rst = MyDB.OpenRecordset("SELECT * FROM Articles", dbOpenForwardOnly)
    
   With rst
     Do While Not .EOF
       'Does the File exist in C:\Images and is there a NewName for the File?
       If Dir(conIMAGE_PATH & ![OldName]) <> "" And Not IsNull(![NewName]) Then
         'Copy Source to Destination essentially making a Copy of the File ([OldName]
         'in the same Folder but under a New Name ([NewName])
         FileCopy conIMAGE_PATH & ![OldName], conIMAGE_PATH & ![NewName]
       End If
         .MoveNext
     Loop
   End With
    
   rst.Close
   Set rst = Nothing

6. Given the following Articles Table only Files 1.bmp, 4.bmp, and 5.bmp will be copied/renamed.
   

   ID    OldName    NewName
   1     1.bmp      One.bmp
   2     2.bmp    
   3                Three.bmp
   4     4.bmp      Four.bmp
   5     5.bmp      Five.bmp
   6     6.bmp    
    

[arpesystem]

Hi,

I've made your solution but nothing happens. If you want, I send you a example db with some photos to try.

Thanks a lot.

Regards.

Jose.

[zmbd]

Please understand, many of us cannot or will not d/l un-requested attachments. This has absolutely nothing to do with who you are... In my case, such d/l are prohibited by my ITSec staff. For many others, it is part of "safe computing/best practices" not to d/l such files. A practical example as to why can be found here http://bytes.com/topic/access/answer...l-ms-products. this unfourtunate Member opened a file from a trusted source and is now haveing all sorts of issues!

Instead, please do the following basic troubleshooting outlined here:[*]> Before Posting (VBA or SQL) Code

You may have to repeat the compile step many times.

Once you have an error free code, please post it as described in the link.

[ADezii]

The Code has been tested and is fully operational.

1. Is your Table named Articles and does it contain two TEXT Fields named OldName and NewName?
2. Does the Folder Images exist in the Root Directory of Drive C:, namely C:\Images\?
3. Does the C:\Images\ Folder contain Image Files and do their Base Names match exactly with the entries in the OldName Field of the Articles Table?
4. Do you have corresponding NewName Values in the Articles Table for at least some of their counterparts, as in:
   

   MyPicture.jpg ==> MyPicture_2.jpg

5. You say that nothing happens but nothing will happen. Did you look inside the C:\Images\ Folder to see the Old Files copied to their New Names?
6. Replace the Original Code with the following which is exactly the same but does provide some Error Checking which hopefully will point you in the right direction.
7. Let us know how you make out.
   

   On Error GoTo Err_cmdCopyRename_Click
   Const conIMAGE_PATH As String = "C:\Images\"
   Dim MyDB As DAO.Database
   Dim rst As DAO.Recordset
    
   Set MyDB = CurrentDb
   Set rst = MyDB.OpenRecordset("SELECT * FROM Articles", dbOpenForwardOnly)
    
   If (rst.BOF And rst.EOF) Then
     MsgBox "There are no Files to Copy", vbExclamation, "No Files Fouond"
       Exit Sub
   End If
    
   With rst
     Do While Not .EOF
       If Not IsNull(![OldName]) Then
         'Does the File exist in C:\Images and is there a NewName for the File?
         If Dir(conIMAGE_PATH & ![OldName]) <> "" And Not IsNull(![NewName]) Then
           'Copy Source to Destination essentially making a Copy of the File ([OldName]
           'in the same Folder but under a New Name ([NewName])
           FileCopy conIMAGE_PATH & ![OldName], conIMAGE_PATH & ![NewName]
         End If
       End If
         .MoveNext
     Loop
   End With
    
   MsgBox "File Copy/Rename Operation successful", vbInformation, "Process Completed"
    
   rst.Close
   Set rst = Nothing
    
   Exit_cmdCopyRename_Click:
     Exit Sub
    
   Err_cmdCopyRename_Click:
     MsgBox Err.Description, vbExclamation, "Error in cmdCopyRename_Click()"
       If Not rst Is Nothing Then
         rst.Close: Set rst = Nothing
       End If
         Resume Exit_cmdCopyRename_Click

[arpesystem]

Thanks for your reply.
This code Works but only has changed one picture, not does the bucle.

I have to put this code on a command button into a form, don't I ?
Thanks and sorry for my "ignorance"

Jose

[ADezii]

not does the bucle

Sorry, but I have no idea as to what you are talking about.

If it worked for one Picture then you probably do not have Values in the NewName Field for the corresponding OldName Values. You obviously cannot Copy and Rename a File if its New Name is not listed in the Articles Table unless you have some Default Renaming process inplace such as:

Base File name for [OldName] & "_2" & ".Extension"

[arpesystem]

Hi,

I will try to explain in other words:

I have an Access database with a table containing 3 fields: Student, OldExped, NewExped.
Also I have a folder where the images are stored (c: \ Images), which is now called as OldExp data field. For example:

Table STUDENTS:  
Student            OldExped    NewExped
Smith, John    3567        1245676
Martin, Adam    3568        2878398
Presley, Elvis    3893        1365443
Etc.

C:\Images\3567.jpg
C:\Images\3568.jpg
C:\Images\3893.jpg
Etc.
So I need to put a command button on a form that runs a code to copy all the images in the folder c: \ Images to another folder c: \ NewImages and renowned as the NewExped field, considering the current folder almost 1000 images. This should be the result

C:\NewImages\1245676.jpg
C:\NewImages\2878398.jpg
C:\NewImages\1365443.jpg
Etc.

Thanks in advance for your patience
Jose

[zmbd]

OK, would have been nice to know upfront that your data had only the file name without extension.

The way you worded your posts, it was implied that the field had the entire path stored.

ADezii has given you plenty of code to work with and an example of how to format the string.

What we need you to do is post your code, formatted with the [CODE/] button (it places the [code] [/code] tags around the script).

[Sophearakpanha]

Thank you for your help