 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Word / Programming / July 2007Rename all files in a folder
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 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 FAQ: http://word.mvps.org Email cannot be acknowledged; please post all follow-ups to the newsgroup so all may benefit. >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 [quoted text clipped - 7 lines] >but with spaces. Is this possible and if so, how? >thank you 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, > 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 [quoted text clipped - 20 lines] > >but with spaces. Is this possible and if so, how? > >thank you 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 > Hi Jay, > [quoted text clipped - 29 lines] > > >but with spaces. Is this possible and if so, how? > > >thank you Thank you very much. This works perfectly. > Hi, > [quoted text clipped - 72 lines] > > > >but with spaces. Is this possible and if so, how? > > > >thank you 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 > Thank you very much. This works perfectly. > [quoted text clipped - 74 lines] > > > > >but with spaces. Is this possible and if so, how? > > > > >thank you > 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!  Signature.NET: It's About Trust! http://vfred.mvps.org 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 > > 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 [quoted text clipped - 8 lines] > control. That library is only intended to be used in scripting situations where > native file i/o isn't an option. HTH! >>> 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 [quoted text clipped - 11 lines] > 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 Signature.NET: It's About Trust! http://vfred.mvps.org 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 > >>> 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 [quoted text clipped - 72 lines] > > Thanks... 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. 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 Internet. <g> Has nothing to do with the (horridly misnamed!) vb.net product they later shipped. > The file needed to use FSO is scrrun.dll has been available for Windows at > least since Win98 so its quite common. Agreed. In many flavors. "So many to choose from!", and all that. <g> > 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. :-) Signature.NET: It's About Trust! http://vfred.mvps.org 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. >> 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: [quoted text clipped - 53 lines] > > Agreed. :-) SignatureRuss drsmN0SPAMikleAThotmailD0Tcom.INVALID > 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 [quoted text clipped - 64 lines] > > > > Agreed. :-) 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 > 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? Signature.NET: It's About Trust! http://vfred.mvps.org > 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. ;-) Signature.NET: It's About Trust! http://vfred.mvps.org |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.