Is there a way to do bulk 'Rename's in Windows?

Factual Questions
1

Every day I have to update market numbers from a website using their software. It outputs files with names that include ! _ and multiple . (periods). Unfortunately MS Access has a problem opening files with those characters in them (seriously- I get an error message along the lines of ‘MS Jet Engine database is unable…’). So I need to rename the files into simpler names for Access, then rename them BACK into their original file names so that the web software can update them the next day.

So, how can I rename index!IUX.csv to IUX.csv and back again after I update my database? I have to do this to about 12 files, so it isn’t horrible, just a pain. Can I write a VBA code in Excel or Access to change the names or ???

Thanks-
-Tcat

2

Does the Dir function (in VBA) return the correct filename, or does it fall over at that point?

3

What I’m doing is a TransferText macro in Access to update about 50 tables from 50 .csv files. It goes to the directory and transfers the text, discarding the 500+ lines of info that I already have in the Table and inputing the 1 I need (yesterday’s figures). It works fine for all of the normally named files, but hangs up at a few weird name files. If you have Access you can do this and see if you get the same result.

Create a .csv file in Excel, save it as index!SPX.csv on your Desktop (click through the format reminders from Excel).
Open Access and start a blank database. Go File > Get External Data > Import…
Find the index!SPX.csv file and try to import it. If it is anything like my computer, it will bomb saying the Jet engine couldn’t find the file…Weird, but true.
I am running Office 2002

4

dir /x will return the correct filename, from that u should be able to construst a .bat file which can change it one way, and then another to change it back.

I don’t know the exact names of your files, so I can’t tell u exactly what it would be, but say you had 01!blah.csv through to 96!blah.csv it would probably be something like:

ren ??!blah.csv ??.csv

Another solution would be to get a program I use called Batch File Renamer and create two tasks on that, which may be simpler in the long run

5

Right, I think I have a solution for you;

First, create a new module in the modules tab of your database, paste this code into it, then save it:


' AllowOnly Function; returns a string stripped of any non-permitted characters (from a parameter or if the parameter is empty, a default alphanumeric set)
Public Function AllowOnly(yourstring As String, Allowed As String)
If Allowed = "" Then Allowed = "1234567890abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ"
Dim intLoop As Integer
For intLoop = 1 To Len(yourstring)
    If InStr(Allowed, Mid(yourstring, intLoop, 1)) > 0 Then
        AllowOnly = AllowOnly & Mid(yourstring, intLoop, 1)
    End If
Next intLoop

End Function

Then use this code to convert all of the filenames in a given folder:


Dim StrFile As String
Dim strPath As String
Dim FileType as String
FileType = ".csv"
strPath = "C:\YourFolderName\"
StrFile = Dir(strPath & "*" & FileType)
Do Until StrFile = ""
Name (strPath & StrFile) As (strPath & AllowOnly((Left(StrFile, Len(StrFile) - 4)), "") & FileType)
StrFile = Dir
Loop

NB, there is at present no validation in place to ensure that duplicate filenames aren’t created (it will try to convert xyz!!123.csv and xyz!&!123.csv both to xyz123.csv and an error will occur, but this should be fairly simple to fix).

Hope this helps

6

Thanks! I have Googled about 5 File renamers, I’ll try them and see if it works. I didn’t even know that bulk renaming was a common thing…

7

Oh! Just saw Mange’s post. That looks better, keep it all in one place. I’ll try it too!

Thanks!

8

I’d go with what Mange said, he seems to be far more knowledgeable about this