Rename files from VBA based on a query

**zmbd** · Jan 21 '14, 12:38 PM

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** · Jan 21 '14, 06:41 PM

The basic concept is as follows:

Create a Recordset based on the Articles Table.
Loop thru the Recordset and determine if the [OldName] Field is NULL, if it is skip this Record.
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.
Assuming both conditions are True, copy the File [OldFile] to the same Folder renaming it [NewFile]

Demo Code follows to get you started, any questions feel free to ask.

Code:

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

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

Code:

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** · Jan 22 '14, 08:29 AM

Nothing happens......

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.

Attached Files

Images.zip (324.1 KB, 353 views)

**zmbd** · Jan 22 '14, 11:54 AM

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** · Jan 22 '14, 04:34 PM

The Code has been tested and is fully operational.

Is your Table named Articles and does it contain two TEXT Fields named OldName and NewName?
Does the Folder Images exist in the Root Directory of Drive C:, namely C:\Images\?
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?
Do you have corresponding NewName Values in the Articles Table for at least some of their counterparts, as in:
Code:
```
MyPicture.jpg ==> MyPicture_2.jpg
```
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?
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.

Let us know how you make out.

Code:

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** · Jan 22 '14, 05:40 PM

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** · Jan 22 '14, 07:53 PM

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:

Code:

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

**arpesystem** · Jan 23 '14, 10:33 AM

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:

Code:

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\12 45676.jpg
C:\NewImages\28 78398.jpg
C:\NewImages\13 65443.jpg
Etc.

Thanks in advance for your patience
Jose

**zmbd** · Jan 23 '14, 03:14 PM

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** · Jul 6 '18, 02:55 PM

Thank you for your help

Rename files from VBA based on a query

Rename files from VBA based on a query

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment