Rename Files

[momoko]

Hi,

I have the following files :

1_20030704_file1.dat
2_20030803_file2.dat

I want to do a global rename as the file name is not fix. In dos I can do ren *.old but how can I do it using MSAcess.

Tks,

 

[dcx693]

There are two ways I can think of doing this. First is to use run a DOS command directly from within Access using a shelled process described here: HOW TO: Determine When a Shelled Process Ends in Access 2000.

The second method is to use VBA. I've posted some sample code below:

Sub BatchRenameFiles(strDir As String, strOldExt As String, strNewExt As String)
Dim strFilename As String

 If Right(strDir, 1) <> "\" Then
  strDir = strDir & "\"
 End If

 '- Call the dir function with arguments
 strFilename = Dir(strDir & "*." & strOldExt)
 Do While strFilename <> ""
  Name strDir & strFilename As _
    strDir & Left(strFilename, Len(strFilename) - 3) & strNewExt
  '- Call the dir function again without arguments to get
  '- the next file
  strFilename = Dir
 Loop

End Sub

Call the sub like this:

Call BatchRenameFiles("C:\temp","dat","old")

 

[momoko]

Hi,

Thanks!! Pono1. How abt if it is the other way round? Meaning now I have

File1.001
File2.002
File003.003

I want to rename the filename to NEW.xxx.

Thanks,

 

[pono1]

No problem, Momoko.

You want to change the prefix, now?

Then you will have to use the Left and Mid functions rather than the Right function to scan your file name.

Regards,
Tim