I inherited a folder with a bunch of files in it. Almost every file had spaces in the name. I abhor spaces in file names. Also, I need to put these files in Subversion, so the names would have all be encoded so they’d work as URLs. That is, all of the spaces would become %20 or some such thing. After manually fixing the first one, I opted for a script.
First, I set a reference to “Microsoft Scripting Runtime” (VBE – Tools – References) so I could access the FileSystemObject.
Sub RemoveSpaces()
Dim fso As FileSystemObject
Dim fsoFile As File
Set fso = New FileSystemObject
For Each fsoFile In fso.GetFolder(“C:Tester”).Files
If InStr(1, fsoFile.Name, ” “) > 0 Then
fsoFile.Name = Replace(fsoFile.Name, ” “, “_”)
End If
Next fsoFile
End Sub
Dim fso As FileSystemObject
Dim fsoFile As File
Set fso = New FileSystemObject
For Each fsoFile In fso.GetFolder(“C:Tester”).Files
If InStr(1, fsoFile.Name, ” “) > 0 Then
fsoFile.Name = Replace(fsoFile.Name, ” “, “_”)
End If
Next fsoFile
End Sub
I had to test to make sure there was a space in the file name. If there wasn’t, it complained that the file already exists. So it must copy, rename, then delete.
From this
to this
And here’s a version for those who have upgraded to Excel 2007 and discovered that FileSystemObject is no longer supported:
Const Folder As String = “C:Tester”
Dim FileName As String, NewName As String
FileName = Dir(Folder)
Do While FileName “”
If InStr(1, FileName, ” “) > 0 Then
NewName = Replace(FileName, ” “, “_”)
Name Folder & FileName As Folder & NewName
End If
FileName = Dir
Loop
End Sub
Here is a different way to do the same thing…
Dim Path As String, FileName As String
Path = “C:Tester” ‘Note the trailing backslash
FileName = Dir(Path & “*.*”)
Do While FileName “”
Name Path & FileName As Path & Replace(FileName, ” “, “_”)
FileName = Dir
Loop
End Sub
Dick:
========
I now see that John Walkenbach beat me to the “Dir” method of doing this by a few minutes. Just to comment on our code, though… it looks like your Comment Processor removed our “not equal to” sign (a less than symbol followed by a greater than symbol) that was in the Do While statement. It also looks like our ampersands (for concatenation) were changed to this text string… “&” for some reason.
John:
========
You don’t have to test the filename to see if it contains a space or not… our method does not seem to complain if the filename has no spaces in it.
Right, the test for spaces isn’t required. I was just mindlessly following Dick’s lead.
Hopefully, people will be able to correct the mangled code.
John… regarding your comment that the FileSystemObject is no longer supported… it looks like if you add a Reference to the Microsoft Scripting Runtime, then the code Dick posted will run as expected in XL2007.
I just noticed that in my comment about the mangled code, my attempt to include the text string that the Comment Processor replaced my ampersands with got displayed as a real ampersand. I’m just playing a hunch here to see if that is the way to include the symbols that got changed. Hopefully, this version of my previously posted code will display correctly…
Dim Path As String, FileName As String
Path = “C:Tester” ‘Note the trailing backslash
FileName = Dir(Path & “*.*”)
Do While FileName <> “”
Name Path & FileName As Path & Replace(FileName, ” “, “_”)
FileName = Dir
Loop
End Sub
Okay, it didn’t work. Dick… you can remove my last posting and this one if you want.
Whilst I agree that the Dir method is a more appropriate approach John, I don’t think that FilesystemObject is not supported in Excel 2007. You must be thinking of FileSearch.
You’re right Bob, I was thinking of FileSearch. My age-induced dementia strikes again.
John, Rick –
Obligatory angle bracket comment: Inside the VB tags, WordPress treats angle bracket pairs as HTML delimiters and throws away/ignores anything you there. In the case of “not equals,” just the pair of brackets goes. In other cases, with a less-than sign up there, and a greater-than sign down here, your code is dramatically shortened.:roll:
It mungs ampersands and an unpaired angle bracket, too.
…mrt
Dick needs a syntax highlighter that’s been updated more recently than 2006. :)
Michael… I am not familiar with HTML (I’ve never designed a webpage)… is there a way to “protect” the angled brackets and ampersand from the HTML processor so that they display as intended?
Using the library reference when declaring variables is helpful to me…
Sub RemoveSpaces()
Dim fso As Scripting.FileSystemObject
Dim fsoFile As Scripting.File
Set fso = New Scripting.FileSystemObject
JP: Which highlighter to do you use?
Not sure what happens. Here’s where I use NEQ, GEQ, and LEQ and concatenate.
Sub useArithCompare()
Dim X, Y, Z
If X <> Y Then
If Y > Z Then
If Z < 0 Then
ElseIf Z >= 0 Then
End If
ElseIf Y <= Z Then
Y = X & Z
End If
End If
End Sub
Hi Rick –
There should be, but alas, no. Escape characters are not honored inside VB tags.
Workarounds are two:
Don’t use VB tags. OK for snippets, but kind of defeats the purpose. Regular replies don’t misbehave like this.
or
Use things like LT or LTE or GT or GTE or !=. The eye will figure it out, and the VBE will catch it otherwise. Not ideal, but at least the code doesn’t get turned into the Reader’s Digest condensed version ;-)
I let the ampersands ride. Dick has inquired of WordPress about the problem.
…mrt
Tushar! –
How’d you do that?
…mrt
Michael, I didn’t do anything other than copy+paste from the VBE and enclose the code in square-brackets-around-vb and square-brackets-around-slash-vb tags. While I didn’t do it this time, I used to separate NEQ into < space > and LEQ into < space =. Those are the two that might be misconstrued as erroneous HTML tags. GEQ cannot be. So, nothing special should be required.
Tushar… are you sure you didn’t do something different than you described? When I posted my code, I had copy/pasted it from the VB editor, surrounded it with the VB tags and the Comment Processor screwed it all up.
This one:
http://wordpress.org/extend/plugins/codecolorer/
None of this HTML formatting nonsense. Perfect formatting every time, even in the comments!
@Tushar –
If you just pasted from the VBE, then how did you get the VBE to forgo putting spaces between the Y and the right angle bracket in the first IF, the left angle bracket and 0 in the second IF and the Z and the right angle bracket in the first ElseIf? For me on my system the VBE always put spaces around all operators.
This is a test.
not in a VB block: LT < LE <= NE <> GE >= GT > AMP &
in a VB block:
Another test, pasting from a unicode editor.
outside VB block:
LT <
LE <=
NE <>
GE >=
GT >
AMP &
inside VB block:
LE <=
NE <>
GE >=
GT >
AMP &
Back on-topic: another fine place to use a batch file, though the batch file would moderately complex.
@echo off
@setlocal enableextensions
@REM default to current directory
if “%~1? == “” %0 .
for /f “delims=” %%f in (‘dir /s/b/a-d “pathname-here* *”‘) do call :PROC “%%f”
goto :EOF
:PROC
set f=%~1
ren “%~1? “%f: =_%”
:EOF
Simpler with a real shell and POSIX find and mv commands.
find “pathname-here” -type f -name “* *” | while read f; do mv “$f” “${f// /_}”; done
Or as a 1-line script
find “${1-.}” -type f -name “* *” | while read f; do echo mv “$f” “${f// /_}”; done
RE:Subversion
Dick,
I am considering Subversion for VBA code management anong with xls file management. Have you used it for this? How well does it work?
AlexJ
Re testing whether the filename includes a space.
I think I would test, so that I don’t make the file system rename a file to the same name. I don’t know what kind of burden that is on the CPU, maybe someone less lazy than I could try it out, but file operations typically cost more than If statements.
Jon brings up an interesting point: renaming “foo bar” when the file “foo_bar” already exists.
The problem would be needing to interrupt one directory search (for “* *”) with a second search to check if there are any files already named with the current file’s name with spaces replaced by whatever.
It seems to me it’d be more efficient to
– load the full directory listing into an array or worksheet range,
– use a second column for filenames with spaces converted to underscores,
– sort the table by RESULTING filename,
– walk through the RESULTING filename field looking for matching adjacent values,
and when found appending a [1] just before the extension
– walk through the ORIGINAL filename field changing filenames when original
filename differs from resulting filename.
Optional to include another pass through the resulting filenames to spot things like [1][1] or [3][1] just before the extension and converting them to [2] and [4], respectively (for these examples).
This is simple with the batch file or shell script approaches since full pathname parsing is built-in as opposed to VBA/VBScript where reinventing path/basename/extension parsing is necessary.
As fzz said, Jon has brought up an interesting point; however, I don’t think the problem is as hard as fzz is imagining. I believe this code will handle the problem by affixing Copy(#), where # is a sequence number, to make sure each file name is unique…
Dim Copies As Long, Dot As Long
Dim Path As String, FileName As String, FN As String, Suffix As String
Path = “C:Tester” ‘Note the trailing backslash
FileName = Dir(Path & “*.*”)
On Error Resume Next
Do While Len(FileName)
FN = FileName
Suffix = “”
Copies = 0
Do
Err.Clear
Name Path & FN As Path & Replace(FileName, ” “, “_”)
If Err.Number Then
Copies = Copies + 1
Suffix = ” Copy(“ & Copies & “)”
Dot = InStrRev(FN, “.”)
FileName = Left(FN, Dot – 1) & Suffix & Mid(FN, Dot)
End If
Loop While Err.Number
FileName = Dir
Loop
End Sub
Rick: I was also implicitly responding to Jon secondary point: but file operations typically cost more than If statements.
Error trapping OS file operations may be simple (as in brute force), but it ain’t fast. If you want robust AND fast, it’s like I said: better to load into an array or range, process the array or range, then loop once through the array or range (with no subloops) to change filenames as/when necessary.
I have not Alex. I’m just learning subversion and I like it so far. Based on what I know, I would create a subversion repository with my Excel file in it. Then I would use Rob Bovey’s code cleaner for every “build” and I would set it sot that it doesn’t delete the bas files. Then when the folder is committed back to the repository, you have the xls and all the text files, which hopefully would be diff’d. That’s just off the top of my head. I’m sure there would be a hundred problems.
Thanks, Dick. You are proposing the same approach I am considering. I was even planning to use vba code from the old version of Code Cleaner as a model for routines to “harvest” and “replentish” the bas, and sheet and class modules to and from subversion (with full credit to Rob, of course!)
c0 = “E:”
c1 = Dir(c0 & “* *.xls”)
Do Until c1 = “”
If InStr(vbCr & c2, vbCr & c1 & vbCr) = 0 Then
c2 = c2 & c1 & vbCr
Else
c3 = c3 & c1 & vbCr
End If
c1 = Dir
Loop
sq = Split(c2, vbCr)
st = Split(Replace(c2, ” “, “_”), vbCr)
For j = 0 To UBound(sq) – 1
Name c0 & sq(j) As c0 & st(j)
Next
If Not IsEmpty(c3) Then MsgBox c3, , “There are a few problems left”
End Sub
Well, you can use Long Path Tool, for such problems, it works good I will say.