Rename all files in a folder

[Jules]

How can I rename all of the files in a folder using VBA (word 2000).
We have a custom application which has the ability to convert word documents
to pdf documents. When converting the documents, the new (pdf) file names
have underscores instead of spaces (eg. "c:\temp\document_one.pdf"). This is
inbuilt in the custom application and the developers of the application can
(or will) not change it. After the documents are converted to pdf documents
we need to publish them to the web and the underscores cause a problem. I
would like to be able to set up something that users can run which will
rename any file in a selected folder that has underscores to the same name
but with spaces. Is this possible and if so, how?
thank you

 

[Jay Freedman]

It would be possible, but the only reason I could think of to do so
would be as a programming exercise. There are dozens if not hundreds
of well-tested, polished, and free utilities on the 'Net that will do
what you want -- http://www.google.com/search?q=file+rename+utility
turns up more than 2 million hits.

--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the
newsgroup so all may benefit.

 

[Jules]

Hi Jay,

Thanks for this info, but, unfortunately our IT department does not wish us
to go this route. As our users are always in Word, it is preferred that we
have this run from Word. If anyone does know VBA code to achieve this, I
would really appreciate it.

thanks,

 

[old man]

Hi,

Here is code I wrote to do what you want (for testing I rename txt files but
you can change it to work on any folder for any type file extension.

Sub s1()

Dim startdir As String
Dim curfile As String
Dim extension As String
Dim newname As String
Dim loc1 As Integer
Dim fulloldname As String
Dim fullnewname As String

startdir = "c:\t25\"
extension = "*.txt"

On Error GoTo errhandle1

curfile = Dir(startdir & extension)
Do While curfile <> ""
newname = curfile
loc1 = InStr(newname, "_")
Do While loc1 <> 0
newname = Left(newname, loc1 - 1) & " " & Mid(newname, loc1 + 1)
loc1 = InStr(newname, "_")
Loop
fulloldname = startdir & curfile
fullnewname = startdir & newname
Name fulloldname As fullnewname
curfile = Dir

Loop

Exit Sub

errhandle1:
MsgBox "Problem in rename routine - " & Err.Number & " Description - " &
Err.Description, vbCritical, "Rename Routine Error!"

End Sub

 

[Jules]

Thank you very much. This works perfectly.

Hi,

Here is code I wrote to do what you want (for testing I rename txt files but
you can change it to work on any folder for any type file extension.

Sub s1()

Dim startdir As String
Dim curfile As String
Dim extension As String
Dim newname As String
Dim loc1 As Integer
Dim fulloldname As String
Dim fullnewname As String

startdir = "c:\t25\"
extension = "*.txt"

On Error GoTo errhandle1

curfile = Dir(startdir & extension)
Do While curfile <> ""
newname = curfile
loc1 = InStr(newname, "_")
Do While loc1 <> 0
newname = Left(newname, loc1 - 1) & " " & Mid(newname, loc1 + 1)
loc1 = InStr(newname, "_")
Loop
fulloldname = startdir & curfile
fullnewname = startdir & newname
Name fulloldname As fullnewname
curfile = Dir

Loop

Exit Sub

errhandle1:
MsgBox "Problem in rename routine - " & Err.Number & " Description - " &
Err.Description, vbCritical, "Rename Routine Error!"

End Sub

 

[old man]

Hi,

Thanks. If you were going to do this often you may want to look at the
filesystemobject object. It has many more methods and properties and can be
used to rename files (I believe using the fso 'name' property). There are
many examples on the web regarding FSO. In .net there are even cleaner
objects....

old man

 

[Karl E. Peterson]

Thanks. If you were going to do this often you may want to look at the
filesystemobject object. It has many more methods and properties and can be
used to rename files (I believe using the fso 'name' property). There are
many examples on the web regarding FSO.

FSO is probably the worst thing one could advise for use in VB/VBA, especially in
cases where it's already been noted there exists a nazi-like IT department. (It's
frequently disabled by such, as a "security" measure.) FSO is also far slower than
native VB (and is *smoked* by direct API), exists (if at all!) in different versions
on different machines, adds an unnecessary dependency, and accepts surrender of full
control. That library is only intended to be used in scripting situations where
native file i/o isn't an option. HTH!

 

[old man]

Hi Karl,

I hate to disagree with someone who is a real expert in VBA (and whenever I
see any code example by I read carefully) but FSO may be slower (using it to
rename files speed won't be a factor), it is available almost everywhere, the
standard methods are pretty consistant and security is an issue with VBA too.
What you are saying is that since it is powerful (has more methods) it is not
safe to use. As I said there are many, many examples on the Web using FSO in
Word VBA. If I was doing a lot of file copying I would use straight C or APIs
but for something like file manipulation I would consider using FSO.

old man

 

[Karl E. Peterson]

I hate to disagree with someone who is a real expert in VBA (and whenever I
see any code example by I read carefully)

I welcome disagreement, because without it learning is far harder (if not
impossible).

but FSO may be slower (using it to
rename files speed won't be a factor),

"May" is not the appropropriate word...

FindFirstFile: Performance Comparison - FSO vs. API
http://vbnet.mvps.org/code/fileapi/fsoapicompare.htm

For renaming files, it's just ridiculously inappropriate given the native
equivalence, speed issues entirely aside.

it is available almost everywhere,

That's simply not true, and it exists in multiple versions where it does in fact
exist. Even if we were, for the sake of argument, grant that it's "almost
everywhere" that leaves some percentage of systems using FSO is conciously designing
for failure.

If you are operating in a wholly controlled environment, this may or may not be an
issue. But "in the wild" using FSO is the height of irresponsibility because you
simply do *not* control whether or not it's available for your application.

the standard methods are pretty consistant

Not sure what you mean by that?

and security is an issue with VBA too.

Security is always an issue, I'm afraid. But that fact is irrelevent to the
discussion. It's people's reaction to security threats that I'm pointing out.
Based mostly on fear alone, many IT departments have disabled FSO within their
organizations. They see this as a way of mitigating imagined damage. Code that
uses native file i/o methods is not impacted by this (short-sighted) policy.

What you are saying is that since it is powerful (has more methods) it is not
safe to use.

Excuse me? That's *absolutely* the opposite of what I'm saying. It's weak.
Extremely weak.

I advocate, instead, for two far more powerful approaches -- native VB(A) and/or the
Windows API -- when file i/o is needed.

As I said there are many, many examples on the Web using FSO in Word VBA.

This world abounds with bad examples. Good examples are frequently far harder to
find. Surely you're not arguing that availability of inferior options boosts their
usefulness?

If I was doing a lot of file copying I would use straight C or APIs
but for something like file manipulation I would consider using FSO.

Your call, of course. As I said, in a totally controlled environment, it's a
functional approach. Outside the lab, using a HLL like VB(A), it's inarguably
nothing short of inviting (with open arms) failure.

Thanks... Karl

 

[old man]

Hi Karl,

I don't want to engage you in a deep discussion on this but I would just
like to respond to several things you said:

The timing example was for 1601 files and it was ten times as long using FSO
but for a typical rename (even 100 files) I don't believe users would notice
the difference. While I use APIs extensively I have seen many VBA people
avoid APIs (indeed the example you noted is from a VBNET group).

The file needed to use FSO is scrrun.dll has been available for Windows at
least since Win98 so its quite common.

I have seen corporate clients remove sccrun.dll in an effort to tighten up
their systems but if users can enable VBA projects and set security level to
low the door is pretty much open anyway (or at least dependent on scanning
emails and files thoroughly.)

I still believe that if a user does not want to use API's (which have their
own issues such as error handling....) they should consider using FSO which
is as much a part of VBA as the use of API's.

with respect
Old Man

 

[Karl E. Peterson]

I don't want to engage you in a deep discussion on this but I would just
like to respond to several things you said:

The timing example was for 1601 files and it was ten times as long using FSO
but for a typical rename (even 100 files) I don't believe users would notice
the difference.

Agreed. Not that I think that's a valid basis for neglecting the potential downside
with FSO.

While I use APIs extensively I have seen many VBA people
avoid APIs
Agreed.

(indeed the example you noted is from a VBNET group).

Randy's actually been using that name since long before Microsoft "discovered" the

The file needed to use FSO is scrrun.dll has been available for Windows at
least since Win98 so its quite common.

I have seen corporate clients remove sccrun.dll in an effort to tighten up
their systems but if users can enable VBA projects and set security level to
low the door is pretty much open anyway (or at least dependent on scanning
emails and files thoroughly.)
Agreed.

I still believe that if a user does not want to use API's (which have their
own issues such as error handling....) they should consider using FSO which
is as much a part of VBA as the use of API's.

Disagree. Violently. (As you've probably noticed. <g>) There are virtually no
"issues" with using APIs other than becoming mildly familiar with them. Examples
also abound. Error handling is actually far simpler -- there really isn't any.
Native file i/o is also superior to FSO. Indeed, the only situation where FSO is
warranted is in scripting languages (like VBScript) that can't access the API and
don't have native file i/o functionality. FSO is no more a part of VBA than is the
AutoCAD object model. It's an wholly external library.

with respect

Agreed.

 

[Russ]

So what API hooks do you use to rename files? What function names?
Although API, FSO, Windows Scripting won't do me any good on my home MacWord
2004; I do use a W2K OS, Word97 at work.

I guess in my MacWord 2004, I'd use VBA to call an AppleScript to call a
UNIX shell and use regular expressions to rename my files. That particular
macro wouldn't work in a Windows environment, however.

Is there a way to call a Perl script and use regular expressions with VBA?
That might be useful on both a Windows and Mac platform.

 

[old man]

So what API hooks do you use to rename files? What function names?
Although API, FSO, Windows Scripting won't do me any good on my home MacWord
2004; I do use a W2K OS, Word97 at work.

I guess in my MacWord 2004, I'd use VBA to call an AppleScript to call a
UNIX shell and use regular expressions to rename my files. That particular
macro wouldn't work in a Windows environment, however.

Is there a way to call a Perl script and use regular expressions with VBA?
That might be useful on both a Windows and Mac platform.

Hi,

I know this is going to irritate Karl but to use regex in VBA you have to
attach another scripting library, "Microsoft Regular Expressions 5.5" or
whatever version is on your machine. I have used RegEx for a long time and
this version is very good.

To use Perl with VBA you can invoke it using WScript.Shell.

Old Man

 

[Karl E. Peterson]

So what API hooks do you use to rename files? What function names?

I've never had to use the API for that -- the native Name statement has worked just
fine, since the days of DOS.

Although API, FSO, Windows Scripting won't do me any good on my home MacWord
2004; I do use a W2K OS, Word97 at work.

And, Name is trans-platform, eh?

I guess in my MacWord 2004, I'd use VBA to call an AppleScript to call a
UNIX shell and use regular expressions to rename my files. That particular
macro wouldn't work in a Windows environment, however.

Sounds like you're just looking for work. ;-)

 

[Karl E. Peterson]

I know this is going to irritate Karl but to use regex in VBA you have to
attach another scripting library, "Microsoft Regular Expressions 5.5" or
whatever version is on your machine.

You're just *trying* to be gratuitously inciteful, aren't you? It's really as if
you didn't *hear* anything I said, earlier. Certainly not the salient points.

There's nothing inherently "wrong" about using an external library when the
same/similar functionality doesn't exist in the language itself. What part of that
that qualification are you having trouble understanding? You _do_ understand what
it means to *qualify* a statement, right?

Furthermore, not only does the library in question provide non-native functionality,
but it *also* isn't _routinely_ disabled by paranoid and ignorant IT departments.

Now, that all said, I see that the "Microsoft Windows Script 5.6" is apparently
something users must download and install themselves. Are you even aware whether
the tool you're recommending is redistributable?