File Renaming Help!!

[Guest]

Hello all and thank you in advance,

Scenario:
Folder with 1000's of jpgs, and mpgs.
1 Database holds two columns for the names. One for the jpgs, one for the
mpgs.

How do I use access to change the file names?

I have to rename them to a concatenation (hope I spelled that right. ),
of various fields and make a new column with that concatenation and rename
all the files with the new file names. I have over 1,000 jpg's and mpgs to do
and definitely do not want to them manually.

Were do I start?

 

[Douglas J. Steele]

There's a Name statement in VBA:

Name oldpathname As newpathname

where oldpathname is a string expression that specifies the existing file
name and location, and newpathname is a string expression that specifies the
new file name and location. The file name specified by newpathname can't
already exist.

Both newpathname and oldpathname must be on the same drive. If the path in
newpathname exists and is different from the path in oldpathname, the Name
statement moves the file to the new directory or folder and renames the
file, if necessary. If newpathname and oldpathname have different paths and
the same file name, Name moves the file to the new location and leaves the
file name unchanged. Using Name, you can move a file from one directory or
folder to another, but you can't move a directory or folder.

 

[David Lloyd]

Maggie:

Below is some sample code for both renaming the files and updating the new
column in your table. As I do not know all of your particulars, this is
just an example. It uses the FileSystemObject to reference the appropriate
folder and iterate through all the files in the folder. You will need to
reference the Microsoft Scripting Runtime to use the FileSystemObject. This
code assumes that you have an entry in your table for each file in the
specified folder. Since I have a reference to the file, I just used the
Name property of the file to rename it, however, Doug's suggestion of using
the VBA Name Statement would also work nicely inside the loop to rename the
file.

Function RenameFiles()
Dim fso As New Scripting.FileSystemObject
Dim fld As Scripting.Folder
Dim fil As Scripting.File
Dim sOldFileName As String
Dim sNewFileName As String
Dim sExtension As String
Dim sSQL As String
Dim iPeriod As Integer

Set fld = fso.GetFolder("C:\MyFolder")

For Each fil In fld.Files
sOldFileName = fil.Name
iPeriod = InStrRev(sOldFileName, ".")
sExtension = Right(sOldFileName, Len(sOldFileName) - iPeriod + 1)
sNewFileName = Left(sOldFileName, iPeriod - 1) & "_NEW" & sExtension
'File is renamed here
fil.Name = sNewFileName
If sExtension = ".jpg" Then
sSQL = "UPDATE MyTable SET NewField ='" & sNewFileName & "'
WHERE MyJPGField = '" & _
sOldFileName & "';"
ElseIf sExtension = ".mpg" Then
sSQL = "UPDATE MyTable SET NewField ='" & sNewFileName & "'
WHERE MyMPGField = '" & _
sOldFileName & "';"
Else
'File with extension other than .jpg or .mpg
End If
CurrentDb.Execute sSQL
Next fil

Set fso = Nothing
Set fld = Nothing
Set fil = Nothing

End Function

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


message Hello all and thank you in advance,

Scenario:
Folder with 1000's of jpgs, and mpgs.
1 Database holds two columns for the names. One for the jpgs, one for the
mpgs.

How do I use access to change the file names?

I have to rename them to a concatenation (hope I spelled that right. ),
of various fields and make a new column with that concatenation and rename
all the files with the new file names. I have over 1,000 jpg's and mpgs to
do
and definitely do not want to them manually.

Were do I start?

 

[Guest]

Thank you Douglas and David!!,

I will try this out and keep you posted. wuhu

 

[Douglas J Steele]

While FSO definitely has its uses, I don't see the point of adding its
overhead in this case. It's easy enough to loop through a folder using the
Dir statement, and that seems to be the only reason you're using FSO.

Remember that every time you add a reference to your application, you add to
the probability of having a problem with the References collection!

And, FWIW, it might be easier to determine the file extension as:

sExtension = Mid(sOldFileName, iPeriod)

In fact, since all you want is .jpg or .mpg, there's really no reason to
determine iPeriod: sExtension = Right(sOldFileName, 4) would suffice.

 

[David Lloyd]

Doug:

It is easy to be a critic of someone else's work. I do not represent my
postings as being the only way to do something, or that the code has been
fully optimized in every respect. All I am trying to do, in the short time
I have available, is to show the user a way to approach the issue or issues
they have raised. In most of my postings, I use the qualifier "one
approach" or "one option," clearly indicating that there are other
approaches that can be used.

The fact is that you did not take the time to provide a complete solution to
the user's question. This is something that I did attempt to do, and of
course, in my first posting, I did not say anything about your deficiency in
this regard. Instead, I showed how to incorporate your advice into my
solution, a positive, constructive approach in my opinion.

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


While FSO definitely has its uses, I don't see the point of adding its
overhead in this case. It's easy enough to loop through a folder using the
Dir statement, and that seems to be the only reason you're using FSO.

Remember that every time you add a reference to your application, you add to
the probability of having a problem with the References collection!

And, FWIW, it might be easier to determine the file extension as:

sExtension = Mid(sOldFileName, iPeriod)

In fact, since all you want is .jpg or .mpg, there's really no reason to
determine iPeriod: sExtension = Right(sOldFileName, 4) would suffice.