Global Save As?
 

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

Global Save As?
 

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)

"Jane" wrote in message
...
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

Global Save As?
 

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


"Jane" wrote:

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

Global Save As?
 

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

On Thu, 15 Feb 2007 18:46:42 -0600, "Chip Pearson" wrote:

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

Global Save As?
 

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)

"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
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

On Thu, 15 Feb 2007 18:46:42 -0600, "Chip Pearson"
wrote:

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

Global Save As?
 

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

"Jane" wrote:

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

Global Save As?
 

This may be useful to take a peek at:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

BTW - my suggestion also assumed all of the files were in one folder and all
of the files in that folder are supposed to be renamed.


"Jane" wrote:

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

"Jane" wrote:

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

Global Save As?
 

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!

"JMB" wrote:

This may be useful to take a peek at:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

BTW - my suggestion also assumed all of the files were in one folder and all
of the files in that folder are supposed to be renamed.


"Jane" wrote:

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

"Jane" wrote:

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