Trying to rename files in a folder from VBA

[Hatman50]

Hello,
I am using Access2003 and i am trying to rename all files in a folder
using VBA.
Is there a way to iterate thru the filenames and rename them to
something else?
So as an example...
If I have a directory called "N:\ABC Winter\Monday
and in that directory there are 2 txt files, 3 exl files and 4 pdf
files, I want to reanme each file and add a specific code such as 123_
at the front of the filename so that the original "filename.txt" would
now be "123_filename.txt" etc. Can this manipulation be done within
VBA? And if so how? I've been playing with some code but it is not
working.
Thank you

 

[Hatman50]

Hello,
I am using Access2003 and i am trying to rename all files in a folder
using VBA.
Is there a way to iterate thru the filenames and rename them to
something else?
So as an example...
If I have a directory called "N:\ABC Winter\Monday
and in that directory there are 2 txt files, 3 exl files and 4 pdf
files, I want to reanme each file and add a specific code such as 123_
at the front of the filename so that the original "filename.txt" would
now be "123_filename.txt" etc. Can this manipulation be done within
VBA? And if so how? I've been playing with some code but it is not
working.
Thank you

Additional info just given to me. And I'm really not sure that this
can be done. Please advise.
The filenames themselves will be in a Excel file with a code
associated to each filename.
i.e. Column1 has the code and column2 contains the filename.
Col1 Col2
123 AAAAAA
456 AAABBB
789 AAACCC
The Excel file does not contain the FULL filename with it's extension.
So the first file may actully be named AAAAAAscrews.txt on the hard
drive and the second file may be named AAABBBnails.xls and so on.
What has to get done is the renaming of the filenames on the Drive in
the same folder, to include the Code from Col1, so the first file gets
renamed to 123_AAAAAAscrews.txt and the second file gets renamed to
456_AAABBBnails.xls
I think this is going to be a real headache! at least for me anyway.
IFF anyone can help it will be rec'd with enormus gratitude!
Regards
Hatman

 

[Hatman50]

Additional info just given to me. And I'm really not sure that this
can be done. Please advise.
The filenames themselves will be in a Excel file with a code
associated to each filename. I will import this file into Access.
i.e. Column1 has the code and column2 contains the filename.
Col1 Col2
123 AAAAAA
456 AAABBB
789 AAACCC
The Excel file does not contain the FULL filename with it's
extension.
So the first file may actully be named AAAAAAscrews.txt on the hard
drive and the second file may be named AAABBBnails.xls and so on.
What has to get done is the renaming of the filenames on the Drive in
the same folder, to include the Code from Col1, so the first file
gets
renamed to 123_AAAAAAscrews.txt and the second file gets renamed to
456_AAABBBnails.xls
I think this is going to be a real headache! at least for me anyway.
IFF anyone can help it will be rec'd with enormus gratitude!
Regards
Hatman

 

[Douglas J. Steele]

What a nightmare! How on earth did you get stuck with such an untenable
requirement?

The VBA Dir function lets you use wildcards, so assuming you know the name
of the folder, you can use Dir(strFolder & strFile & "*.*") to return the
complete file name (note that this is only the name of the file, not the
name of the path!). However, what happens if there's both AAAAAAscrews.txt
and AAAAAArivets.xls in the same folder?

Once you know the file whose name you have to change, you can use the Name
statement to rename it.

So assuming your table is named tblFiles, with columns Prefix and
PartFileName, and you're looking in folder C:\MyFolder, you can use
something like:

Dim rs As DAO.Recordset
Dim strFolder As String
Dim strFile As String
Dim strSQL As String

strFolder = "C:\MyFolder\"
strSQL = "SELECT Prefix, PartFileName FROM tblFiles"
Set rs = CurrentDb.OpenRecordset(strSQL)
Do While rs.EOF = False
strFile = Dir(strFolder & rs!PartFileName & "*.*")
Do While Len(strFile) > 0
Name strFolder & strFile As strFolder & rs!Prefix & "_" & strFile
strFile = Dir
Loop
rs.MoveNext
Loop
rs.Close
Set rs = Nothing

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
Co-author: Access 2010 Solutions, published by Wiley
(no e-mails, please!)


Additional info just given to me. And I'm really not sure that this
can be done. Please advise.
The filenames themselves will be in a Excel file with a code
associated to each filename. I will import this file into Access.
i.e. Column1 has the code and column2 contains the filename.
Col1 Col2
123 AAAAAA
456 AAABBB
789 AAACCC
The Excel file does not contain the FULL filename with it's
extension.
So the first file may actully be named AAAAAAscrews.txt on the hard
drive and the second file may be named AAABBBnails.xls and so on.
What has to get done is the renaming of the filenames on the Drive in
the same folder, to include the Code from Col1, so the first file
gets
renamed to 123_AAAAAAscrews.txt and the second file gets renamed to
456_AAABBBnails.xls
I think this is going to be a real headache! at least for me anyway.
IFF anyone can help it will be rec'd with enormus gratitude!
Regards
Hatman

 

[Hatman50]

What a nightmare! How on earth did you get stuck with such an untenable
requirement?

Thanks for recognizing that!
However luckily "They" tell me that there shouldn't be any duplicate
filnames as you suggested. We'll see...
Other than that...THANK YOU SO MUCH! for the code snippet. I was
stuggling with the logic to get this working and your code solved it.
I incorporated it into my existing code and all is working fine.
Again, your assistance is much appreciated Doug. Thanks!

 

[baldmosher]

I appreciate this has been solved already so sorry for hijacking, but the above solution was a bit too complicated for my own needs and I'm guessing others might come here via Google like I did. So here's my simplified version!

I use this solution to rename music tracks into a standard format for my in-car USB player. This example renames all files in a folder from "Filthcast001.mp3" to "001 Filthcast.mp3" -- it should have enough clues to get you started.

Sub testrenamefolderfiles()
'This example renames all files in a folder from "Filthcast001.mp3" to "001 Filthcast.mp3"

Const fdr As String = "e:\Filthcast\"
Dim oldnm As String, newnm As String

oldnm = Dir(fdr & "*.*")
Do While oldnm <> ""
'deduce new fn from old fn
    newnm = Mid(oldnm, 10, 3) & " Filthcast.mp3"
'rename the file, will move instead if you specify two different fdrs
    Name fdr & oldnm As fdr & newnm
    oldnm = Dir
Loop

End Sub