MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Renaming all files in a folder

  Asked By: Conrad    Date: Nov 14    Category: MS Office    Views: 1590

this is might be difficult to explain, but the cooncept
itself is basic. I receive a 300 files weekly that need to be in a
specific format in order for a macro to read the file names and
consolidate them for other reports. The problem is that 75% are sent
in as some variation of the correct name. I want to write a script
that will by brute force open each file, refer to a cell within the
file, and rename the file. I am a yellow belt in VBA but after asking
a couple of the black belts in my office we are stumped in how to
instruct VBA to open each and every file as long as it is in a given



2 Answers Found

Answer #1    Answered By: Lenora Green     Answered On: Nov 14

A simple Google search would have got you loads of information.

From what I can see, you can either use Application.FileSearch or simply Dir
() to list the files  in a directory. There were examples of using both to
search directories within the first few hits from the Google search.

The one you decide to use will be somewhat dependent on what you are doing.

If you are changing files in the same directory that you're listing, then
there is a chance that Dir () could get into trouble. On the other hand,
Application.FileSearch returns a collection of found files, and is less
likely to have this problem.

However, the problem can be avoided by the simple expedient of creating a
list of files first, then working through them.

Answer #2    Answered By: Ryan Evans     Answered On: Nov 14

In the VBA editor's help, try looking for "files collection".
then, select the entry for the
Files Property (Visual Basic for Applications)

It gives you sample code that looks like:
Sub ShowFileList(folderspec)
Dim fs, f, f1, fc, s
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder(folderspec)
Set fc = f.Files
For Each f1 in fc
s = s & f1.name
s = s & vbCrLf
MsgBox s
End Sub

You can modify this code to do what you need to do for each file.

Didn't find what you were looking for? Find more on Renaming all files in a folder Or get search suggestion and latest updates.