Global Save As?

J

Jane

We need to change the file names of 65 files by adding the same 1 word to the
beginning of each file name.

Is it possible to do a global save as so we do not have to re-name each file?

thank you in advance! jane
 
C

Chip Pearson

Assuming that all files are in the same folder, and that the folder doesn't
contain files that you don't want to rename, use code like the following:

Sub RenameAll()

Const C_FOLDER_NAME = "C:\Test" '<<<< CHANGE THIS
Const C_WORD_TO_APPEND = "Test" '<<<< CHANGE THIS
Dim FName As String
Dim NewName As String
Dim Pos As Integer
ChDrive C_FOLDER_NAME
ChDir C_FOLDER_NAME
FName = Dir("*.xls") ' Change the *.xls to whatever file spec you need.
Do Until FName = vbNullString
Pos = InStrRev(FName, ".")
NewName = Left(FName, Pos - 1) & C_WORD_TO_APPEND & Mid(FName, Pos)
Name FName As NewName
FName = Dir()
Loop

End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)
 
J

JMB

You could try a macro - this appears to work okay for me, but it is a good
idea to back up your files before trying anything new. You will need to
check the Microsoft Scripting Runtime library in the VBA editor under
Tools/References and change the variables for your path and prefix.

Sub Rename()
Const strPath As String = "I:\Excel\Temp" '<< CHANGE
Const strPrefix As String = "Prefix" '<<CHANGE
Dim scrFSO As Scripting.FileSystemObject
Dim scrFolder As Scripting.Folder
Dim scrFile As Scripting.File

Set scrFSO = New Scripting.FileSystemObject
Set scrFolder = scrFSO.GetFolder(strPath)

For Each scrFile In scrFolder.Files
scrFile.Name = strPrefix & scrFile.Name
Next scrFile

End Sub
 
G

Gord Dibben

Chip

I found that your code did not add the text to the beginning of the original
filename as OP desired

I changed to...........

Sub RenameAll()

Const C_FOLDER_NAME = "C:\Test" '<<<< CHANGE THIS
Const C_WORD_TO_APPEND = "Test" '<<<< CHANGE THIS
Dim FName As String
Dim NewName As String
Dim Pos As Integer
ChDrive C_FOLDER_NAME
ChDir C_FOLDER_NAME
FName = Dir("*.xls") ' Change the *.xls to whatever file spec you need.
Do Until FName = vbNullString
NewName = C_WORD_TO_APPEND & FName
Name FName As NewName
FName = Dir()
Loop

End Sub

OK with you?


Gord Dibben MS Excel MVP
 
C

Chip Pearson

I know. I realized that after I posted. I was thinking "suffix" not
"prefix".


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)
 
J

Jane

wow! thank you for your responses!

We are a bit shaky on our macro skills but will do our best to implement
your solutions.

By chance is there a non macro solution? Thought I would ask :)

thank you again! Jane
 
J

Jane

yes, you are right that all files are within 1 folders altho we do have a
number of folders that are broken down by product type so we assume that this
process would have to be done to each folder.

Will take a look at the link you provided.

THANKS!
 
Top